SQL Server Native Client: Connection strings and OLE DB
(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 (https://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 (https://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 (https://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).
https://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 (https://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 (https://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.
Comments
Anonymous
May 06, 2009
PingBack from http://asp-net-hosting.simplynetdev.com/sql-server-native-client-connection-strings-and-ole-db/Anonymous
September 09, 2016
HelloI am trying yo open connection using SQLNCLI on Windows server 2012 but it fails at Cocreateinstance.Same works fine on Windows server 2008 R2 but fail on Windows server 2012.I am building MSDN sample code using IBCPSession from link https://technet.microsoft.com/en-us/library/ms130930(v=sql.110).aspxNot able to create instance using SQLNCLI on windows server 2012 and failed at CocreateInstacne call.Please help me.