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

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  amp  OleDB provider   Here s the full set of syntaxes according to MSDN - Connection String Syntax  ADO NET

User · Answer

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

User · Answer

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    What is sspi  Connection Strings Syntax

User · Answer

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

[sql-server] What is the difference between Integrated Security = True and Integrated Security = SSPI?

Examples related to sql-server

Examples related to security

Examples related to connection-string

Examples related to database-security