ADO.NET - 使用应用程序提供的登录名和密码通过 Windows 登录连接到 SQL Server [英] ADO.NET - Connect to SQL Server with Windows Login with login and password supplied by the application

问题描述

这是 SQL Server 2008 R2，.NET 4.0.

This is SQL Server 2008 R2, .NET 4.0.

在我的 SQL Server 中，有一个使用Windows 身份验证"创建的用户.用户存在于 Active Directory 域中.

In my SQL Server there is a user created with "Windows Authentication". The user exists in a Active Directory domain.

我想让一个 .NET 应用程序以这个用户的身份连接到 SQL Server.在应用程序内部，我知道用户的域、登录名和密码，并且应用程序可以访问 AD 服务器.

I want to make a .NET application connect to the SQL Server as this user. Inside the application, I know the user's domain, login and password, and the application has network access to the AD server.

我怎样才能做到这一点?

How can I accomplish this?

我知道 ASP.NET 有它的 AD 提供程序和模拟.但我正在寻找的是一个真正通用的解决方案，它应该适用于普通的控制台应用程序.我可以在控制台应用、Windows 窗体、asp.net 或通用业务类库上使用的东西.

I know that ASP.NET has it's AD provider and impersonation. But what I'm looking for is a really generic solution, one that should work on a plain console application. Something that I could use on console app, windows forms, asp.net, or a common business class library.

感谢您的帮助！

推荐答案

我已经使用这个类完成了:

I've done it using this class:

https://platinumdogs.me/2008/10/30/net-c-impersonation-with-network-credentials/

如果计算机不属于域，您必须使用 LOGON32_LOGON_NEW_CREDENTIALS = 9 进行模拟.

You must impersonate using LOGON32_LOGON_NEW_CREDENTIALS = 9 if the computer does not belong to the domain.

一旦被模拟，然后使用 SQL 连接字符串上的Integrated Security=true"连接到 SQL.

Once impersonated, then connect to SQL using "Integrated Security=true" on the SQL Connection String.

SqlConnection conn;
using (new Impersonator("myUserName", "myDomain", "myPassword", LogonType.LOGON32_LOGON_NEW_CREDENTIALS, LogonProvider.LOGON32_PROVIDER_DEFAULT))
{
    conn = new SqlConnection("Data Source=databaseIp;Initial Catalog=databaseName;Integrated Security=true;");
    conn.Open();
}
//(...) use the connection at your will.
//Even after the impersonation context ended, the connection remains usable.

警告:注意连接池.该池与运行应用程序的实际用户相关联，而不是与模拟的网络凭据相关联.因此，在使用相同的连接字符串进行后续访问时，池可能会返回先前模拟的用户建立的连接，即使您通过网络模拟第二个用户.如果您不知道自己在做什么，请在使用它时禁用连接池.

ALERT: Beware of connection pooling. The pool is associated with the actual user running the application, not with the network credentials that were impersonated. So on subsequent access using the same connection string, the pool may return a connection made by a previously impersonated user, even if you netorkly-impersonate a second user. If you don't know what you're doing, disable connection pooling when using this.

这篇关于ADO.NET - 使用应用程序提供的登录名和密码通过 Windows 登录连接到 SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！