SQL Server connection string and authentication detailed

The SQL Server. NET Data Provider Connection string contains a collection of property 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;
with local SQL Server (localhost), if you want to run with a remote server, you should assign the correct server to the data Source property in the sample object. In addition, you must specify one of the two supported authentication methods (that is, Windows authentication and SQL Server Authentication). Windows authentication uses the Windows logon user 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 property is False, which means that Windows authentication is disabled. If you do not explicitly set the value of this property to true, the connection will use SQL Server authentication, so you must provide the SQL Server user ID and password. Other values that the Integrated security attribute can recognize are only SSPI (Security support Provider Interface, secure Provider Interface). On all Windows NT operating systems, including Windows NT 4.0, 2000, XP, all support the value SSPI. It is the only interface that can be used when using Windows Authentication, which is equivalent to setting the integrated Security property value to True.

In Windows Authentication mode, SQL Server uses the Windows security subsystem to validate user connections. SQL server does not check the user ID and password in the connection string, even if the user ID and password are specified by the display. Because only Windows NT, 2000, and XP support SSPI, if you are using these operating systems, you can only use Windows Integrated Security policy to connect to SQL Server. Regardless of which operating system you use, when you use SQL Server authentication, you must specify a user ID and password in the connection string:
Data Source=servername; User ID=DONALDX; Password=unbreakable

By default, the SQL Server. NET Data Provider connection Specifies the user's default database, and when a user is created in the database, the user's default database can be set. In addition, you can change the user's default database at any time. For example, the system administrator's default database is master. If you want to connect to a different database, you should specify the name of the database:
Data source=servername;integrated security=sspi;initial Catalog=northwind

Each type of authentication has its advantages and disadvantages. Windows authentication uses a single user repository source, so you do not need to configure users for database access separately. The connection string does not contain a user ID and password, thereby eliminating the risk of exposing the user ID and password to unauthorized users. Users and their roles can be managed in Active Directory without having to explicitly configure their properties in SQL Server.
The disadvantage of Windows authentication is that it requires customers to connect to SQL Server through a secure channel that is supported by the security subsystem of Windows. If application sequencing is required to connect through an unsecured network (such as the Internet), SQL Server,windows authentication will not work. In addition, this authentication method also partially transfers the responsibility of managing database access control from the DBA to the system administrator, which may be a problem in a determined environment.
In general, in order to use Windows authentication when designing a generic application, some aspects will be enhanced. Most companies ' databases reside on a more robust Windows server operating system that supports Windows authentication. The separation of the data access layer and the data presentation layer also facilitates the application of encapsulating the data access code in the middle-tier component idea, which typically runs in the internal network with the database server. When this is designed, there is no need to establish a database connection through an unsecured channel. In addition, Web services make it much less necessary to directly connect to databases in different domains.

The connectivity of the database has evolved into a standard aspect of application development. The database connection string is now a standard requirement for each project. I found myself in order to find the syntax that I need, I often have to copy the connection string from another application or perform a search. This is especially true when interacting with SQL Server because it has too many connection string options. Now let's look at the many aspects of the connection string.

Connection string

During object instantiation or establishment, the database connection string is passed to the necessary objects through properties or methods. The format of the connection string is a semicolon-delimited list of key/value parameter pairs. listing A includes an example in C # that shows how to connect to SQL Server using the method of creating the SqlConnection object (the actual connection string is assigned through the object's ConnectionString property). The version of VB.net is included in list B .

List A

String cString = "Data source=server;initial catalog=db; User id=test; Password=test; ";
Sqlconnectionconn = new SqlConnection ();
Conn. ConnectionString = cString;
Conn. Open ();

List B

Dim cString as String
cString = "Data source=server;initial catalog=db; User id=test; Password=test; "

Dim conn as SqlConnection = New SqlConnection ()
Conn. ConnectionString = cString
Conn. Open ()

The connection string specifies the database server and database, as well as the user name and password required to access the database. However, this format does not apply to all database interactions, it does have many options available, many of which have synonyms.

Along with elements such as data source, Initial catalog (initial catalog), User ID (userid), and password (password), the following options are available:

Application Name (application name): The name of the application. If not specified, it has a value of. NET SqlClient data Provider.

AttachDbFileName / Extended Properties (extended attribute)/ Initial File Name (initial file name): The name of the primary file that can connect to the database, including the full path name. The database name must be specified with the keyword database.

Connect Timeout (Connection timeout)/ Connection Timeout (Connection Timeout): The length of time (in seconds) that a connection to the server waits before terminating, with a default value of 15.

Connection Lifetime (Connection Life time): When a connection is returned to the connection pool, its creation time is compared to the current time. If the time span exceeds the validity of the connection, the connection is canceled. Its default value is 0.

Connection Reset (Connection Reset): Indicates whether a connection is reset when it is removed from the connection pool. A pseudo-effective when a connection is obtained there is no need to perform an additional server back and forth, its default value is true.

Current Language (current language): The name of the SQL Server language record.

Data Source (data source)/ Server (server)/ Address (address)/ Addr (address)/ Network Address (Network address): The name or network address of the instance of SQL Server.

Encrypt (encrypted): when the value is true, SQL Server uses SSL encryption for all data transferred between the client and the server if the server has an authorization certificate installed. The accepted values are true (TRUE), False (pseudo), yes (yes), and no (NO).

Enlist (Enlistment): Indicates whether the connection pooling program automatically enlists the connection in the context of the current transaction in which the thread was created, with a default value of True.

Database (database)/ Initial Catalog (initial Catalog): The name of the database.

