MS SQL: Database + Windows Authentication + Username/Password?

Question

I have always thought that in order to connect to SQL server using windows authentication with explicitly specified creds, you must LogonUser, Impersonate, then connect.

It seems to me that this link suggests that it's possible to connect to SQL server without all this hassle, simply by specifying "uid=...;pwd=..." in connection string. I tested this method just to be sure it doesn't work, and - lo and behold - it didn't. If that blog post wasn't on msdn.com, I would have just dismissed it as noob talk, but it is.

Does anyone have an idea what am I missing?

EDIT1: Many respondents misunderstood what I was referring to. Here's a copy/paste of what I was talking about. It's not integrated SQL, nor it's an ASP.NET impersonation made by IIS:

string sql4 = String.Format(
   @"Data Source={0};Integrated Security=SSPI;uid=<uid>;pwd=<pid>", server);     
// Database + Windows Authentication + Username/Password

Answer 1

It depends - if you connect from a command-line or Winforms app DIRECTLY to your SQL Server, you EITHER specify "Integrated Security=SSPI;" and then use your Windows credentials as logon credentials, OR you specify "user id=....;pwd=....." - but that's then a SQL logon - NOT your Windows logon.

You mention "impersonate and then connect" - that seems to indicate ASP.NET - that's a totally different story again. If you impersonate, then you're basically using your Windows credentials, e.g. the web server will "impersonate" you and log on as you (using your Windows credentials). In that case, again, no "uid=....;pwd=....." needs to be specified (if it is, it will be ignored).

As that link you mentioned clearly shows - if you can connect directly, and you specify "Integrated Security=SSPI;", then this takes precedence over any uid=...;pwd=.... which you might also specified and logs you in using your Windows credentials; those extra uid=...;pwd=.... pieces are ignored.

Marc

Answer 2

that's probably for sql server logins.

Answer 3

The article and point in question relates to SQL security, not integrated security. You can pass the credentials for a SQL user and log in in this manner if SQL authentication (mixed mode) is enabled. If the SQL server is set up to use integrated security only then this will not work. It will also not work to allow logging in using Windows logon credentials.

Answer 4

There are two distinct kinds of security with SQL Server. "Windows Authentication", and "SQL Server Authentication". When you see uid and pwd you're seeing the latter. The uid in this case is not a Windows principal - the OS knows nothing about it.

So the answer to your question is, no, you can't pass Windows user name and password in the connection string to log in to SQL Server.

Answer 5

In our shop, we routinely use connection strings like you describe. No problemo. But your sql server database must be set up to use sql security, not windows authentication.

A sample connection string (from web.config) in our app looks like:

<connectionStrings>
<add name="ConfigurationData" connectionString="server=DevServer;
database=travel_expense_management_dv;uid=userid;pwd=password!;"
providerName="System.Data.SqlClient" />
</connectionStrings>

On the other hand, the DBA guru for our shop set me up a personal database on the main server that had integrated security with my Windows logon. I didn't need uid and pwd because it took my authentication info from context.