SQL Server 2008 Connection string notation

This article transferred from: http://www.cnblogs.com/tough/archive/2011/11/18/2254076.html

I.. NET Framework Data Provider for SQL Server

Type:. NET Framework Class Library
Use: System.Data.SqlClient.SqlConnection
Vendor: Microsoft

Standard secure connection

Data Source = myserveraddress;initial Catalog = myDataBase; User Id = MyUserName; Password = mypassword;

Specifies the data source for the instance of SQL Server using the server name \ Instance Name as the connection. If you are using SQL Server Express Edition, the instance name is SQLEXPRESS.

Replaceable Standard secure connection

Server = Myserveraddress;database = MyDataBase; User ID = MyUserName; Password = mypassword; Trusted_Connection = False;

This connection string has the same effect as the previous one. To write this out just to say, in fact, many of the connection string keyword has a variety of wording.

Trust connection

Data Source = myserveraddress;initial Catalog = mydatabase;integrated Security = SSPI;

Alternative trusted connections

Server = Myserveraddress;database = MyDataBase; Trusted_Connection = True;

Connect a trusted connection to a Windows CE device

Typically, a Windows CE device cannot be authenticated and logged on in a domain. In order for a CE device to use SSPI or trust connection and authentication, you can use the following connection string:

\myusername; Password = mypassword;

Note that this statement can only be used on CE devices.

Connect using an IP address

ID = MyUserName; Password = mypassword;

This statement replaces the named pipe with a TCP/IP address. At the end of the data source field is the port used. The default port used by SQL Server is 1433.

Turn on the Mars function (multiple active result sets)

Server = Myserveraddress;database = MyDataBase; Trusted_Connection = True; Multipleactiveresultsets = true;

The Mars does not support ADO 1.0 and ADO 1.1.

Attaching a database file when connecting to an instance of SQL Server Express

Server =. \SQLExpress; AttachDbFileName = C:\asd\qwe\mydbfile.mdf;database = dbname; Trusted_Connection = Yes;

Why do I need the database field here? Because if the specified database file is already attached, SQL Server no longer attaches it, and the attached database is used as the default database.

Attach a database file from the data directory when connecting to an instance of SQL Server Express

Server =. \SQLExpress; AttachDbFileName = | Datadirectory|mydbfile.mdf; Database = dbname; Trusted_Connection = Yes;

Using a user instance on a local SQL Server Express instance

User instance this feature creates a new instance of SQL Server during the connection process. This feature is only available on the local SQL Server instance and is connected using Windows authentication through the local named channel. The benefit of this is that you can create a new instance of SQL Server with full permissions for a user who has only a fairly limited amount of administrator rights on the local computer.

Data Source =. \sqlexpress;integrated Security = true; AttachDbFileName = | Datadirectory|\mydb.mdf; User Instance = true;

If you want to use the user instance feature, you need to turn it on in SQL Server first. The Open command is:

sp_configure ' user instances enabled ', ' 1 '

The Cancel command is:

sp_configure ' user instances enabled ', ' 0 '

Note: These two commands are only valid in SQL Server Express.

Database Mirroring

If you use the ADO or SQL Native client to connect to a database image, your application can use the features of the drive to automatically redirect the connection when the database image fails over. Of course, you must specify the initial primary server and database in the Connection field, as well as the mirror server for failover.

Security = True;

The above example just describes how to use database mirroring, and you can use the Failover partner field with other connection string features.

Asynchronous Processing

Server = Myserveraddress;database = mydatabase;integrated Security = True; asynchronous processing = True;

Ii. SQL Server Native Client 10.0 OLE DB Provider

Type: OLE DB Provider
Use: provider=sqlncli10
Vendor: Microsoft

Standard secure connection

Provider = SQLNCLI10; Server = Myserveraddress;database = MyDataBase; Uid = MyUserName; PWD = mypassword;

Trust connection

Provider = SQLNCLI10; Server = Myserveraddress;database = MyDataBase; Trusted_Connection = yes;

