Initialization and Authorization Properties (Native Client OLE DB Provider)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Important

The SQL Server Native Client (often abbreviated SNAC) has been removed from SQL Server 2022 (16.x) and SQL Server Management Studio 19 (SSMS). Both the SQL Server Native Client OLE DB provider (SQLNCLI or SQLNCLI11) and the legacy Microsoft OLE DB Provider for SQL Server (SQLOLEDB) are not recommended for new development. Switch to the new Microsoft OLE DB Driver (MSOLEDBSQL) for SQL Server going forward.

The SQL Server Native Client OLE DB provider interprets OLE DB initialization and authorization properties as follows:

Property ID Description
DBPROP_AUTH_CACHE_AUTHINFO The SQL Server Native Client OLE DB provider does not cache authentication information.

The SQL Server Native Client OLE DB provider returns DB_S_ERRORSOCCURRED on an attempt to set the property value. The dwStatus member of the DBPROP Structure indicates DBPROPSTATUS_NOTSUPPORTED.
DBPROP_AUTH_ENCRYPT_PASSWORD The SQL Server Native Client OLE DB provider uses standard Microsoft SQL Server security mechanisms to conceal passwords.

The SQL Server Native Client OLE DB provider returns DB_S_ERRORSOCCURRED on an attempt to set the property value. The dwStatus member of the DBPROP Structure indicates DBPROPSTATUS_NOTSUPPORTED.
DBPROP_AUTH_INTEGRATED If DBPROP_AUTH_INTEGRATED is set to a NULL pointer, a null string, or 'SSPI' VT_BSTR value, the SQL Server Native Client OLE DB provider uses Windows Authentication Mode to authorize user access to the SQL Server database specified by the DBPROP_INIT_DATASOURCE and DBPROP_INIT_CATALOG properties.

If it is set to VT_EMPTY (the default), SQL Server security is used. The SQL Server login and password are specified in the DBPROP_AUTH_USERID and DBPROP_AUTH_PASSWORD properties.
DBPROP_AUTH_MASK_PASSWORD The SQL Server Native Client OLE DB provider uses standard SQL Server security mechanisms to conceal passwords.

The SQL Server Native Client OLE DB provider returns DB_S_ERRORSOCCURRED on an attempt to set the property value. The dwStatus member of the DBPROP Structure indicates DBPROPSTATUS_NOTSUPPORTED.
DBPROP_AUTH_PASSWORD Password assigned to a SQL Server login. This property is used when SQL Server Authentication is selected for authorizing access to a SQL Server database.
DBPROP_AUTH_PERSIST_ENCRYPTED The SQL Server Native Client OLE DB provider does not encrypt authentication information when persisted.

The SQL Server Native Client OLE DB provider returns DB_S_ERRORSOCCURRED on an attempt to set the property value. The dwStatus member of the DBPROP Structure indicates DBPROPSTATUS_NOTSUPPORTED.
DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO The SQL Server Native Client OLE DB provider persists authentication values, including an image of a password, if requested to do so. No encryption is provided.
DBPROP_AUTH_USERID SQL Server login. This property is used when SQL Server Authentication is selected for authorizing access to a SQL Server database.
DBPROP_INIT_ASYNCH The SQL Server Native Client OLE DB provider supports asynchronous initiation.

Setting the DBPROPVAL_ASYNCH_INITIALIZE bit in the DBPROP_INIT_ASYNCH property causes IDBInitialize::Initialize to become a non-blocking call. For more information, see Performing Asynchronous Operations.
DBPROP_INIT_CATALOG Name of an existing SQL Server database to which to connect.
DBPROP_INIT_DATASOURCE Network name of a server running an instance of Microsoft SQL Server. If there are multiple instances of SQL Server running on the computer, in order to connect to a specific instance of SQL Server the value DBPROP_INIT_DATASOURCE is specified as \\ServerName\InstanceName. The escape sequence \\ is used for backslash itself.
DBPROP_INIT_GENERALTIMEOUT Indicates the number of seconds before a request, other than data source initialization and command execution, times out. A value of 0 indicates an infinite time-out. Providers that work over network connections or in distributed or transacted scenarios can support this property to advise an enlisted component to time-out in the event of a long-running request. Time-outs for data source initialization and command execution remain governed by DBPROP_INIT_TIMEOUT and DBPROP_COMMANDTIMEOUT, respectively.

