SQL Server 2008 Connection string notation

Source: Internet
Author: User
Tags failover ole wrapper sql server express

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 securityConnection 
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
Data Source = 190.168.1.100,1433
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 function creates a new one in the connection process . An instance of SQL Server. 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
10.0}; Server = Myserveraddress;database = MyDataBase; Uid = MyUserName; PWD = mypassword;

Trust Connection
10.0}; Server = Myserveraddress;database = MyDataBase; Trusted_Connection = yes;

connecting an instance of SQL Server
10.0
Database = MyDataBase; Trusted_Connection = yes;

user name, password hint
Oconn.properties ("Prompt") = adPromptAlways
10.0}; Server = Myserveraddress;database = MyDataBase;

turn on the Mars function (multiple active result sets)
10.0
Mars_connection = yes;

Encryption Mode
10.0
Trusted_Connection = yes; Encrypt = yes;
attaching a database file when connecting to an instance of SQL Server Express
10.0
Database = dbname; Trusted_Connection = Yes;

attach a database file from the data directory when connecting to an instance of SQL Server Express
10.0
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.

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

Provider=sqlxmloledb. 4.0
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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.