"Integrated Security=sspi" and "Trusted_connection=yes" are equivalent.

Connecting an instance of SQL Server

Provider = SQLNCLI10; Server = Myservername\theinstancename;database = MyDataBase; Trusted_Connection = yes;

User name, password hint

Oconn.properties ("Prompt") = adPromptAlways
oConn.Open "Provider = SQLNCLI10; Server = Myserveraddress;database = MyDataBase;

Turn on the Mars function (multiple active result sets)

Provider = SQLNCLI10; Server = Myserveraddress;database = MyDataBase; Trusted_Connection = yes; MARS Connection = True;

Encryption mode

Provider = SQLNCLI10; Server = Myserveraddress;database = MyDataBase; Trusted_Connection = yes; Encrypt = yes;

attaching a database file when connecting to an instance of SQL Server Express

Trusted_Connection = Yes;

attach a database file from the data directory when connecting to an instance of SQL Server Express

Database = dbname; Trusted_Connection = Yes;

Database Mirroring

Catalog = mydatabase;integrated Security = True;

Iii.. NET Framework Data Provider for OLE DB

Type:. NET Framework Wrapper Class Library
Use: System.Data.OleDb.OleDbConnection
Vendor: Microsoft

Bridge to SQL Native Client OLE DB

Provider = SQLNCLI10; Server = Myserveraddress;database = MyDataBase; Uid = MyUserName; PWD = mypassword;

Iv. SQL Server Native Client 10.0 ODBC Driver

Type: ODBC Driver
Use: Driver={sql Server Native Client 10.0}
Vendor: Microsoft

Standard secure connection

Driver = {SQL Server Native Client 10.0}; Server = Myserveraddress;database = MyDataBase; Uid = MyUserName; PWD = mypassword;

Trust connection

Driver = {SQL Server Native Client 10.0}; Server = Myserveraddress;database = MyDataBase; Trusted_Connection = yes;

connecting an instance of SQL Server

Database = MyDataBase; Trusted_Connection = yes;

User name, password hint

Oconn.properties ("Prompt") = adPromptAlways
Driver = {SQL Server Native Client 10.0}; Server = Myserveraddress;database = MyDataBase;

turn on the Mars function (multiple active result sets)

Mars_connection = yes;

Encryption mode

Trusted_Connection = yes; Encrypt = yes;

attaching a database file when connecting to an instance of SQL Server Express

Database = dbname; Trusted_Connection = Yes;

attach a database file from the data directory when connecting to an instance of SQL Server Express

Database=dbname; Trusted_connection=yes;

Database Mirroring

10.0}; Server = myserveraddress; Failover_partner = Mymirrorserveraddress;database = MyDataBase; Trusted_Connection = yes;

V.. NET Framework Data Provider for ODBC

Type:. NET Framework Wrapper Class Library
Use: System.Data.Odbc.OdbcConnection
Vendor: Microsoft

Bridge to SQL Native Client 10.0 ODBC Driver

The following statement is just an example of the different ODBC drivers for different vendors.

Driver={sql Server Native Client 10.0}; Server=myserveraddress;database=mydatabase; Uid=myusername; Pwd=mypassword;

VI. SQLXML 4.0 OLE DB Provider

Type: OLE DB Provider
Use: Provider=sqlxmloledb.4.0;data provider=providername
Vendor: Microsoft

Id=myusername; Password=mypassword;

Vii. Context Connection

Type:. NET Framework Class Library
Use:
Vendor: Microsoft

Connect to "itself" through stored procedures/functions in the current CLR. A context connection allows you to execute T-SQL statements in the context (connection) where your code is called the first time.

C#

using (SqlConnection connection = new SqlConnection ("Context connection=true"))
{
    Connection. Open ();
    Use the connection
}


vb.net

Using Connection as New SqlConnection ("Context connection=true")
    Connection. Open ()
    ' Use the connection
End Using

SQL Server 2008 Connection string notation