SQL Server 2008 Connection strings - Connectionstring Examples

Connection strings Examples for SQL Server 2008

.NET Data Provider for SQL Server 2008

This is a .NET class library from Microsoft. The namespace for this is System.Data.SqlClient.SqlConnection

Standard Security:

Data Source=yourServerAddress;Initial Catalog=yourDataBase;User Id=yourUsername;Password=yourPassword;

OR

Server=yourServerAddress;Database=yourDataBase;User ID=yourUsername;Password=yourPassword;Trusted_Connection=False;

If you have different instance on the server make sure you use this syntax serverName\instanceName .

Trusted Connection:

Data Source=yourServerAddress;Initial Catalog=yourDataBase;Integrated Security=SSPI;

OR

Server=yourServerAddress;Database=yourDataBase;Trusted_Connection=True;

Connecting to an SQL Server instance:

Server=yourServerName\theInstanceName;Database=yourDataBase;Trusted_Connection=True;

Trusted Connection from a CE device:

Data Source=yourServerAddress;Initial Catalog=yourDataBase;Integrated Security=SSPI;User ID=yourDomain\yourUsername;Password=yourPassword;

Only works on CE device. You can get more information on this from Microsoft.

Connect via an IP address:

Data Source=192.168.1.125,1433;Network Library=DBMSSOCN;Initial Catalog=yourDataBase;User ID=yourUsername;Password=yourPassword;

    The default port for SQL Server is 1433. DBMSSOCN=TCP/IP.

    Available Network Library:

Name	Network Library
dbmsvinn	Banyan Vines
dbmslpcn	Shared Memory
dbnmpntw	Named Pipes
dbmssocn	Winsock TCP/IP
dbmsadsn	Apple Talk
dbmsgnet	VIA
dbmsspxn	SPX/IPX
dbmsrpcn	Multi-Protocol (Windows RPC)

Attach a database file on connect to a local SQL Server Express instance:

Server=.\SQLExpress;AttachDbFilename=c:\data\mssql\yourdbfile.mdf;Database=dbname; Trusted_Connection=Yes;

Enabling MARS (multiple active result sets):

Server=yourServerAddress;Database=yourDataBase;Trusted_Connection=True; MultipleActiveResultSets=true;

Not Availble in ADO.NET 1.0 and 1.1, to use this connection string use ADO.NET.

Using an User Instance on a local SQL Server Express instance:

Data Source=.\SQLExpress;Integrated Security=true; AttachDbFilename=|DataDirectory|\yourdb.mdf;User Instance=true;

    Enable this feature on SQL server before trying to use this functionality. Two commands can be used
    to switch the functionality on and off. This string creates a new SQL Server instance on the fly during connect.
        * sp_configure 'user instances enabled', '1'     (Enabled)
        * sp_configure 'user instances enabled', '0'.    (Disabled)

Attach a database file, located in the data directory, on connect to a local SQL Server Express instance:

Server=.\SQLExpress;AttachDbFilename=|DataDirectory|yourdbfile.mdf; Database=dbname;Trusted_Connection=Yes;

Attach a database file on connect to a local SQL Server Express instance:

Server=.\SQLExpress;AttachDbFilename=c:\data\mssql\yourdbfile.mdf;Database=dbname; Trusted_Connection=Yes;

Database mirroring:

Data Source=yourServerAddress;Failover Partner=yourMirrorServer;Initial Catalog=yourDataBase;Integrated Security=True

Automatically redirect connection to the mirrored database if the current one failed.

Asynchronous processing:

Server=yourServerAddress;Database=yourDataBase;Integrated Security=True;Asynchronous Processing=True;

A connection to SQL Server that allows for the issuing of async requests through ADO.NET objects.

.NET Data Provider for OLE DB

This is a .NET wrapper class library from Microsoft. Use the namespace System.Data.OleDb.OleDbConnection

Standard:

Provider=SQLNCLI10;Server=yourServerAddress;Database=yourDataBase;Uid=yourUsername; Pwd=yourPassword;

SQL Server Native Client 10.0 OLE DB Provider

This is a OLE DB Provider from Microsoft. Its usage is Provider=SQLNCLI10.

Standard Security:

Provider=SQLNCLI10;Server=yourServerAddress;Database=yourDataBase;Uid=yourUsername; Pwd=yourPassword;

