connection_strings_for_sql_server_2005

8/7/2019 Connection_strings_for_SQL_Server_2005

1/4


2/4

Attach a database file, located in the data directory, on connect to a local SQL ServerExpress instanceServer = .\SQLExpress; AttachDbFilename = |DataDirectory|mydbfile.mdf; Database =dbname; Trusted_Connection =Yes; Why is the Database parameter needed? If the named database have already been attached, SQL Server doesnot reattach it. It uses the attached database as the default for the connection.COPY TO CLIPBOARD

Using an User Instance on a local SQL Server Express instanceThe User Instance functionality creates a new SQL Server instance on the fly during connect. This works only ona local SQL Server 2005 instance and only when connecting using windows authentication over local namedpipes. The purpose is to be able to create a full rights SQL Server instance to a user with limited administrativerights on the computer.Data Source = .\SQLExpress; Integrated Security = true; AttachDbFilename = |DataDirectory|\mydb.mdf; User Instance = true; To use the User Instance functionality you need to enable it on the SQL Server. This is done by executing thefollowing command: sp_configure 'user instances enabled', '1'. To disable the functionality execute sp_configure'user instances enabled', '0'.COPY TO CLIPBOARD

Database mirroringIf you connect with ADO.NET or the SQL Native Client to a database that is being mirrored, your application cantake advantage of the drivers ability to automatically redirect connections when a database mirroring failoveroccurs. You must specify the initial principal server and database in the connection string and the failoverpartner server.Data Source = myServerAddress; Failover Partner =myMirrorServer; InitialCatalog = myDataBase; Integrated Security =True; There is ofcourse many other ways to write the connection string using database mirroring, this is just oneexample pointing out the failover functionality. You can combine this with the other connection strings optionsavailable.Read more about database mirroring in this Microsoft TechNet article "Database Mirroring in SQL Server 2005" COPY TO CLIPBOARD

Asynchronous processingA connection to SQL Server 2005 that allows for the issuing of async requests through ADO.NET objects.Server =myServerAddress; Database = myDataBase; Integrated Security =True; AsynchronousProcessing =True; COPY TO CLIPBOARD

S Q L N a t i v e C l i e n t O L E D B P r o v i d e r

Customize string example values Standard securityProvider =SQLNCLI; Server =myServerAddress; Database = myDataBase; Uid = myUsername; Pwd = myPassword; Are you using SQL Server 2005 Express? Don't miss the server name syntax Servername\SQLEXPRESS whereyou substitute Servername with the name of the computer where the SQL Server 2005 Express installationresides.COPY TO CLIPBOARD

Trusted connectionProvider =SQLNCLI; Server =myServerAddress; Database = myDataBase; Trusted_Connection =yes; Equivalent key-value pair: "Integrated Security=SSPI" equals "Trusted_Connection=yes"COPY TO CLIPBOARD

Connecting to an SQL Server instanceThe syntax of specifying the server instance in the value of the server key is the same for all connection stringsfor SQL Server.Provider =SQLNCLI; Server =myServerName\theInstanceName; Database = myDataBase; Trusted_Connection =yes; COPY TO CLIPBOARD

Prompt for username and passwordThis one is a bit tricky. First you need to set the connection object's Prompt property to adPromptAlways. Thenuse the connection string to connect to the database.oConn.Properties("Prompt") = adPromptAlways

oConn.Open "Provider = SQLNCLI; Server = myServerAddress; DataBase =myDataBase; COPY TO CLIPBOARD

Enabling MARS (multiple active result sets)Provider =SQLNCLI; Server =myServerAddress; Database = myDataBase; Trusted_Connection =yes; MarsConn = yes;


3/4

Equivalent key-value pair: "MultipleActiveResultSets=true" equals "MARS_Connection=yes"

Use ADO.NET 2.0 for MARS functionality. MARS is not supported in ADO.NET 1.0 nor ADO.NET 1.1.Using MARS with SQL Native Client, by Chris Lee COPY TO CLIPBOARD

Encrypt data sent over networkProvider =SQLNCLI; Server =myServerAddress; Database = myDataBase; Trusted_Connection =yes; Encrypt =yes; COPY TO CLIPBOARD

