I have two apps that use Integrated Security. One assigns Integrated Security = true
in the connection string, and the other sets Integrated Security = SSPI
.
What is the difference between SSPI
and true
in the context of Integrated Security?
According to Microsoft they are the same thing.
When
false
, User ID and Password are specified in the connection. When true, the current Windows account credentials are used for authentication.
Recognized values aretrue
,false
,yes
,no
, andsspi
(strongly recommended), which is equivalent totrue
.
Integrated Security=true;
doesn't work in all SQL providers, it throws an exception when used with the OleDb
provider.
So basically Integrated Security=SSPI;
is preferred since works with both SQLClient
& OleDB
provider.
Here's the full set of syntaxes according to MSDN - Connection String Syntax (ADO.NET)
Using Windows Authentication
To connect to the database server is recommended to use Windows Authentication, commonly known as integrated security. To specify the Windows authentication, you can use any of the following two key-value pairs with the data provider. NET Framework for SQL Server:
Integrated Security = true;
Integrated Security = SSPI;
However, only the second works with the data provider .NET Framework OleDb. If you set Integrated Security = true
for ConnectionString an exception is thrown.
To specify the Windows authentication in the data provider. NET Framework for ODBC, you should use the following key-value pair.
Trusted_Connection = yes;
Many questions get answers if we use .Net Reflector
to see the actual code of SqlConnection
:)
true
and sspi
are the same:
internal class DbConnectionOptions
...
internal bool ConvertValueToIntegratedSecurityInternal(string stringValue)
{
if ((CompareInsensitiveInvariant(stringValue, "sspi") || CompareInsensitiveInvariant(stringValue, "true")) || CompareInsensitiveInvariant(stringValue, "yes"))
{
return true;
}
}
...
EDIT 20.02.2018 Now in .Net Core we can see its open source on github! Search for ConvertValueToIntegratedSecurityInternal method:
Integrated Security = False : User ID and Password are specified in the connection. Integrated Security = true : the current Windows account credentials are used for authentication.
Integrated Security = SSPI : this is equivalant to true.
We can avoid the username and password attributes from the connection string and use the Integrated Security
Let me start with Integrated Security = false
false
User ID and Password are specified in the connection string.true
Windows account credentials are used for authentication.
Recognized values are true
, false
, yes
, no
, and SSPI
.
If User ID
and Password
are specified and Integrated Security is set to true
, then User ID
and Password
will be ignored and Integrated Security will be used
Note that connection strings are specific to what and how you are connecting to data. These are connecting to the same database but the first is using .NET Framework Data Provider for SQL Server. Integrated Security=True will not work for OleDb.
When in doubt use the Visual Studio Server Explorer Data Connections.
Integrated Security = True
orSSPI
are not same.Integrated Security=true;
doesn't work in all SQL providers, it throws an exception when used with theOleDb
provider. So basicallyIntegrated Security=SSPI;
is preferred since works with bothSQLClient
&OleDB
provider. I have added an answer for better clarification. – Pranav Singh