Use this syntax Servername\SQLEXPRESS if you use the express version.

Trusted connection:

Provider=SQLNCLI10;Server=yourServerAddress;Database=yourDataBase; Trusted_Connection=yes;

Connecting to an SQL Server instance :

Provider=SQLNCLI10;Server=yourServerName\theInstanceName;Database=yourDataBase; Trusted_Connection=yes;

Prompt for username and password:

oConn.Properties("Prompt") = adPromptAlways

oConn.Open "Provider=SQLNCLI10;Server=yourServerAddress;DataBase=yourDataBase;

Must use the oConn.Properties("Prompt") for this to work.

Enabling MARS (multiple active result sets):

Provider=SQLNCLI10;Server=yourServerAddress;Database=yourDataBase; Trusted_Connection=yes;MarsConn=yes;

Not Availble in ADO.NET 1.0 and 1.1, to use this connection string use ADO.NET.

Encrypt data sent over network:

Provider=SQLNCLI10;Server=yourServerAddress;Database=yourDataBase; Trusted_Connection=yes;Encrypt=yes;

Attach a database file on connect to a local SQL Server Express instance:

Provider=SQLNCLI10;Server=.\SQLExpress;AttachDbFilename=c:\data\MSSQL\yourdbfile.mdf; Database=dbname; Trusted_Connection=Yes;

Attach a database file, located in the data directory, on connect to a local SQL Server Express instance:

Provider=SQLNCLI10;Server=.\SQLExpress;AttachDbFilename=|DataDirectory|yourdbfile.mdf; Database=dbname;Trusted_Connection=Yes;

Database mirroring:

Data Source=yourServerAddress;Failover Partner=yourMirrorServer;Initial Catalog=yourDataBase;Integrated Security=True;

Automatically redirect connection to the mirrored database if the current one failed.

SQLXML 4.0 OLEDB Provider

This is a OLE DB Provider from Micrsoft.

Standard :

Provider=SQLXMLOLEDB.4.0;Data Provider=SQLNCLI10;Data Source=yourServerAddress;Initial Catalog=yourDataBase;User Id=yourUsername;Password=yourPassword;

Context Connection

This is a .NET class library from Microsoft.

Standard:

using(SqlConnection connection = new SqlConnection("context connection=true"))
{
connection.Open();
}

SQL Server Native Client 10.0 ODBC Driver

This is a ODBC driver from Microsoft. The usage is Driver={SQL Server Native Client 10.0}

Standard security:

Driver={SQL Server Native Client 10.0};Server=yourServerAddress;Database=yourDataBase;Uid=yourUsername;Pwd=yourPassword;

Trusted Connection:

Driver={SQL Server Native Client 10.0};Server=yourServerAddress;Database=yourDataBase;Trusted_Connection=yes;

Connecting to an SQL Server instance:

Driver={SQL Server Native Client 10.0};Server=yourServerName\theInstanceName; Database=yourDataBase;Trusted_Connection=yes;

Prompt for username and password:

oConn.Properties("Prompt") = adPromptAlways

Driver={SQL Server Native Client 10.0};Server=yourServerAddress;Database=yourDataBase;

Must use the oConn.Properties("Prompt") for this to work.

Enabling MARS (multiple active result sets):

Driver={SQL Server Native Client 10.0};Server=yourServerAddress;Database=yourDataBase;Trusted_Connection=yes; MARS_Connection=yes;

Encrypt data sent over network:

Driver={SQL Server Native Client 10.0};Server=yourServerAddress;Database=yourDataBase; Trusted_Connection=yes;Encrypt=yes;

Attach a database file on connect to a local SQL Server Express instance:

Driver={SQL Server Native Client 10.0};Server=.\SQLExpress; AttachDbFilename=c:\data\mssql\mydbfile.mdf; Database=dbname;Trusted_Connection=Yes;

Attach a database file, located in the data directory, on connect to a local SQL Server Express instance:

Driver={SQL Server Native Client 10.0};Server=.\SQLExpress;AttachDbFilename=|DataDirectory|yourdbfile.mdf; Database=dbname;Trusted_Connection=Yes;

Database mirroring:

Data Source=yourServerAddress;Failover Partner=yourMirrorServer;Initial Catalog=yourDataBase;Integrated Security=True;