SQL Server 2008 Connection string notation

SQL Server 2008 Connection string notation


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
Data Source = 190.168.1.100, 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
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
 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)
 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.



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. 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