DBPROP_INIT_GENERALTIMEOUT is read-only, and if one tries to set it the dwstatus error of DBPROPSTATUS_NOTSETTABLE is returned.
DBPROP_INIT_HWND The Windows handle from the calling application. A valid window handle is required for the initialization dialog box displayed when prompting for initialization properties is allowed.
DBPROP_INIT_IMPERSONATION_LEVEL The SQL Server Native Client OLE DB provider does not support impersonation level adjustment.

The SQL Server Native Client OLE DB provider returns DB_S_ERRORSOCCURRED on an attempt to set the property value. The dwStatus member of the DBPROP Structure indicates DBPROPSTATUS_NOTSUPPORTED.
DBPROP_INIT_LCID The SQL Server Native Client OLE DB provider validates the locale ID and returns an error if the locale ID is not supported or is not installed on the client.
DBPROP_INIT_LOCATION The SQL Server Native Client OLE DB provider returns DB_S_ERRORSOCCURRED on an attempt to set the property value. The dwStatus member of the DBPROP Structure indicates DBPROPSTATUS_NOTSUPPORTED.
DBPROP_INIT_MODE The SQL Server Native Client OLE DB provider returns DB_S_ERRORSOCCURRED on an attempt to set the property value. The dwStatus member of the DBPROP Structure indicates DBPROPSTATUS_NOTSUPPORTED.
DBPROP_INIT_PROMPT The SQL Server Native Client OLE DB provider supports all prompting modes for data source initialization. The SQL Server Native Client OLE DB provider uses DBPROMPT_NOPROMPT as its default setting for the property.
DBPROP_INIT_PROTECTION_LEVEL The SQL Server Native Client OLE DB provider does not support a protection level on connections to instances of SQL Server.

The SQL Server Native Client OLE DB provider returns DB_S_ERRORSOCCURRED on an attempt to set the property value. The dwStatus member of the DBPROP Structure indicates DBPROPSTATUS_NOTSUPPORTED.
DBPROP_INIT_PROVIDERSTRING See the SQL Server Native Client OLE DB provider string later in this topic.
DBPROP_INIT_TIMEOUT The SQL Server Native Client OLE DB provider returns an error on initialization if a connection to the instance of SQL Server cannot be established within the number of seconds specified.

In the provider-specific property set DBPROPSET_SQLSERVERDBINIT, the SQL Server Native Client OLE DB provider defines these additional initialization properties.

Property ID Description
SSPROP_AUTH_OLD_PASSWORD Type: VT_BSTR

R/W: Write

Default: VT_EMPTY

Description: The current or expired password. For more information, see Changing Passwords Programmatically.
SSPROP_INIT_APPNAME Type: VT_BSTR

R/W: Read/write

Description: The client application name.
SSPROP_INIT_AUTOTRANSLATE Type: VT_BOOL

R/W: Read/write

Default: VARIANT_TRUE

Description: OEM/ANSI character conversion.

VARIANT_TRUE: The SQL Server Native Client OLE DB provider translates ANSI character strings sent between the client and server by converting through Unicode to minimize problems in matching extended characters between the code pages on the client and the server:

Client DBTYPE_STR data sent to an instance of SQL Serverchar, varchar, or text variable, parameter, or column is converted from character to Unicode using the client ANSI code page (ACP) and then converted from Unicode to character using the ACP of the server.

SQL Server char, varchar, or text data sent to a client DBTYPE_STR variable is converted from character to Unicode using the server ACP and then converted from Unicode to character using the client ACP.

These conversions are performed on the client by the SQL Server Native Client OLE DB provider. This requires that the same ACP used on the server be available on the client.

