Developers community number 1 connection string reference

Connection strings for SQL Server 2000, 7.0

Community Forums
Find solutions and post questions regarding connection string related issues.

.NET Framework Data Provider for SQL Server

Type .NET Framework Class Library
 Usage System.Data.SqlClient.SqlConnection
 Manufacturer Microsoft
Set example values »

More about this class library »

Standard Security

Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
 
Copy
 

Standard Security alternative syntax

This connection string produces the same result as the previous one. The reason to include it is to point out that some connection string keywords have many equivalents.

Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;Trusted_Connection=False;
 
Copy
 

Trusted Connection

Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;
 
Copy
 

Trusted Connection alternative syntax

This connection string produce the same result as the previous one. The reason to include it is to point out that some connection string keywords have many equivalents.

Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;

Use serverName\instanceName as Data Source to use a specific SQL Server instance. Please note that the multiple SQL Server instances feature is available only from SQL Server version 2000 and not in any previous versions.

 
Copy
 

Connecting to an SQL Server instance

The syntax of specifying the server instance in the value of the server key is the same for all connection strings for SQL Server.

Server=myServerName\theInstanceName;Database=myDataBase;Trusted_Connection=True;
 
Copy
 

Trusted Connection from a CE device

Often a Windows CE device is not authenticated and logged in to a domain. To use SSPI or trusted connection / authentication from a CE device, use this connection string.

Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;User ID=myDomain\myUsername;Password=myPassword;

Note that this will only work on a CE device.

Read more about connecting to SQL Server from CE devices here

 
Copy
 

Connect via an IP address

Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;

DBMSSOCN=TCP/IP. This is how to use TCP/IP instead of Named Pipes. At the end of the Data Source is the port to use. 1433 is the default port for SQL Server.

How to define which network protocol to use

 
Copy
 

Specifying packet size

Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;Trusted_Connection=False;Packet Size=4096;

By default, the Microsoft .NET Framework Data Provider for SQL Server sets the network packet size to 8192 bytes. This might however not be optimal, try to set this value to 4096 instead.

The default value of 8192 might cause errors as well ("Failed to reserve contiguous memory"), check this out

 
Copy
 

Microsoft OLE DB Provider for SQL Server

Type OLE DB Provider
 Usage Provider=sqloledb
 Manufacturer Microsoft
Set example values »

More about this provider »

Standard Security

Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
 
Copy
 

Trusted connection

Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

Use serverName\instanceName as Data Source to use a specific SQL Server instance. Please note that the multiple SQL Server instances feature is available only from SQL Server version 2000 and not in any previous versions.

 
Copy
 

Connecting to an SQL Server instance

The syntax of specifying the server instance in the value of the server key is the same for all connection strings for SQL Server.

Provider=sqloledb;Data Source=myServerName\theInstanceName;Initial Catalog=myDataBase;Integrated Security=SSPI;
 
Copy
 

Prompt for username and password

This one is a bit tricky. First set the connection object's Provider property to "sqloledb". Thereafter set the connection object's Prompt property to adPromptAlways. Then use the connection string to connect to the database.

oConn.Provider = "sqloledb"
oConn.Properties("Prompt") = adPromptAlways

Data Source=myServerAddress;Initial Catalog=myDataBase;
 
Copy
 

Connect via an IP address

Provider=sqloledb;Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;

DBMSSOCN=TCP/IP. This is how to use TCP/IP instead of Named Pipes. At the end of the Data Source is the port to use. 1433 is the default port for SQL Server.

How to define which network protocol to use

 
Copy
 

Disable connection pooling

This one is usefull when receving errors "sp_setapprole was not invoked correctly." (7.0) or "General network error. Check your network documentation" (2000) when connecting using an application role enabled connection. Application pooling (or OLE DB resource pooling) is on by default. Disabling it can help on this error.

Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;OLE DB Services=-2;
 
Copy
 

.NET Framework Data Provider for OLE DB

Type .NET Framework Wrapper Class Library
 Usage System.Data.OleDb.OleDbConnection
 Manufacturer Microsoft
Set example values »

More about this wrapper class library »

Bridging to OLE DB Provider for SQL Server

This is just one connection string sample for the wrapping OleDbConnection class that calls the underlying OLEDB provider. See respective OLE DB provider for more connection strings to use with this class.

Provider=SQLOLEDB;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername; Password=myPassword;
 
Copy
 

Microsoft SQL Server ODBC Driver

Type ODBC Driver
 Usage Driver={SQL Server}
 Manufacturer Microsoft
Set example values »

More about this driver »

Standard Security

Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
 
Copy
 

Trusted connection

Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Trusted_Connection=Yes;
 
Copy
 

Prompt for username and password

This one is a bit tricky. First you need to set the connection object's Prompt property to adPromptAlways. Then use the connection string to connect to the database.

oConn.Properties("Prompt") = adPromptAlways

Driver={SQL Server};Server=myServerAddress;Database=myDataBase;
 
Copy
 

SQL Server Native Client 10.0 OLE DB Provider

Type OLE DB Provider
 Usage Provider=SQLNCLI10
 Manufacturer Microsoft
Set example values »

More about this provider »

Standard security

Note that the SQL Server Native Client OLE DB Provider does not support SQL Server 7.0.

Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase;Uid=myUsername; Pwd=myPassword;
 
Copy
 

Trusted connection

Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase; Trusted_Connection=yes;

Equivalent key-value pair: "Integrated Security=SSPI" equals "Trusted_Connection=yes"

 
Copy
 

Connecting to an SQL Server instance

The syntax of specifying the server instance in the value of the server key is the same for all connection strings for SQL Server.

Provider=SQLNCLI10;Server=myServerName\theInstanceName;Database=myDataBase; Trusted_Connection=yes;
 
