SQL Server String connection and its meaning

SQL Server String connection and its meaning

Commonly used declarations in connection strings include:

The server declares data source, server, and ADDR.

Database declaration initial catalog and database.

Integrated Windows Account Security statements, such as integrated security and trusted_connection.

Use the Security Statement of the database account, such as user ID and password.

For the account accessing the database, we usually see the string connection of ADO. Net in some references as follows:

Copy codeThe Code is as follows:

String connstr = "Server = localhost;
User ID = sa; Password = xxx; database = northwind ";

For an account integrated with Windows security, the connection string is generally written as follows:

Copy codeThe Code is as follows: String connstr = "Server = localhost;
Integrated Security = sspi; database = northwind ";
Or string connstr = "Server = localhost;
Trusted_connection = yes; database = northwind ";

Using Windows integrated security verification has many advantages in accessing the database: higher security, faster access, less security architecture re-design, hard-coded connection strings, etc, it is worth using.

SQL native client ODBC driver

Standard secure connectionCopy codeThe Code is as follows: Driver = {SQL native client}; server = myserveraddress; database = mydatabase; uid = myusername; Pwd = mypassword;

Are you using SQL Server 2005 Express? Use the connection expression "Host Name \ sqlexpress" in the "server" option ".

Trusted connectionCopy codeThe Code is as follows: Driver = {SQL native client}; server = myserveraddress; database = mydatabase; trusted_connection = yes;

"Integrated Security = sspi" is the same as "trusted_connection = yes.

Connect to an SQL server instance
The expression of the specified server instance is the same as the connection string of other SQL servers.
Driver = {SQL native client}; server = myservername \ theinstancename; database = mydatabase; trusted_connection = yes;

User name and password
Oconn. properties ("prompt") = adpromptalways

Driver = {SQL native client}; server = myserveraddress; database = mydatabase;

Use Mars (multiple active result sets)

Driver = {SQL native client}; server = myserveraddress; database = mydatabase; trusted_connection = yes; mars_connection = yes;

"Multipleactiveresultsets = true" is the same as mars_connection = yes.
Use ADO. NET 2.0 as the module of Mars. Mars does not support ADO. NET 1.0 and ADO. NET 1.1.

Verify Network Data

Driver = {SQL native client}; server = myserveraddress; database = mydatabase; trusted_connection = yes; encrypt = yes;

Connect to the local SQL Server express instance by attaching a local database file

Driver = {SQL native client}; server =. \ sqlexpress; attachdbfilename = c: \ ASD \ qwe \ mydbfile. MDF; database = dbname; trusted_connection = yes;

Why use database parameters? If a database with the same name has been attached, SQL server will not attach it again.

Connect to the local SQL Server express instance by attaching a database file in the local data folder

Driver = {SQL native client}; server =. \ sqlexpress; attachdbfilename = | datadirectory | mydbfile. MDF; database = dbname; trusted_connection = yes;

Why use database parameters? If a database with the same name has been attached, SQL server will not attach it again.

Database image
Data Source = myserveraddress; failover partner = mymirrorserver; initial catalog = mydatabase; Integrated Security = true;

SQL native client OLE DB Provider

Standard connection

Provider = sqlncli; server = myserveraddress; database = mydatabase; uid = myusername; Pwd = mypassword;

Are you using SQL Server 2005 Express? Use the connection expression "Host Name \ sqlexpress" in the "server" option ".

Trusted connection

Provider = sqlncli; server = myserveraddress; database = mydatabase; trusted_connection = yes;

"Integrated Security = sspi" is the same as "trusted_connection = yes"

Connect to an SQL server instance
The expression of the specified server instance is the same as the connection string of other SQL servers.
Provider = sqlncli; server = myservername \ theinstancename; database = mydatabase; trusted_connection = yes;

Use account and password
Oconn. properties ("prompt") = adpromptalways

Oconn. Open "provider = sqlncli; server = myserveraddress; database = mydatabase;

Use Mars (multiple active result sets)

Provider = sqlncli; server = myserveraddress; database = mydatabase; trusted_connection = yes; marsconn = yes;

"Multipleactiveresultsets = true" and "mars_connection = yes" are the same.
Use ADO. NET 2.0 as the module of Mars. Mars does not support ADO. NET 1.0 and ADO. NET 1.1.

Verify Network Data

Provider = sqlncli; server = myserveraddress; database = mydatabase; trusted_connection = yes; encrypt = yes;

Connect to the local SQL Server express instance by attaching a local database file

Provider = sqlncli; server =. \ sqlexpress; attachdbfilename = c: \ ASD \ qwe \ mydbfile. MDF; database = dbname; trusted_connection = yes;

Why use database parameters? If a database with the same name has been attached, SQL server will not attach it again.

Connect to the local SQL Server express instance by attaching a database file in the local data folder

Provider = sqlncli; server =. \ sqlexpress; attachdbfilename = | datadirectory | mydbfile. MDF; database = dbname; trusted_connection = yes;

Why use database parameters? If a database with the same name has been attached, SQL server will not attach it again.

Database image
Data Source = myserveraddress; failover partner = mymirrorserver; initial catalog = mydatabase; Integrated Security = true;

Sqlconnection (. NET)

Standard connection

Data Source = myserveraddress; initial catalog = mydatabase; user id = myusername; Password = mypassword;

You can use servername \ InstanceName as the data source to specify an SQL server instance.
Are you using SQL Server 2005 Express? Use the connection expression "Host Name \ sqlexpress" in the "server" option ".

Standard Security alternative syntax
Server = myserveraddress; database = mydatabase; user id = myusername; Password = mypassword; trusted_connection = false;

Trusted connection

Data Source = myserveraddress; initial catalog = mydatabase; Integrated Security = sspi;

Trusted connection alternative syntax
Server = myserveraddress; database = mydatabase; trusted_connection = true;

Connect to an SQL server instance
The expression of the specified server instance is the same as the connection string of other SQL servers.
Server = myservername \ theinstancename; database = mydatabase; trusted_connection = true;

Secure connection from wince Device
Data Source = myserveraddress; initial catalog = mydatabase; Integrated Security = sspi; user id = mydomain \ myusername; Password = mypassword;

Only applicable to CE devices.

Connections with IP addresses

Data Source = 190.190.200.100, 1433; network library = dbmssocn; initial catalog = mydatabase; user id = myusername; Password = mypassword;

Use Mars (multiple active result sets)

Server = myserveraddress; database = mydatabase; trusted_connection = true; multipleactiveresultsets = true;

Use ADO. NET 2.0 as the module of Mars. Mars does not support ADO. NET 1.0 and ADO. NET 1.1.

Connect to the local SQL Server express instance by attaching a local database file

Server =. \ sqlexpress; attachdbfilename = c: \ ASD \ qwe \ mydbfile. MDF; database = dbname; trusted_connection = yes;

Why use database parameters? If a database with the same name has been attached, SQL server will not attach it again.

Connect to the local SQL Server express instance by attaching a database file in the local data folder

Server =. \ sqlexpress; attachdbfilename = | datadirectory | mydbfile. MDF; database = dbname; trusted_connection = yes;

Why use database parameters? If a database with the same name has been attached, SQL server will not attach it again.

Use a user instance on an SQL Server express instance
Data Source =. \ sqlexpress; Integrated Security = true; attachdbfilename = | datadirectory | \ mydb. MDF; user instance = true;

Database image
Data Source = myserveraddress; failover partner = mymirrorserver; initial catalog = mydatabase; Integrated Security = true;

Asynchronous Processing
Server = myserveraddress; database = mydatabase; Integrated Security = true; asynchronous Processing = true;