These settings have no effect on the conversions that occur for these transfers:

Unicode DBTYPE_WSTR client data sent to char, varchar, or text on the server.

char, varchar, or text server data sent to a Unicode DBTYPE_WSTR variable on the client.

ANSI DBTYPE_STR client data sent to Unicode nchar, nvarchar, or ntext on the server.

Unicode char, varchar, or text server data sent to an ANSI DBTYPE_STR variable on the client.

VARIANT_FALSE: The SQL Server Native Client OLE DB provider does not perform character translations.

The SQL Server Native Client OLE DB provider does not translate client ANSI character DBTYPE_STR data sent to char, varchar, or text variables, parameters, or columns on the server. No translation is performed on char, varchar, or text data sent from the server to DBTYPE_STR variables on the client.

If the client and the instance of SQL Server are using different ACPs, extended characters can be misinterpreted.
SSPROP_INIT_CURRENTLANGUAGE Type: VT_BSTR

R/W: Read/write

Description: A SQL Server language name. Identifies the language used for system message selection and formatting. The language must be installed on the computer running an instance of SQL Server or data source initialization fails.
SSPROP_INIT_DATATYPECOMPATIBILITY Type: VT_UI2

R/W: Read/write

Default: 0

Description: Enables data type compatibility between SQL Server and ActiveX Data Object (ADO) applications. If the default value of 0 is used, data type handling defaults to that used by the provider. If the value of 80 is used, data type handling uses only SQL Server 2000 (8.x) data types. For more information, see Using ADO with SQL Server Native Client.
SSPROP_INIT_ENCRYPT Type: VT_BOOL

R/W: Read/Write

Default: VARIANT_FALSE

Description: To encrypt the data going over the network, SSPROP_INIT_ENCRYPT property is set to VARIANT_TRUE.

If Enable Protocol Encryption is on, encryption will always occur, regardless of the setting of SSPROP_INIT_ENCRYPT. If it is off and SSPROP_INIT_ENCRYPT is set to VARIANT_TRUE, then encryption will occur.

If Enable Protocol Encryption is off and SSPROP_INIT_ENCRYPT is set to VARIANT_FALSE, then no encryption occurs.
SSPROP_INIT_FAILOVERPARTNER Type: VT_BSTR

R/W: Read/write

Description: Specifies the name of the failover partner for database mirroring. It is an initialization property and can only be set before initialization. After initialization it will return the failover partner, if any, returned by the primary server.

This allows a smart application to cache the most recently determined backup server, but such applications should be aware that the information is only updated when the connection is first established (or reset, if pooled) and can become out of date for long term connections.

After making the connection, the application can query this attribute to determine the identity of the failover partner. If the primary server has no failover partner this property will return an empty string. For more information, see Using Database Mirroring.
SSPROP_INIT_FILENAME Type: VT_BSTR

R/W: Read/write

Description: Specifies the primary file name of an attachable database. This database is attached and becomes the default database for the connection. To use SSPROP_INIT_FILENAME, you must specify the name of the database as the value of the initialization property DBPROP_INIT_CATALOG. If the database name does not exist, then it looks for the primary file name specified in SSPROP_INIT_FILENAME and attaches that database with the name specified in DBPROP_INIT_CATALOG. If the database was previously attached, SQL Server does not reattach it.
SSPROP_INIT_MARSCONNECTION Type: VT_BOOL

R/W: Read/write

Default: VARIANT_FALSE

Description: Specifies if Multiple Active Result Sets (MARS) are enabled for the connection. This option must be set to true before a connection is made to the database. For more information, see Using Multiple Active Result Sets (MARS).
SSPROP_INIT_NETWORKADDRESS Type: VT_BSTR

R/W: Read/write

Description: The network address of the server running an instance of SQL Server specified by the DBPROP_INIT_DATASOURCE property.
SSPROP_INIT_NETWORKLIBRARY Type: VT_BSTR

R/W: Read/write