Copy
 

Prompt for username and password

This one is a bit tricky. First you need to set the connection object's Prompt property to adPromptAlways. Then use the connection string to connect to the database.

oConn.Properties("Prompt") = adPromptAlways

oConn.Open "Provider=SQLNCLI10;Server=myServerAddress;DataBase=myDataBase;
 
Copy
 

Encrypt data sent over network

Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase; Trusted_Connection=yes;Encrypt=yes;
 
Copy
 

SQL Native Client 9.0 OLE DB provider

Type OLE DB Provider
 Usage Provider=SQLNCLI
 Manufacturer Microsoft
Set example values »

More about this provider »

Standard security

Note that the SQL Server Native Client OLE DB Provider does not support SQL Server 7.0.

Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Uid=myUsername; Pwd=myPassword;
 
Copy
 

Trusted connection

Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase; Trusted_Connection=yes;

Equivalent key-value pair: "Integrated Security=SSPI" equals "Trusted_Connection=yes"

 
Copy
 

Connecting to an SQL Server instance

The syntax of specifying the server instance in the value of the server key is the same for all connection strings for SQL Server.

Provider=SQLNCLI;Server=myServerName\theInstanceName;Database=myDataBase; Trusted_Connection=yes;
 
Copy
 

Prompt for username and password

This one is a bit tricky. First you need to set the connection object's Prompt property to adPromptAlways. Then use the connection string to connect to the database.

oConn.Properties("Prompt") = adPromptAlways

oConn.Open "Provider=SQLNCLI;Server=myServerAddress;DataBase=myDataBase;
 
Copy
 

Encrypt data sent over network

Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase; Trusted_Connection=yes;Encrypt=yes;
 
Copy
 

SQL Server Native Client 10.0 ODBC Driver

Type ODBC Driver
 Usage Driver={SQL Server Native Client 10.0}
 Manufacturer Microsoft
Set example values »

More about this driver »

Standard security

Driver={SQL Server Native Client 10.0};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
 
Copy
 

Trusted Connection

Driver={SQL Server Native Client 10.0};Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;

Equivalent key-value pair: "Integrated Security=SSPI" equals "Trusted_Connection=yes"

 
Copy
 

Connecting to an SQL Server instance

The syntax of specifying the server instance in the value of the server key is the same for all connection strings for SQL Server.

Driver={SQL Server Native Client 10.0};Server=myServerName\theInstanceName; Database=myDataBase;Trusted_Connection=yes;
 
Copy
 

Prompt for username and password

This one is a bit tricky. First you need to set the connection object's Prompt property to adPromptAlways. Then use the connection string to connect to the database.

oConn.Properties("Prompt") = adPromptAlways

Driver={SQL Server Native Client 10.0};Server=myServerAddress;Database=myDataBase;
 
Copy
 

Encrypt data sent over network

Driver={SQL Server Native Client 10.0};Server=myServerAddress;Database=myDataBase; Trusted_Connection=yes;Encrypt=yes;
 
Copy
 

SQL Native Client 9.0 ODBC Driver

Type ODBC Driver
 Usage Driver={SQL Native Client}
 Manufacturer Microsoft
Set example values »

More about this driver »

Standard security

Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase; Uid=myUsername;Pwd=myPassword;
 
Copy
 

Trusted Connection

Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase; Trusted_Connection=yes;

Equivalent key-value pair: "Integrated Security=SSPI" equals "Trusted_Connection=yes"

 
Copy
 

Connecting to an SQL Server instance

The syntax of specifying the server instance in the value of the server key is the same for all connection strings for SQL Server.

Driver={SQL Native Client};Server=myServerName\theInstanceName;Database=myDataBase; Trusted_Connection=yes;
 
Copy
 

Prompt for username and password

This one is a bit tricky. First you need to set the connection object's Prompt property to adPromptAlways. Then use the connection string to connect to the database.

oConn.Properties("Prompt") = adPromptAlways

Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase;
 
Copy
 

Encrypt data sent over network

Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase; Trusted_Connection=yes;Encrypt=yes;
 
Copy
 

.NET Framework Data Provider for ODBC

Type .NET Framework Wrapper Class Library
 Usage System.Data.Odbc.OdbcConnection
 Manufacturer Microsoft
Set example values »

More about this wrapper class library »

Bridging to SQL Native Client 10.0 ODBC Driver

This is just one connection string sample for the wrapping OdbcConnection class that calls the underlying ODBC Driver. See respective ODBC driver for more connection strings to use with this class.

Driver={SQL Server Native Client 10.0};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
 
Copy
 

SQLXML 3.0 OLEDB Provider

Type OLE DB Provider
 Usage Provider=SQLXMLOLEDB.3.0;Data Provider=sqloledb
 Manufacturer Microsoft
Set example values »

More about this provider »

Using SQL Server Ole Db

The SQLXML version 3.0 restricts the data provider to SQLOLEDB only.

Provider=SQLXMLOLEDB.3.0;Data Provider=SQLOLEDB;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
 
Copy
 

MSDataShape

Type Data Shaping COM component
 Usage Provider=MSDataShape;Data Provider=providername
 Manufacturer Microsoft
Set example values »

More about this wrapper COM component »

MSDataShape

Provider=MSDataShape;Data Provider=SQLOLEDB;Data Source=myServerAddress;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;

Want to learn data shaping? Check out 4GuyfFromRolla's great article about Data Shaping

List of all SqlConnection connection string properties

 
Copy
 
Copyright © 2012 ConnectionStrings.com   |   All Rights Reserved   |   Powered by CSAS   |   Send feedback, articles, requests and more connection strings here.