(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
Network Address
SSPROP_INIT_NETWORKLIBRARY
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
Application Name
SSPROP_INIT_WSID
Workstation ID
SSPROP_INIT_FILENAME
Initial File Name
SSPROP_INIT_ENCRYPT
VARIANT_FALSE
Use Encryption for Data
SSPROP_AUTH_REPL_SERVER_NAME
Replication server name connect option
SSPROP_INIT_TAGCOLUMNCOLLATION
Tag with column collation when possible
SSPROP_INIT_MARSCONNECTION
MARS Connection
SSPROP_INIT_FAILOVERPARTNER
Failover Partner
SSPROP_AUTH_OLD_PASSWORD
Old Password
SSPROP_INIT_DATATYPECOMPATIBILITY
0
DataTypeCompatibility
SSPROP_INIT_TRUST_SERVER_CERTIFICATE
Trust Server Certificate
SSPROP_INIT_SERVERSPN
Server SPN
SSPROP_INIT_FAILOVERPARTNERSPN
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.
“UID"
“PWD"
“APP"
“WSID"
“Database"
“Language"
“Network" or “Net” or “Network Library”
“Address" or “Addr”
“Trusted_Connection"
“UseProcForPrepare"
“LCID"
Not used
“Prompt"
“PersistSensitive"
“AutoTranslate" or “Auto Translate”
“Timeout"
“PacketSize"
“HWND"
“AttachDBFileName"
“Encrypt"
“MarsConn"
“FailoverPartner"
“DataTypeCompatibility"
“TrustServerCertificate"
“ServerSPN"
“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.