Service Principal Names (SPNs) in Client Connections (OLE DB)

 

System_CAPS_ICON_warning.jpg Warning

SQL Server Native Client (SNAC) is not supported beyond SQL Server 2012. Avoid using SNAC in new development work, and plan to modify applications that currently use it. The Microsoft ODBC Driver for SQL Server provides native connectivity from Windows to Microsoft SQL Server and Microsoft Azure SQL Database.

This topic describes OLE DB properties and member functions that support service principal names (SPNs) in client applications. For more information about SPNs in client applications, see Service Principal Name (SPN) Support in Client Connections. For a sample, see Integrated Kerberos Authentication (OLE DB).

The following provider initialization string keywords support SPNs in OLE DB applications. In the following table, the values in the keyword column are used for the provider string of IDBInitialize::Initialize. The values in the description column are used in initialization strings when connecting using ADO or IDataInitialize::GetDataSource.

KeywordDescriptionValue
ServerSPNServer SPNThe SPN for the server. The default value is an empty string, which causes SQL Server Native Client to use the default, provider-generated SPN.
FailoverPartnerSPNFailover Partner SPNThe SPN for the failover partner. The default value is an empty string, which causes SQL Server Native Client to use the default, provider-generated SPN.

The following properties in the DBPROPSET_SQLSERVERDBINIT property set allow applications to specify SPNs.

NameTypeUsage
SSPROP_INIT_SERVERSPNVT_BSTR, read/writeSpecifies the SPN for the server. The default value is an empty string, which causes SQL Server Native Client to use the default, provider-generated SPN.
SSPROP_INIT_FAILOVERPARTNERSPNVT_BSTR, read/writeSpecifies the SPN for the failover partner. The default value is an empty string, which causes SQL Server Native Client to use the default, provider-generated SPN.

The following properties in the DBPROPSET_SQLSERVERDATASOURCEINFO property set allow applications to discover the authentication method.

NameTypeUsage
SSPROP_INTEGRATEDAUTHENTICATIONMETHODVT_BSTR, readonlyReturns the authentication method used for the connection. The value returned to the application is the value that Windows returns to SQL Server Native Client. The following are possible values:
"NTLM", which is returned when a connection is opened using NTLM authentication.
"Kerberos", which is returned when a connection is opened using Kerberos authentication.

If a connection has been opened and the authentication method cannot be determined, VT_EMPTY is returned.

This property can only be read when a data source has been initialized. If you attempt to read the property before a data source has been initialized, IDBProperties::GetProperies will return DB_S_ERRORSOCCURRED or DB_E_ERRORSOCCURRED, as appropriate, and DBPROPSTATUS_NOTSUPPORTED will be set in DBPROPSET_PROPERTIESINERROR for this property. This behavior is in accordance with the OLE DB core specification.
SSPROP_MUTUALLYAUTHENICATEDVT_BOOL, readonlyReturns VARIANT_TRUE if the servers on the connection were mutually authenticated; otherwise, returns VARIANT_FALSE.

This property can only be read when a data source has been initialized. If there is an attempt to read the property before a data source has been initialized, IDBProperties::GetProperies will return DB_S_ERRORSOCCURRED or DB_E_ERRORSOCCURRED, as appropriate, and DBPROPSTATUS_NOTSUPPORTED will be set in DBPROPSET_PROPERTIESINERROR for this property. This behavior is in accordance with the OLE DB core specification

If this attribute is queried for a connection that did not use Windows Authentication, VARIANT_FALSE is returned.

The following table describes the OLE DB member functions that support SPNs in client connections:

Member functionDescription
IDataInitialize::GetDataSourcepwszInitializationString can contain the new keywords ServerSPN and FailoverPartnerSPN.
IDataInitialize::GetInitializationStringIf SSPROP_INIT_SERVERSPN and SSPROP_INIT_FAILOVERPARTNERSPN have non-default values, they will be included in the initialization string through ppwszInitString as keyword values for ServerSPN and FailoverPartnerSPN. Otherwise, these keywords will not be included in the initialization string.
IDBInitialize::InitializeIf prompting is enabled by setting DBPROP_INIT_PROMPT in the data source initialization properties, the OLE DB Login dialog box will be displayed. This allows SPNs to be entered for both the principal server and its failover partner.

The provider string in DPPROP_INIT_PROVIDERSTRING, if set, will recognize the new keywords ServerSPN and FailoverPartnerSPN and use their values, if present, to initialize SSPROP_INIT_SERVER_SPN and SSPROP_INIT_FAILOVER_PARTNER_SPN.

IDBProperties::SetProperties can be called to set the properties SSPROP_INIT_SERVER_SPN and SSPROP_INIT_FAILOVER_PARTNER_SPN before IDBInitialize::Initialize is called. This is an alternative to using a provider string.

If a property is set in more than one place, a value set programmatically takes precedence over a value set in the provider string. A value set in an initialization string takes precedence over a value set in a login dialog box.

If the same keyword appears more than once in the provider string, the value from first occurrence takes precedence.
IDBProperties::GetPropertiesIDBProperties::GetProperties can be called to get the values of the new data source initialization properties SSPROP_INIT_SERVERSPN and SSPROP_INIT_FAILOVERPARTNERSPN, and of the new data source properties SSPROP_AUTHENTICATIONMETHOD and SSPROP_MUTUALLYAUTHENTICATED.
IDBProperties::GetPropertyInfoIdbProperties::GetPropertyInfo will include the new data source initialization properties SSPROP_INIT_SERVERSPN and SSPROP_INIT_FAILOVERPARTNERSPN, or the new data source properties SSPROP_AUTHENTICATION_METHOD and SSPROP_MUTUALLYAUTHENTICATED.
IDBProperties::SetPropertiesIDBProperties::SetProperties can be called to set the values of the new data source initialization properties SSPROP_INITSERVERSPN and SSPROP_INIT_FAILOVERPARTNERSPN.

These properties can be set at any time, but if the data source is already open, the following error will be returned: DB_E_ERRORSOCCURRED, "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

SQL Server Native Client (OLE DB)

Community Additions

ADD
Show: