Redigera

Dela via


Initialization and Authorization Properties

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

Download OLE DB driver

The OLE DB Driver for SQL Server interprets OLE DB initialization and authorization properties as follows:

Property ID Description
DBPROP_AUTH_CACHE_AUTHINFO The OLE DB Driver for SQL Server does not cache authentication information.

The OLE DB Driver for SQL Server 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 OLE DB Driver for SQL Server uses standard Microsoft SQL Server security mechanisms to hide passwords.

The OLE DB Driver for SQL Server 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 OLE DB Driver for SQL Server 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 OLE DB Driver for SQL Server uses standard SQL Server security mechanisms to conceal passwords.

The OLE DB Driver for SQL Server 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 OLE DB Driver for SQL Server does not encrypt authentication information when persisted.

The OLE DB Driver for SQL Server 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 OLE DB Driver for SQL Server 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 OLE DB Driver for SQL Server 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 if there is 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 OLE DB Driver for SQL Server does not support impersonation level adjustment.

The OLE DB Driver for SQL Server 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 OLE DB Driver for SQL Server 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 OLE DB Driver for SQL Server 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 OLE DB Driver for SQL Server 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 OLE DB Driver for SQL Server supports all prompting modes for data source initialization. The OLE DB Driver for SQL Server uses DBPROMPT_NOPROMPT as its default setting for the property.
DBPROP_INIT_PROTECTION_LEVEL The OLE DB Driver for SQL Server does not support a protection level on connections to instances of SQL Server.

The OLE DB Driver for SQL Server 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 OLE DB Driver for SQL Server string later in this topic.
DBPROP_INIT_TIMEOUT The OLE DB Driver for SQL Server 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 OLE DB Driver for SQL Server defines these additional initialization properties.

Property ID Description
SSPROP_AUTH_ACCESS_TOKEN1 Type: VT_BSTR

R/W: Read/write

Default: VT_EMPTY

Description: The access token used to authenticate to Microsoft Entra ID.

NOTE: It's an error to specify this property and also UID, PWD, Trusted_Connection, or Authentication connection string keywords or their corresponding properties/keywords.
SSPROP_AUTH_MODE1 Type: VT_BSTR

R/W: Read/write

Default: VT_EMPTY

Description: Specifies the SQL or Microsoft Entra authentication used. Valid values are:
  • (not set): Authentication mode determined by other keywords.
  • (empty string): Unsets a previously set authentication mode.
  • ActiveDirectoryPassword:Username and password authentication with a Microsoft Entra identity.
  • ActiveDirectoryIntegrated: Integrated authentication with a Microsoft Entra identity.

  • NOTE: The ActiveDirectoryIntegrated keyword can also be used for Windows authentication to SQL Server. It replaces Integrated Security (or Trusted_Connection) authentication keywords. It's recommended that applications using Integrated Security (or Trusted_Connection) keywords or their corresponding properties set the value of the Authentication keyword (or its corresponding property) to ActiveDirectoryIntegrated to enable new encryption and certificate validation behavior.

  • ActiveDirectoryInteractive: Interactive authentication with a Microsoft Entra identity. This method supports Microsoft Entra multifactor authentication.
  • ActiveDirectoryMSI: Managed Identity authentication. For a user-assigned identity, the user ID should be set to the object ID of the user identity.
  • ActiveDirectoryServicePrincipal: Service principal authentication with a Microsoft Entra Application. The user ID should be set to the application (client) ID. The password should be set to the application (client) secret.
  • SqlPassword: Authentication using user ID and password.

  • NOTE: It's recommended that applications using SQL Server authentication set the value of the Authentication keyword (or its corresponding property) to SqlPassword to enable new encryption and certificate validation behavior.
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 OLE DB Driver for SQL Server 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 Server char, 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.

For versions 18.6.4 and 19.1+ of the OLE DB Driver, the above conversions also apply to data transferred between a CharVal member of the SSVARIANT structure and an instance of SQL Server sql_variant variable, parameter, or column.

These conversions are performed on the client by the OLE DB Driver for SQL Server. 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 OLE DB Driver for SQL Server does not perform character translations.

