What is the difference between Integrated Security = True and Integrated Security = SSPI?

575
votes

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?

9
sql-serversecurityconnection-stringdatabase-security
The accepted answer is not the best one, its not fully correct either. Integrated Security = True or SSPI are not same. 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. I have added an answer for better clarification. – Pranav Singh
@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. – Mark
Although 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. – deadManN

9 Answers

461
votes

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 are true, false, yes, no, and sspi (strongly recommended), which is equivalent to true.

201
votes

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)

![Windows Auth Syntax

75
votes

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

34
votes

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:

https://github.com/dotnet/corefx/blob/fdbb160aeb0fad168b3603dbdd971d568151a0c8/src/System.Data.SqlClient/src/System/Data/Common/DbConnectionOptions.cs

24
votes

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

15
votes

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

8
votes

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.

  • Data Source=.;Initial Catalog=aspnetdb;Integrated Security=True
  • Provider=SQLOLEDB;Data Source=.;Integrated Security=SSPI;Initial Catalog=aspnetdb

When in doubt use the Visual Studio Server Explorer Data Connections.

6
votes

True is only valid if you're using the .NET SqlClient library. It isn't valid when using OLEDB. Where SSPI is bvaid in both either you are using .net SqlClient library or OLEDB.

3
votes

In my point of view,

If you dont use Integrated security=SSPI,then you need to hardcode the username and password in the connection string which means "relatively insecure" why because, all the employees have the access even ex-employee could use the information maliciously.