Connection string for SQL Server Windows Authentication and SQL Server Authentication

Source: http://www.cnblogs.com/lanse777/archive/2007/03/28/691757.html

The SQL Server. NET data provider connection string contains a set of attribute name/value pairs. Each attribute/value pair is separated by a semicolon. Propertyname1 = value1; propertyname2 = value2; propertyname3 = value3; ...... Similarly, the connection string must contain the SQL server instance name: Data Source = servername;

If you want to use a remote server to run SQL Server (localhost), you should assign the correct server to the data source attribute in the sample object. In addition, you must specify either of the two supported authentication methods (Windows Authentication and SQL Server Authentication. Windows Authentication uses Windows login user identity to connect to the database, and SQL authentication requires that the SQL Server user ID and password be explicitly specified.To use Windows authentication, you must include the Integrated Security attribute in the connection string:

Data Source = servername; Integrated Security = true;

By default, the Integrated Security attribute is false, which means Windows authentication is disabled. If the value of this attribute is not explicitly set to true, the connection uses SQL Server Authentication. Therefore, the SQL Server user ID and password must be provided. The integrated security attribute can recognize other values only sspi (Security Support Provider Interface, Security Support Provider Interface ). all Windows NT operating systems, including Windows NT 4.0, 2000, and XP, support sspi. It is the only interface that can be used for Windows authentication. It is equivalent to setting the value of integrated security to true.

In Windows Authentication mode, SQL server uses the Windows security subsystem to verify the validity of user connections. Even if the user ID and password are explicitly specified, SQL server does not check the user ID and password in the connection string. Because only Windows NT, 2000, and XP support sspi, if you are using these operating systems, you can only use Windows integrated security policies to connect to SQL Server.No matter which operating system you use, when using SQL Server Authentication, you must specify the user ID and password in the connection string:

Data Source = servername; user id = donaldx; Password = unbreakable

By default, SQL Server. NET data provider connects to the default database of the specified user. When creating a user in the database, you can set the default database of the user. In addition, you can change your default database at any time. For example, the default database of the system administrator is master.To connect to different databases, specify the Database Name:

Data Source = servername; Integrated Security = sspi; initial catalog = northwind

Each authentication method has its advantages and disadvantages. Windows Authentication uses a single user information library source. Therefore, you do not need to configure users for database access separately. The connection string does not contain the user ID and password, which eliminates the risk of exposing the user ID and password to unauthorized users. Users and their roles can be managed in Active Directory without explicitly configuring their properties in SQL Server. The disadvantage of Windows authentication is that it requires the user to connect to SQL Server through the Secure Channel supported by the Windows security subsystem. If the application needs to connect to SQL Server through an insecure network (such as the Internet), Windows authentication will not work. In addition, this authentication method also partially transfers the responsibility for managing database access control from the DBA to the system administrator, which may be a problem in the identified environment.

In general, it is used to design general applications.ProgramTo use Windows authentication, some aspects will be enhanced. Most companies' databases reside on robust Windows server operating systems that support Windows authentication. The separation of the data access layer and the data presentation layer also promotes data access.CodeApplications encapsulated in the middle layer component idea, middle layer components usually run in the internal network with database servers. In this design, you do not need to establish a database connection through an insecure channel. In addition, Web Services also greatly reduce the need to directly connect to databases in different domains.