Attach a database file on connect to a local SQL Server Express instanceProvider =SQLNCLI; Server = .\SQLExpress; AttachDbFilename = c:\mydbfile.mdf; Database =dbname; Trusted_Connection =Yes; Why is the Database parameter needed? If the named database have already been attached, SQL Server doesnot reattach it. It uses the attached database as the default for the connection.COPY TO CLIPBOARD

Attach a database file, located in the data directory, on connect to a local SQL ServerExpress instanceProvider =SQLNCLI; Server = .\SQLExpress; AttachDbFilename = |DataDirectory|mydbfile.mdf; Database =dbname; Trusted_Connection =Yes; Why is the Database parameter needed? If the named database have already been attached, SQL Server doesnot reattach it. It uses the attached database as the default for the connection.Download the SQL Native Client here. The package contains booth the ODBC driver and the OLE DB provider COPY TO CLIPBOARD


S Q L N a t i v e C l i e n t O D B C D r i v e r

Customize string example values Standard securityDriver ={SQL Native Client}; Server = myServerAddress; Database = myDataBase; Uid = myUsername; Pwd =myPassword; Are you using SQL Server 2005 Express? Don't miss the server name syntax Servername\SQLEXPRESS whereyou substitute Servername with the name of the computer where the SQL Server 2005 Express installationresides.COPY TO CLIPBOARD

Trusted ConnectionDriver ={SQL Native Client}; Server = myServerAddress; Database = myDataBase; Trusted_Connection =yes; Equivalent key-value pair: "Integrated Security=SSPI" equals "Trusted_Connection=yes"COPY TO CLIPBOARD

Connecting to an SQL Server instanceThe syntax of specifying the server instance in the value of the server key is the same for all connection stringsfor SQL Server.Driver ={SQL Native Client}; Server = myServerName\theInstanceName; Database =myDataBase; Trusted_Connection =yes; COPY TO CLIPBOARD

Prompt for username and passwordThis one is a bit tricky. First you need to set the connection object's Prompt property to adPromptAlways. Thenuse the connection string to connect to the database.oConn.Properties("Prompt") = adPromptAlways

Driver ={SQL Native Client}; Server = myServerAddress; Database = myDataBase; COPY TO CLIPBOARD

Enabling MARS (multiple active result sets)Driver ={SQL Native Client}; Server = myServerAddress; Database = myDataBase; Trusted_Connection =yes; MARS_Connection =yes;


4/4

Equivalent key-value pair: "MultipleActiveResultSets=true" equals "MARS_Connection=yes"

Use ADO.NET 2.0 for MARS functionality. MARS is not supported in ADO.NET 1.0 nor ADO.NET 1.1.Using MARS with SQL Native Client, by Chris Lee COPY TO CLIPBOARD

Encrypt data sent over networkDriver ={SQL Native Client}; Server = myServerAddress; Database = myDataBase; Trusted_Connection =yes; Encrypt =yes; COPY TO CLIPBOARD

Attach a database file on connect to a local SQL Server Express instanceDriver ={SQL Native Client}; Server = .\SQLExpress; AttachDbFilename = c:\mydbfile.mdf; Database =dbname; Trusted_Connection =Yes; Why is the Database parameter needed? If the named database have already been attached, SQL Server doesnot reattach it. It uses the attached database as the default for the connection.COPY TO CLIPBOARD

Attach a database file, located in the data directory, on connect to a local SQL ServerExpress instanceDriver ={SQL Native Client}; Server = .\SQLExpress; AttachDbFilename = |DataDirectory|mydbfile.mdf; Database = dbname; Trusted_Connection = Yes; Why is the Database parameter needed? If the named database have already been attached, SQL Server doesnot reattach it. It uses the attached database as the default for the connection.Download the SQL Native Client here. The package contains booth the ODBC driver and the OLE DB provider COPY TO CLIPBOARD


S Q L X M L O L E D B

Customize string example values Using SQL Server Native Client providerProvider =SQLXMLOLEDB.4.0; Data Provider =SQLNCLI; Data Source = myServerAddress; InitialCatalog = myDataBase; User Id = myUsername; Password =myPassword; COPY TO CLIPBOARD

S Q L S e r v e r 2 0 0 5 s p e c i a l s

Customize string example values Context ConnectionConnecting to "self" from within your CLR stored prodedure/function. The context connection lets you executeTransact-SQL statements in the same context (connection) that your code was invoked in the first place.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 When to use SQL Native Client? List of all SqlConnection connection string properties

connection_strings_for_sql_server_2005

Documents