The OLE DB Driver for SQL Server 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. Similarly, for versions 18.6.4 and 19.1+ of the OLE DB Driver, the driver doesn't translate data transferred between a CharVal member of the SSVARIANT structure and an instance of SQL Server sql_variant variable, parameter, or column.

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

R/W: Read/write

Default: 1

Description: Controls the number of reconnection attempts in the case of connection loss. For more information, see Idle Connection Resiliency.
SSPROP_INIT_CONNECT_RETRY_INTERVAL Type: VT_I4

R/W: Read/write

Default: 10

Description: Specifies the number of seconds between each connection retry attempt in the case of connection loss. For more information, see Idle Connection Resiliency.
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 OLE DB Driver for SQL Server.
SSPROP_INIT_DISCOVEREDSERVERSPN Type: VT_BSTR

R/W: Read

Description: Used to query the server SPN of a connection. (Version 18.6.3 or later)
SSPROP_INIT_ENCRYPT1 Type: VT_BSTR

R/W: Read/Write

Default: Mandatory

Description: Specifies whether to encrypt the data going over the network.

If Force 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 Mandatory or Strict, then encryption will occur.

If Force Protocol Encryption is off and SSPROP_INIT_ENCRYPT is set to Optional, then no encryption occurs. Valid values are: Optional, Mandatory, and Strict. For versions 18.x.x, see MSOLEDBSQL major version differences.
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_FAILOVERPARTNERSPN Type: VT_BSTR

R/W: Read/write

Default: VT_EMPTY

Description: Specifies the SPN for the failover partner. An empty string causes OLE DB Driver for SQL Server to use the default, provider-generated SPN.
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_HOST_NAME_CERTIFICATE Type: VT_BSTR

R/W: Read/write

Default: VT_EMPTY

Description: The host name to be used in validating the SQL Server TLS/SSL certificate. If the SSPROP_INIT_HOST_NAME_CERTIFICATE property is not set, the driver uses the DBPROP_INIT_DATASOURCE property value as the host name to validate the SQL Server TLS/SSL certificate.
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_MULTISUBNETFAILOVER Type: VT_BOOL

R/W: Read/write

Default: VARIANT_FALSE

Description: MultiSubnetFailover enables faster failover for all Always On Availability Groups and Failover Cluster Instances in SQL Server, and will significantly reduce failover time for single and multi-subnet Always On topologies. During a multi-subnet failover, the client will attempt connections in parallel. For more information, see OLE DB Driver for SQL Server Support for High Availability, Disaster Recovery.
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 that 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 Tabular Data Stream (TDS) packet size in bytes. The packet size property value must be either 0 or between 512 and 32,767. The default value is 0, that means the actual packet size will be determined by the server.
SSPROP_INIT_SERVER_CERTIFICATE Type: VT_BSTR

R/W: Read/write

Default: VT_EMPTY

Description: Specifies the path to a certificate file to match against the SQL Server TLS/SSL certificate. This option can only be used when Strict encryption is enabled. The accepted certificate formats are PEM, DER, and CER. If specified, the SQL Server certificate is checked by seeing if the provided certificate is an exact match.
SSPROP_INIT_SERVERSPN Type: VT_BSTR

R/W: Read/write

Default: VT_EMPTY

Description: Specifies the SPN for the server. An empty string causes OLE DB Driver for SQL Server to use the default, provider-generated SPN.
SSPROP_INIT_TAGCOLUMNCOLLATION Type: VT_BOOL

R/W: Write

Default: VARIANT_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_TNIR Type: VT_BOOL

R/W: Read/write

Default: VARIANT_TRUE

Description: When there are multiple IPs associated with the hostname, TNIR affects the connection sequence when the first resolved IP of the hostname does not respond. TNIR interacts with MultiSubnetFailover to provide different connection sequences. For more information, see Using Transparent Network IP Resolution.
SSPROP_INIT_TRUST_SERVER_CERTIFICATE1 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 Encryption and certificate 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.

[1]: To improve security, encryption and certificate validation behavior is modified when using Authentication/Access Token initialization properties or their corresponding connection string keywords. For details, see Encryption and certificate validation.

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

The OLE DB Driver for SQL Server String

The OLE DB Driver for SQL Server 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 OLE DB Driver for SQL Server, 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 OLE DB Driver for SQL Server-specific initialization properties.

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

See Also

Data Source Objects (OLE DB)