[转]ODBC连接字符串大全(SQL SERVER 2000)

原帖地址： http://www.connectionstrings.com/sql-server 

 

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

 

 

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 causeFailed 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 thearticle 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 morehere.

 

 

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

 

 

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

 

 

Sign-in or Join

Share

Popular

• Connection strings explained
• Rules for connection strings
• Store connection string in web.config
• Connection Pooling

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