C # -- Integrated Security = SSPI in Web. config,

C # -- Integrated Security = SSPI in Web. config,
Problem cause

In the past, the configuration file was used to access the database when the charging system of the IDC room was restructured. For example:

   string="server=localhost;database=pubs;uid=sa;password=123456"

During the two days of learning MVC, when setting the configuration file, we found that another method is used to link the database:

connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=aspnet-MVCMovie-20141126170450;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\Movies.mdf" providerName="System.Data.SqlClient"

The focus is on the Integrated Security = SSPI sentence. What is the difference ??

Two authentication modes for Databases

Windows Authentication Mode

During installation, if you follow the default operation (windows Authentication mode), SQL server uses the current windows Account and its password as the embedded Login Name (BUILTIN \ Administrators) and password in windows Authentication mode, therefore, you do not need to specify the logon name and password when using windows Authentication mode. Of course, you can also specify another windows account as the embedded login name in windows Authentication Mode and follow the prompts to modify it. In Windows Authentication mode, only connections in windows Authentication mode are allowed. Even if you can select SQL server mode in SSMS, the connection may fail.

Hybrid Authentication Mode

If you select "mixed mode" during installation, you must specify the sa Login Name password to add a login name for SQL server Authentication.

Different Methods

Windows authentication is more secure than Hybrid Authentication. When using this connection mode, SQL server performs authentication only based on the user's windows permissions. This is called "trusted connection ", however, during remote connection, you cannot log on because of NTML verification.
Mixed-Mode authentication is complex. When a local user accesses SQL, windows authentication is used to establish a trusted connection. When a remote user accesses SQL, windows Authentication fails, for SQL server Authentication (sa users can also log on to SQL), and establish a "untrusted connection", so that remote users can log on.
To be accurate, the Hybrid Authentication mode is based on Windows identity authentication and SQL Server identity Hybrid Authentication. In this mode, the system determines whether the account is trusted in the Windows operating system. For trusted connections, the system uses Windows Authentication instead of trusted connections, this connection includes not only remote users but also local users. SQL Server automatically verifies the connection through account existence and password matching.

Common misunderstanding

The difference between windows Authentication Mode and SQL server Authentication mode is that permission is incorrect. The difference between the two authentication methods is not permission. No one has high permissions or has low permissions. Whether it is the default login name in windows Authentication mode, the sa in SQL server mode, or other self-added login names, you only need to set the corresponding database and table permissions for the login name in SQL server, it has operation permissions.

Intergrated Security = SSPI

SSPI: Security Support Provider Interface (Microsoft Security Support Provider Interface), which is defined as a comprehensive public API for integrated Security services such as verification, information integrity, and information privacy, and security services for all distributed application protocols.

========================================================== ===
Integrated Security Authentication Method
If this parameter is set to False, the user ID and password are specified in the connection.
If this parameter is set to True, the current Windows Account creden。 are used for authentication.
The identifiable values are True, False, Yes, No, and SSPI equivalent to True.

========================================================== ====

Summary

To ensure database security, we recommend that you use Windows Authentication mode whenever possible. Windows Authentication uses a series of encrypted messages to authenticate users in SQL Server. When you log on to SQL Server, the logon name and password of SQL Server are transmitted over the network, which reduces their security.