SQL Server Native Client: Connection strings and OLE DB

[SQL-Server-Team]

First published on MSDN on May 06, 2009

(Anton Klimov, a developer on the SQL Server Native Client team, wrote the following article.)

Connection strings for an OLE DB provider is a concept that causes a lot of confusion. One problem is that OLE DB itself does not define connection string as a provider concept.

The documentation at Connection String Syntax ( http://msdn.microsoft.com/en-us/library/ms722656(VS.85).aspx ) says:

"A connection string is a string version of the initialization properties needed to connect to a data store and enables you to easily store connection information within your application or to pass it between applications. Without a connection string, you would be required to store or pass a complex array of structures to access data. When accessing OLE DB directly, using IDataInitialize ( http://msdn.microsoft.com/en-us/library/ms714296%28VS.85%29.aspx ), the connection string is passed as a parameter to create an OLE DB data source object."

“when accessing OLE DB directly” actually means that the application doesn’t directly instantiate a corresponding provider object, but rather goes through the “service component”, (otherwise known as “core services”, layer (oledb32.dll) by doing something like:

hr = CoCreateInstance(
CLSID_MSDASC,   // CLSID of “service component”
NULL,           // pUnkOuter
CLSCTX_INPROC_SERVER,
IID_IDataInitialize,
(void**)&m_pIDataInitialize)
;

This way, you get additional services from oledb32 like session pooling, automatic transaction enlistment, cursor engine, and support for additional interfaces.

This also applies to ADO; so for an ADO programmer, a connection string is a similar concept but the way the provider looks at it is frequently misunderstood.

From the provider point of view there is no such thing as a connection string, instead the provider understands the initialization properties, which are hinted to in the above passage as “complex array of structures to access data”. See also OLE DB Initialization Properties: Quick Reference ( http://msdn.microsoft.com/en-us/library/ms723996(VS.85).aspx ).

What makes the situation especially confusing is that one of the properties is similar to a connection string. These are the so called “Extended Properties” (DBPROP_INIT_PROVIDERSTRING).

http://msdn.microsoft.com/en-us/library/ms723996(VS.85).aspx for it says:

"A string containing provider-specific, extended connection information. Use of this property implies that the consumer knows how this string will be interpreted and used by the provider. Consumers should use this property only for provider-specific connection information that cannot be explicitly described through the property mechanism."

However, in practice, lack of understanding leads consumers to use a combination of keywords both corresponding to the generic OLE DB properties and to provider specific keywords making sense only in the context of the provider string.

For example the site listing lots of examples of connection strings has the following for SQL Server Native Client 10.0 OLE DB Provider ( http://www.connectionstrings.com/sql-server-2008 ):

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

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

Here “Provider” keyword is used by oledb32 to find a CLSID of the provider to instantiate. “Server”, “Database”, “Trusted_Connection” and “MarsConn” are not the names of the OLE DB initialization properties (which would be “Data Source”, “Integrated Security”, and “Mars Connection”). Moreover, MarsConn=yes will have no effect, see explanation below.

MultipleActiveResultSets is a keyword for ADO.NET , and MARS_Connection is for ODBC.

oledb32 process a connection string as follows: it tries to find the keywords corresponding to the generic OLE DB initialization keywords (corresponding to DBPROPSET_DBINIT), and it also queries a provider by using IDBProperties::GetPropertyInfo ( http://msdn.microsoft.com/en-us/library/ms718175(VS.85).aspx ) to see which initialization properties provider supports (for SQL Server Native Client it is DBPROPSET_SQLSERVERDBINIT). For matched keywords, the corresponding properties will be set, everything else goes into “Extended Properties”.

Consider the following example in VBScript:

set connection = CreateObject("ADODB.Connection")

connection.ConnectionString = "provider=sqlncli10;data source=akl6; integrated security=sspi;initial catalog=tempdb"

connection.Open

WScript.Echo connection.ConnectionString

Here is the result of the execution:

Provider=SQLNCLI10.1;Integrated Security=sspi;Initial Catalog=tempdb;Data Source=akl6;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=AKL5;Use Encryption for Data=False;Tag with column collation when possible=False;MARS Connection=False;DataTypeCompatibility=0;Trust Server Certificate=False

If we try the following connection string

"provider=sqlncli10;server=akl6;database=tempdb;Trusted_Connection=yes;MarsConn=yes"

we will get:

Provider=SQLNCLI10.1;Extended Properties="server=akl6;database=tempdb;Trusted_Connection=yes;MarsConn=yes";Use Procedure for Prepare=1;Auto

Translate=True;Packet Size=4096;Workstation ID=AKL5;Use Encryption for Data=False;Tag with column collation when possible=False;MARS Connect

ion=False;DataTypeCompatibility=0;Trust Server Certificate=False

As you can see all the keywords that we specified are bundled into “Extended Properties” since they are not recognized as property names. What is worse is that now we apparently have two keywords corresponding to MARS Connectivity and one is “yes”, whether the other is “False”.

Which one wins? It turns out that the generic value wins. So the setting “MarsConn=yes” as a part of OLE DB connection string has no effect. It would work only if the provider was initialized directly and provider specific property was not used, but “Extended Properties” was used.

If you have the following:

“Server=server1; Data Source=server2;”

The data Source will win.

Generic Initialization properties (DBPROPSET_DBINIT)

The following list shows property IDs and their associated description/keywords:

Property ID	Description / keyword
DBPROP_AUTH_INTEGRATED	Integrated Security
DBPROP_AUTH_PASSWORD	Password
DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO	Persist Security Info
DBPROP_AUTH_USERID	User ID
DBPROP_INIT_ASYNCH	Asynchronous Processing
DBPROP_INIT_CATALOG	Initial Catalog
DBPROP_INIT_DATASOURCE	Data Source
DBPROP_INIT_HWND	Window Handle
DBPROP_INIT_LCID	Locale Identifier
DBPROP_INIT_PROPMT	Prompt
DBPROP_INIT_PROVIDERSTRING	Extended Properties
DBPROP_INIT_TIMEOUT	Connect Timeout
DBPROP_INIT_GENERALTIMEOUT	General Timeout

If default value is specified the property cannot be overridden with an “extended property” when used in an OLE DB connection string.

Provider Specific Properties (DBPROPSET_SQLSERVERDBINIT)

The following list shows the associated property IDs, default values (if any) and description/keywords for provider specific properties.

SSPROP_INIT_CURRENTLANGUAGE

(no default value)

Current Language

SSPROP_INIT_NETWORKADDRESS

(no default value)

Network Address

SSPROP_INIT_NETWORKLIBRARY

(no default value)

Network Library

SSPROP_INIT_USEPROCFORPREP

1

Use Procedure for Prepare

SSPROP_INIT_AUTOTRANSLATE

VARIANT_TRUE

Auto Translate

SSPROP_INIT_PACKETSIZE

4096

Packet Size

SSPROP_INIT_APPNAME

(no default value)

Application Name

SSPROP_INIT_WSID

(no default value)

Workstation ID

SSPROP_INIT_FILENAME

(no default value)

Initial File Name

SSPROP_INIT_ENCRYPT

VARIANT_FALSE

Use Encryption for Data

SSPROP_AUTH_REPL_SERVER_NAME

(no default value)

Replication server name connect option

SSPROP_INIT_TAGCOLUMNCOLLATION

VARIANT_FALSE

Tag with column collation when possible

SSPROP_INIT_MARSCONNECTION

VARIANT_FALSE

MARS Connection

SSPROP_INIT_FAILOVERPARTNER

(no default value)

Failover Partner

SSPROP_AUTH_OLD_PASSWORD

(no default value)

Old Password

SSPROP_INIT_DATATYPECOMPATIBILITY

0

DataTypeCompatibility

SSPROP_INIT_TRUST_SERVER_CERTIFICATE

VARIANT_FALSE

Trust Server Certificate

SSPROP_INIT_SERVERSPN

(no default value)

Server SPN

SSPROP_INIT_FAILOVERPARTNERSPN

(no default value)

Failover Partner SPN

Note that the corresponding properties are not going to be set, and as mentioned above, some of the settings might be ignored.

The following list shows keywords recognized in the provider string and its (approximate) corresponding property.

“Server"

DBPROP_INIT_DATASOURCE

“UID"

DBPROP_AUTH_USERID

“PWD"

DBPROP_AUTH_PASSWORD

“APP"

SSPROP_INIT_APPNAME

“WSID"

DBPROP_INIT_CATALOG

“Database"

DBPROP_INIT_CATALOG

“Language"

SSPROP_INIT_CURRENTLANGUAGE

“Network" or “Net” or “Network Library”

SSPROP_INIT_NETWORKLIBRARY

“Address" or “Addr”

SSPROP_INIT_NETWORKADDRESS

“Trusted_Connection"

DBPROP_AUTH_INTEGRATED

“UseProcForPrepare"

SSPROP_INIT_USEPROCFORPREP

“LCID"

Not used

“Prompt"

DBPROP_INIT_PROPMT

“PersistSensitive"

DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO

“AutoTranslate" or “Auto Translate”

SSPROP_INIT_AUTOTRANSLATE

“Timeout"

DBPROP_INIT_TIMEOUT

“PacketSize"

SSPROP_INIT_PACKETSIZE

“HWND"

DBPROP_INIT_HWND

“AttachDBFileName"

SSPROP_INIT_FILENAME

“Encrypt"

SSPROP_INIT_ENCRYPT

“MarsConn"

SSPROP_INIT_MARSCONNECTION

“FailoverPartner"

SSPROP_INIT_FAILOVERPARTNER

“DataTypeCompatibility"

SSPROP_INIT_DATATYPECOMPATIBILITY

“TrustServerCertificate"

SSPROP_INIT_TRUST_SERVER_CERTIFICATE

“ServerSPN"

SSPROP_INIT_SERVERSPN

“FailoverPartnerSPN"

SSPROP_INIT_FAILOVERPARTNERSPN

Linked server configuration note:

In the "Linked Server Properties -- New Linked Server" dialog box“ (SQL Server Management Studio go to Server Objects -> Linked Servers -> New Linked Server), the Provider string” really corresponds to “Extended Properties”.

Some users try putting their generic OLE DB Connection string, which leads to the server discarding all or the part of the settings and it could end up connecting to the local machine instead of the machine that the user thinks is specified. For instance if “Failover Partner” is specified in that dialog and it contains a space, the setting will have no effect since in the provider string no space is expected.