Connectionstrings, mixing usernames and windows authentication. Who goes first?

Article
11/14/2008

When connecting to Sql Server from .Net, there are three namespaces containing classes to do so: System.Data.SqlClient / System.Data.OleDb / System.Data.Odbc

The one should use is System.Data.SqlClient since this contains functions specific to Sql Server, note that you have to use .Net 2.0 SP1 or higher to get Sql Server 2008 specific support.

When using System.Data.OleDbClient there are three providers to use, SQLOLEDB, SQLNCLI, SQLNCLI10. You can use anyone to connect to a Sql Server, but to get Sql Server <version>

specific support you have to use the provider designed for the particular Sql Server version that you are using.

For example, you can use SQLOLEDB to connect to Sql Server 2008, but in order to use the new DATETIME2 datatype, you have to use SQLNCLI10, etc.

SQLOLEDB comes with the OS.

SQLNCLI comes with the Sql Server Tools for Sql Server 2005

SQLNCLI10 comes with the Sql Server Tools for Sql Server 2008

.NET Framework Data Provider for SQL Server of course comes with the .Net framework.

see more in the links below.

What all of the above has in common is that when you connect, you can specify if you wish to connect to Sql Server using Windows Authentication (Integrated Security) or by providing

a username and a password (assuming that the user exists and that the server is configured for Windows Authentication and Sql Authentication, aka Mixed Mode).

The recommendation is to use Windows Authentication, see below for this and other things regarding protection of connection information.

Now, what is somewhat confusing, at least it was to me, is that the syntax is different depending on what you use to connect, and the default behavior as well when you combine it

with a username and password.

SYNTAXES:

System.Data.SqlClient

Integrated Security=true

Integrated Security=false

Integrated Security=yes

Integrated Security=no

Integrated Security=SSPI

System.Data.OleDb

Integrated Security=SSPI

Note: if you try to use Integrated Security=true/false/yes/no with OleDb, you will get an exception when connecting.

System.Data.Odbc

Trusted_Connection=yes

Trusted_Connection=no

Note: if you try to use Trusted_Connection=true/false/SSPI with Odbc, you will get an exception when connecting.

CONNECTION STRING COMBINATIONS:

For the combination (or the absence) of user/password and Windows Authentication I can see four connectionstring syntaxes,

. Database only

. Database + Windows Authentication.

. Database + Username/Password

. Database + Windows Authentication + Username/Password

but what is actually used depending on way of connection and connectionstring syntax?

Let’s do them one by one, this is assuming that you have a user/password that is allowed to connect and that the Sql Server has Mixed Mode authentication.

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

System.Data.SqlClient

string sql1 = String.Format(@"Data Source={0}", server); // Database only

string sql2 = String.Format(@"Data Source={0};Integrated Security=SSPI", server); // Database + Windows Authentication

string sql3 = String.Format(@"Data Source={0};uid=<uid>;pwd=<pid>", server); // Database + Username/Password

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

string sql1 -> Throws an exception: {"Login failed for user ''."}

string sql2 -> Logs in with Windows login

string sql3 -> Logs in as the specified user

string sql4 -> Logs in with Windows login, ie. takes precedence over the username/password.

System.Data.OleDb

string oledb1 = String.Format(@"Provider={0};Data Source={1}", provider, server); // Database only

string oledb2 = String.Format(@"Provider={0};Data Source={1};Integrated Security=SSPI", provider, server); // Database + Windows Authentication

string oledb3 = String.Format(@"Provider={0};Data Source={1};uid=<uid>;pwd=<pid>", provider, server); // Database + Username/Password

string oledb4 = String.Format(@"Provider={0};Data Source={1};Integrated Security=SSPI;uid=<uid>;pwd=<pid>", provider, server);// Database + Windows Authentication + Usr/Pwd

string oledb1 -> Throws an exception: {"Invalid authorization specification"}

string oledb2 -> Logs in with Windows login

string oledb3 -> Logs in as the specified user

string oledb4 -> Logs in with Windows login, ie. takes precedence over the username/password.

System.Data.Odbc

string odbc1 = String.Format(@"Dsn=Sql2008DSN", server); // Database (DSN) only

string odbc2 = String.Format(@"Dsn=Sql2008DSN;Trusted_Connection=yes", server); // Database (DSN)+ Windows Authentication

string odbc3 = String.Format(@"Dsn=Sql2008DSN;uid=<uid>;pwd=<pid>", server); // Database (DSN) + Username/Password

string odbc4 = String.Format(@"Dsn=Sql2008DSN;Trusted_Connection=yes;uid=<uid>;pwd=<pid>", server); // Database (DSN) + Windows Authentication + Username/Password

string odbc1 -> Logs in with Windows login

string odbc2 -> Logs in with Windows login

string odbc3 -> Logs in with Windows login

string odbc4 -> Logs in with Windows login, ie. takes precedence over the username/password.

Now, the observant user will see that this is strange. The third connectionstring is clearly not using Windows Authentication, or is it?

Well, this is a common thing to see, in the ODBC Data Source Administrator there is an option where you set “With Integrated Windows Authentication” or “With Sql Server Authentication”.

If “With Integrated Windows Authentication” is selected, this will takes precedence over the username/password in the connection string.

The solution is to either configure the DSN (Start -> Run -> odbcad32) to use “With Sql Server Authentication” OR in the connection string explicitly say ‘do not use Windows Authentication’.

This is done by changing the connection string like so:

string odbc3 = String.Format(@"Dsn=Sql2008DSN;uid=<uid>;pwd=<pid>", server

string odbc3 = String.Format(@"Dsn=Sql2008DSN;Trusted_Connection=no;uid=<uid>;pwd=<pid>", server

Note that if you change the DSN to use “With Sql Server Authentication”, then you can’t use the syntax of connection string 1 above, instead you have to use 2, 3 or 4.

When using connection string syntax 1, the server expects a windows login or a username/password. Since it has neither, it will throw an exception: Login failed for user ''

So as you can see, the syntax and the behaviors are different depending on what provider or way of connection you use.

Hopefully you will be able to use the above to get a clearer picture of what is going on.

As mentioned, when connecting to Sql Server, the recommended approach is to use the .Net SqlClient and Windows Authentication.

Some references:

".NET Framework Developer's Guide - Connection String Syntax (ADO.NET)"

https://msdn.microsoft.com/en-us/library/ms254500.aspx

".NET Framework Class Library - SqlConnection.ConnectionString Property"

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx

".NET Framework Class Library - OleDbConnection.ConnectionString Property"

https://msdn.microsoft.com/en-us/library/system.data.oledb.oledbconnection.connectionstring.aspx

".NET Framework Class Library - OdbcConnection.ConnectionString Property"

https://msdn.microsoft.com/en-us/library/system.data.odbc.odbcconnection.connectionstring.aspx

".NET Framework Developer's Guide - Protecting Connection Information (ADO.NET)"

https://msdn.microsoft.com/en-us/library/89211k9b.aspx

As mentioned, you can connect to Sql Server 2008 using SQLOLEDB or the 2005 provider SQLNCLI, but to be able to use all functions, use the 2008 provider SQLNCLI10

"SQL Server 2008 Books Online (October 2008) - Installing SQL Server Native Client"

https://msdn.microsoft.com/en-us/library/ms131321.aspx

"SQL Server 2005 Books Online (September 2007) - Installing SQL Native Client"

https://msdn.microsoft.com/en-us/library/ms131321(SQL.90).aspx

Connectionstrings, mixing usernames and windows authentication. Who goes first?

Additional resources