SQL Server express database connection configuration in the web. config file

From: http://www.cnblogs.com/aodailiheben/archive/2008/08/06/1262227.html

For the sqlserverexpress database connection configuration, I have seen it several times before. I have seen it again in an open-source software this morning. I feel it is necessary to have a clear understanding of it, example:
<Add name = "localsqlserver" connectionstring = "Data Source =. \ sqlexpress; Integrated Security = true; attachdbfilename = | datadirectory | timetracker. MDF; user instance = true"/>
Connectionstring is used when sqlconnectionstringbuilder is instantiated, for example, sqlconnectionstringbuild builder = new sqlconnectionstringbuild (connectionstring ).

I. Data Source
The datasource attribute of sqlconnectionstringbuilder corresponds to data source in connectionstring. "Data Source" can be replaced by the following strings: "server", "Address", "ADDR", and "network address ".
Data Source =. \ sqlexpress can also be written as Data Source = (local) \ sqlexpress.

Ii. Integrated Security
The integratedsecurity attribute of sqlconnectionstringbuilder corresponds to the Integrated Security in connectionstring. "Integrated Security" can be written as "trusted_connection". If it is true, the current Windows Account creden are used for authentication. If it is false, you must specify the user ID and password in the connection.

3. attachdbfilename
The attachdbfilename attribute of sqlconnectionstringbuilder corresponds to attachdbfilename in connectionstring. "attachdbfilename" can be written as "extended properties" and "initial file name ". The attachdbfilename attribute specifies the location of the database files dynamically appended to the server when the connection is opened. This attribute can accept the full path and relative path of the database (for example, | datadirectory | syntax). This path will be applied at runtime.ProgramThe app_data directory.
One articleArticleSpecifically, it explains what datadirectory is.

Iv. user instance
The userinstance attribute of sqlconnectionstringbuilder corresponds to the user instance in connectionstring. This value indicates whether to redirect the connection from the default SQL Server express instance to the instance that runs under the caller account and starts at runtime. Userinstance = true. In this case, sqlserverexpress creates a new process to attach the database to a new instance and runs it under the user identity that opens the connection. In ASP. NET applications, this user is a local ASPNET account or the default networkservice, which depends on the operating system. To securely append database files provided by non-system administrator accounts (such as ASP. NET accounts), it is necessary to create an independent sqlserver user instance.

Appendix:

What is datadirectory?

Asp.net 2.0 has a special directory app_data. Generally, the SQL Server 2005 express data file is stored in this directory. The corresponding database connection string is:
Connectionstring = "...... Data Source =. \ sqlexpress; Integrated Security = sspi; attachdbfilename = | datadirectory | data. MDF; user instance = true"
Here is a macro of datadirectory. What does it mean?

Datadirectory indicates the replacement string of the database path. Datadirectory simplifies project sharing and application deployment because it does not require hard coding of the complete path. For example, you do not need to use the following connection string:
"Data Source = c: \ Program Files \ MyApp \ app_data \ mydb. MDF"
Use | datadirectory | (included in the following vertical line) to have the following connection string:
"Data Source = | datadirectory | \ mydb. MDF ".

It is not only used in SQL Server 2005 Express, but also in other file databases, such as the connection string of sqllite database files:

<Add name = "defaultdb"
Connectionstring = "driverclass = nhib.pdf. Driver. sqlite20driver; dialect = nhib.pdf. dialect. sqlitedialect; Data Source = | datadirectory | \ data. db3"/>

 

Appendix:

Explanation of common terms in config: (I found it online for convenient use)

The persist Security info attribute indicates whether to save the security information. In fact, it can be simply understood as "whether to save the password information after the database connection is successful ",
True indicates saving, false indicates not saving
ADO is true by default.
(The default value of ado.net is false. It is not tested, as described in reference)

Integrated Security: when it is set to true, the preceding userid and PW do not work. Only when it is set to false, it is connected by userid and PW.
Integrated security can be set to: True, false, yes, no. The four values are clear and can also be set to: sspi, which is equivalent to true. We recommend that you use this instead of true.

The new features of user instance, in short, allow the current user to use it without being an administrator. The default value is true;

 

Detailed description of the web. config configuration file