(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 |
