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


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


to


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.


 


Login failed for user ”


 


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)”


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


“.NET Framework Class Library – SqlConnection.ConnectionString Property”


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


“.NET Framework Class Library – OleDbConnection.ConnectionString Property”


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


“.NET Framework Class Library – OdbcConnection.ConnectionString Property”


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


“.NET Framework Developer’s Guide – Protecting Connection Information (ADO.NET)”


http://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”


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


“SQL Server 2005 Books Online (September 2007) – Installing SQL Native Client”


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

Comments (3)

  1. Rakesh says:

    For ODBC windows authentication you can also use the following

    "Driver={SQL Server};Server=(local);Trusted_Connection=Yes;Database=AdventureWorks;"

  2. Ty B says:

    Excellent article explaining the different connection strings. I really appreciate it.

Skip to main content