Description: The name of the networklibrary (DLL) used to communicate with an instance of SQL Server. The name should not include the path or the .dll file name extension.

The default can be customized using the SQL Server Client Configuration Utility.

Note: Only TCP and Named Pipes are supported by this property. If you use this property with a prefix, you end up with a double prefix which results in an error, because the property is used to generate a prefix internally.
SSPROP_INIT_PACKETSIZE Type: VT_I4

R/W: Read/write

Description: A network packet size in bytes. The packet size property value must be between 512 and 32,767. The default SQL Server Native Client OLE DB provider network packet size is 4,096.
SSPROP_INIT_TAGCOLUMNCOLLATION Type: BOOL

R/W: Write

Default: FALSE

Description: Is used during a database update when server-side cursors are used. This property tags the data with collation information obtained from the server instead of the code page on the client. Currently, this property is used only by the distributed query process because it knows the collation of destination data and converts it correctly.
SSPROP_INIT_TRUST_SERVER_CERTIFICATE Type: VT_BOOL

R/W: Read/write

Default: VARIANT_FALSE

Description: Used to enable or disable server certificate validation. This property is read/write, but attempting to set it after a connection has been established will result in an error.

This property is ignored if the client is configured to require certificate validation. However, an application can use it together with SSPROP_INIT_ENCRYPT to guarantee that its connection to the server is encrypted, even if the client is configured not to require encryption and no certificate is provisioned on the client.

Client applications can query this property after a connection has been opened to determine the actual encryption and validation settings in use.

Note: Using encryption without certificate validation provides partial protection against packet sniffing, but it does not protect against man-in-the-middle attacks. It simply allows for encrypting the login and data sent to the server without validating the server certificate.

For more information, see Using Encryption Without Validation.
SSPROP_INIT_USEPROCFORPREP Type: VT_I4

R/W: Read/write

Default: SSPROPVAL_USEPROCFORPREP_ON

Description: The SQL Server stored procedure use. Defines the use of SQL Server temporary stored procedures to support the ICommandPrepare interface. This property was meaningful only when connecting to SQL Server 6.5. The property is ignored for later versions.

SSPROPVAL_USEPROCFORPREP_OFF: A temporary stored procedure is not created when a command is prepared.

SSPROPVAL_USEPROCFORPREP_ON: A temporary stored procedure is created when a command is prepared. The temporary stored procedures are dropped when the session is released.

SSPROPVAL_USEPROCFORPREP_ON_DROP: A temporary stored procedure is created when a command is prepared. The procedure is dropped when the command is unprepared with ICommandPrepare::Unprepare, when a new command is specified for the command object with ICommandText::SetCommandText, or when all application references to the command are released.
SSPROP_INIT_WSID Type: VT_BSTR

R/W: Read/write

Description: A string identifying the workstation.

In the provider-specific property set DBPROPSET_SQLSERVERDATASOURCEINFO, the SQL Server Native Client OLE DB provider defines the additional properties; see Data Source Information Properties for more information.

The SQL Server Native Client OLE DB Provider String

The SQL Server Native Client OLE DB provider recognizes an ODBC-like syntax in provider string property values. The provider string property is provided as the value of the OLE DB initialization property DBPROP_INIT_PROVIDERSTRING when a connection is established to the OLE DB data source. This property specifies OLE DB provider-specific connection data required to implement a connection to the OLE DB data source. Within the string, elements are delimited by using a semicolon. The final element in the string must be terminated with a semicolon. Each element consists of a keyword, an equal sign character, and the value passed on initialization. For example:

Server=MyServer;UID=MyUserName;  

With the SQL Server Native Client OLE DB provider, the consumer never needs to use the provider string property. The consumer can set any initialization property reflected in the provider string by using either OLE DB or SQL Server Native Client OLE DB provider-specific initialization properties.

For a list of the keywords available in the SQL Server Native Client OLE DB provider, see Using Connection String Keywords with SQL Server Native Client.

See Also

Data Source Objects (OLE DB)