Integrated Security (Integrated security)/ Trusted Connection (Trusted connection): Indicates whether Windows authentication is used to connect to the database. It can be set to a true, pseudo, or an SSPI with true equivalence, and its default value is pseudo.

Max Pool Size (maximum connection pool capacity): The maximum number of connections allowed for a connection pool, with a default value of 100.

Min Pool Size (minimum connection pool capacity): The minimum number of connections allowed for a connection pool, with a default value of 0.

Network Library (Network library)/ Net (Network): A network library that is used to establish a connection to an instance of SQL Server. Supported values include: DBNMPNTW (Named Pipes), DBMSRPCN (MULTIPROTOCOL/RPC), Dbmsvinn (Banyan vines), DBMSSPXN (ipx/spx), and DBMSSOCN ( TCP/IP). The dynamic link library for the protocol must be installed to the appropriate connection with the default value of TCP/IP.

Packet Size (packet size): The size of the network packet used to communicate with the database. Its default value is 8192.

Password (password)/ PWD : The password that corresponds to the account name.

Persist Security Info (maintain security information): used to determine if the security information is available after the connection has been established. If the value is true, data that is sensitive to security, such as the user name and password, is available and is not available if the value is pseudo. Resetting the connection string will reconfigure the values of all connection strings, including passwords. Its default value is pseudo.

Pooling (Pool): Determines whether connection pooling is used. If the value is true, the connection is obtained from the appropriate connection pool, or, if necessary, the connection is created and then added to the appropriate connection pool. Its default value is true.

User ID (User ID ): The name of the account used to log in to the database.

Workstation ID(WorkstationID):The name of the workstation that is connected to SQL Server. The default value is the name of the local computer.

• Some preliminary experiences after solving some basic problems in SQL Server connection

• 
  

  Students do a question bank system, the ASP application written in C #, the database with SQL Server2000, give me a look. The problem occurs after you put it on the server. After repeated adjustment found the solution, in fact, very simple. Looking back, I found out that I didn't know enough about SQL Server's connection statement and user rights. The following will be some of my experience and online search for information about the collection, thought later, of course, very superficial.
  
  1. How SQL Server is connected

  
  

  Take the local server (LocalHost), the database (Northwind) as an example, there are several ways to connect SqlConnection conn=new SqlConnection ("server=localhost;integrated Security=sspi;database=northwind "); SqlConnection conn = new SqlConnection ("Data source=localhost;integrated security=sspi;initial catalog=northwind;"); SqlConnection conn = new SqlConnection ("Data source=localhost;initial catalog=northwind;integrated Security=sspi; Persist Security Info=false; Workstation Id=xurui; Packet size=4096; ");
  SqlConnection myconn = new SqlConnection ("Persist Security info=false;integrated security=sspi;database=northwind; Server=localhost ");
  SqlConnection conn = new SqlConnection ("UID=SA; Pwd=***;initial Catalog=northwind;data Source=localhost; Connect timeout=900 ");

  
  

  Experience:

  
  

  A.server and Database,data source and initial catalog are paired and can be replaced with each other (laughed at)
  B.integrated security default is False, you need to provide UID and PWD, will be logged into the database as a SQL Server user, if set to True,yes or SSPI, this can not appear uid and PWD, The database will be logged in the Windows user Province. It is highly recommended to use the latter form for higher security.
  c.integrated security and Persist Security info appear at the same time, which is set to false to guarantee information security.

  
  

  For more string connection descriptions, see MSDN:
  http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/ Frlrfsystemdatasqlclientsqlconnectionclassconnectionstringtopic.asp

  
  

  
  

  The connection string should be proficient in writing should be no problem, I am looking at other people's program, to tell the truth some things are really unclear. But after the problem is not connected, it has to be solved. So you have to understand the meaning of these keywords, modify and then test.

  
  

  
  

  2. User settings for SQL Server
  Problem one, using the connection string
  SqlConnection conn = new SqlConnection ("UID=SA; Pwd=***;initial Catalog=northwind;data Source=localhost; Connect timeout=900 ");
  Error:
  User "sa" failed to log on, no trusted SQL Server connection
  Find the solution after checking the data:
  Cause: The way SQL Server is validated needs to be set to a hybrid of SQL Server authentication and Windows Integrated authentication, and if set only to the latter way, it will appear as above
  WORKAROUND: Run SQL Server Enterprise Manager, click on the server, select Properties in the right-click menu, choose Security, change the authentication method
  Question two, using the connection string
  SqlConnection conn = new SqlConnection ("Data source=localhost;integrated security=sspi;initial catalog=northwind;");
  Error:
  User "Computername\iwam_servername" Login failed
  Cause: IWAM_servername is not included in the login user of SQL Server
  WORKAROUND: Run SQL Server Enterprise Manager, click on the server, select Security, select Login, new login to join IWAM_servername, and with the appropriate permissions, such as only access to the Northwind database, the role of the database is set to public and db_ Owner

  
  

  
  

  3, about the security of the connection

  
  

  
  

  It is best to connect to the database using SSPI's integrated security method, and the SA user's way to connect is a security risk, I think mainly because when the SQL Server is installed, often in order to access the convenient setting of the sa password is empty, once the hacker let SA become an administrator, you can gain all access to the system. Therefore, for the security of the database, you can set the SQL Server access users to only through Windows Integrated authentication, set the SA security password, enhance the security of the database. When set to Windows Integrated authentication, of course, the flexibility of database performance and access is bound to be affected, and administrators can set different authentication methods for each database without having to set up SQL Server in a uniform manner.

  
  

SQL Server connection string and authentication detailed