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