Connection strings for SQL Server 2000, 7.0

.NET Framework Data Provider for SQL Server

Type .NET Framework Class Library
 Usage System.Data.SqlClient.SqlConnection
 Manufacturer Microsoft
More info and class library downloads

Set example values

Standard Security

    
Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;
 
 

Trusted Connection

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

Connection to a SQL Server instance

The server/instance name syntax used in the server option is the same for all SQL Server connection strings.

    
Server=myServerName\myInstanceName;Database=myDataBase;User Id=myUsername;
Password=myPassword;
 
 

Trusted Connection from a CE device

A Windows CE device is most often not authenticated and logged in to a domain but it is possible to use SSPI or trusted connection and authentication from a CE device using 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.

 
 

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 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. Read more here.

 
 

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 Failed to reserve contiguous memory errors as well, read more here.

 
 

Microsoft OLE DB Provider for SQL Server

Type OLE DB Provider
 Usage Provider=sqloledb
 Manufacturer Microsoft
More info and provider downloads

Set example values

Standard Security

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

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.

 
 

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;
 
 

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;
 
 

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. Read more in the article How to define which network protocol to use.

 
 

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;
 
 

.NET Framework Data Provider for OLE DB

Type .NET Framework Wrapper Class Library
 Usage System.Data.OleDb.OleDbConnection
 Manufacturer Microsoft
More info and wrapper class library downloads

Set example values

Use an OLE DB provider from .NET

    
Provider=any oledb provider's name;OledbKey1=someValue;OledbKey2=someValue;

See the respective OLEDB provider's connection strings options. The .net OleDbConnection will just pass on the connection string to the specified OLEDB provider. Read more here.

 
 

Microsoft SQL Server ODBC Driver

Type ODBC Driver
 Usage Driver={SQL Server}
 Manufacturer Microsoft
More info and driver downloads

Set example values

Standard Security

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

Trusted connection

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

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;
 
 

SQL Server Native Client 10.0 OLE DB Provider

Type OLE DB Provider
 Usage Provider=SQLNCLI10
 Manufacturer Microsoft
More info and provider downloads

Set example values

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;
 
 

Trusted connection

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

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

 
 

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;
 
 

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;
 
 

Encrypt data sent over network

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

SQL Native Client 9.0 OLE DB provider

Type OLE DB Provider
 Usage Provider=SQLNCLI
 Manufacturer Microsoft
More info and provider downloads

Set example values

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;
 
 

Trusted connection

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

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

 
 

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;
 
 

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;
 
 

Encrypt data sent over network

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

SQL Server Native Client 10.0 ODBC Driver

Type ODBC Driver
 Usage Driver={SQL Server Native Client 10.0}
 Manufacturer Microsoft
More info and driver downloads

Set example values

Standard security

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

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"

 
 

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;
 
 

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;
 
 

Encrypt data sent over network

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

SQL Native Client 9.0 ODBC Driver

Type ODBC Driver
 Usage Driver={SQL Native Client}
 Manufacturer Microsoft
More info and driver downloads

Set example values

Standard security

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

Trusted Connection

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

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

 
 

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;
 
 

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;
 
 

Encrypt data sent over network

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

.NET Framework Data Provider for ODBC

Type .NET Framework Wrapper Class Library
 Usage System.Data.Odbc.OdbcConnection
 Manufacturer Microsoft
More info and wrapper class library downloads

Set example values

Use an ODBC driver from .NET

    
Driver={any odbc driver's name};OdbcKey1=someValue;OdbcKey2=someValue;

See the respective ODBC driver's connection strings options. The .net OdbcConnection will just pass on the connection string to the specified ODBC driver. Read more here.

 
 

SQLXML 3.0 OLEDB Provider

Type OLE DB Provider
 Usage Provider=SQLXMLOLEDB.3.0;Data Provider=sqloledb
 Manufacturer Microsoft
More info and provider downloads

Set example values

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;
 
 

MSDataShape

Type Data Shaping COM component
 Usage Provider=MSDataShape;Data Provider=providername
 Manufacturer Microsoft
More info and wrapper COM component downloads

Set example values

MSDataShape

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

See also the List of all SqlConnection connection string properties

 
 

Popular