What is the difference between Integrated Security = True and Integrated Security = SSPI?
Solution 1
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
.
Solution 2
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)
Solution 3
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;
Source: MSDN: Working with Connection Strings
Solution 4
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:
Solution 5
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
JD.
Updated on August 01, 2022Comments
-
JD. almost 2 years
I have two apps that use Integrated Security. One assigns
Integrated Security = true
in the connection string, and the other setsIntegrated Security = SSPI
.What is the difference between
SSPI
andtrue
in the context of Integrated Security?-
Pranav Singh almost 10 yearsThe accepted answer is not the best one, its not fully correct either.
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. -
Mark over 9 years@PranavSingh has the right idea, this question is incomplete unless you specify which provider you are using. Different providers accept and/or translate various strings into internal states.
-
Hassan Faghihi over 7 yearsAlthough they are same, I believe that there was a very old document in one of websites, at the time i was curious same as you, that said if you are developing for windows mobile (not what you see today, the old devices which i don't remember the OS suffix since i never had one), you should use SSPI, and User Password together. but since i never wrote one, and i don't remember the source of that document, i cannot guarantee it.
-
ATL_DEV almost 3 yearsWhat is SSPI short for? The "SS" hopefully means SQL Server, but not sure what SI means.
-
-
eugened almost 15 yearsOriginally, I think there was a difference in that "True" used NTLM and "SSPI" used Kerberos, but they're now interchangeable.
-
JD. almost 15 yearsThanks for the response. Any reason why it works with one and not the other? In fact, if recall correctly, the error obtained when I used "true" was about some driver (on a 2003 windows server with sql server express). JD.
-
Johnny_D about 12 yearsDidn't check last comment, but if true, should be as answer, but not the comment
-
Barış Velioğlu about 12 years@RodneyFoley I use SSPI with wrong username and password, but it doesnt care and connected successfully in net4.0. Is this expected result ?
-
Kirk Broadhurst almost 12 years@RodneyFoley Do you have a source for that statement? This seems unusual as integrated is preferred over SQL authentication, so a system that defaults to SQL auth seems unlikely.
-
Rodney S. Foley almost 12 years@KirkBroadhurst MSDN Doc's for connection strings, and real world usage.
-
Kirk Broadhurst almost 12 years@RodneyFoley sorry, wasn't clear enough. Can you provide a source for that statement? I'm googling it and can't find any such advice.
-
Kirk Broadhurst almost 12 years@RodneyFoley sorry, my tests confirm that this answer is correct and your comment is not. Maybe it worked that way once, but it doesn't now, and you can't provide any reference to a Microsoft doc that supports your opinion.
-
Amit Shishodia over 9 years
-
Alex des Pelagos over 9 yearsAgree with Kirk. User / password is ignored when SSPI specified - .net 4.0, SQL server 2012.
-
Jason Goemaat over 8 years@RodneyFoley and @KirkBroadhurst: Looking at other answers it looks like it might depend on the provider used. If talking about
System.Data.SqlClient
, I did find this article which saysTrue
means thatUser Id
andPassword
will be ignored andSSPI
means that they will be be used if present, but Windows Security will be used if not. This link says true will ignore them but doesn't mention SSPI. -
Zé Carlos almost 8 yearsSo if they "are the same thing" why is SSPI "strongly recommended" rather than "true" or "yes? That's the reason why I came to this question...
-
underscore_d over 6 yearsThe connection string is not necessarily visible to any employee.
-
Zé Carlos over 6 years@PranavSingh: It's not me who is saying they are the same. Is the current answer which we are commenting...
-
Pranav Singh over 6 years@ZéCarlos Ok, Got it. Somehow that is accepted answer with highest votes :) . I added answer for same clarification
-
Pranav Singh almost 6 yearsThat part of code is property only for one case that is explainable by name
ConvertValueToIntegratedSecurityInternal
. That property is used only when provider isSqlClient
so inSqlClient
,SSPI
&true
are same but not when client isOleDb
orOracleClient
. I have clarified that in stackoverflow.com/a/23637478/704008 with msdn reference -
Kelly over 4 yearsIf it is only used to say you are using Windows Authentication, wonder why they didn't just call the setting/property "Windows Authentication" instead of Integrated Security?
-
Yola over 3 yearsIsn't this answer repeats the third rated one?
-
interDist over 3 years@Yola this answer is a bit more complete and also links to a still-valid Microsoft Docs page (the link in the other answer now brings you to a page suggesting to download Visual Studio 2005 Retired docs).