| title | DSN and connection string keywords for the ODBC driver - SQL Server | Microsoft Docs |
|---|---|
| ms.custom | |
| ms.date | 02/04/2019 |
| ms.prod | sql |
| ms.prod_service | connectivity |
| ms.technology | connectivity |
| ms.topic | conceptual |
| ms.reviewer | v-chojas |
| ms.author | v-jizho2 |
| author | karinazhou |
This page lists the keywords for connection strings and DSNs, and connection attributes for SQLSetConnectAttr and SQLGetConnectAttr, available in the ODBC Driver for SQL Server.
The following table lists the available keywords and the attributes for each platform (L: Linux ; M: Mac ; W: Windows). Click the keyword or attribute for more details.
Here are some connection string keywords and connection attributes which are not documented in Using Connection String Keywords with SQL Server Native Client, SQLSetConnectAttr and SQLSetConnectAttr Function.
Used to describe the data source.
Controls ANSI to OEM conversion of data.
| Attribute Value | Description |
|---|---|
| SQL_AO_OFF | (Default) Translation is not performed. |
| SQL_AO_ON | Translation is performed. |
Controls the use of SQL Server Fallback Connections. This one is no longer supported.
| Attribute Value | Description |
|---|---|
| SQL_FB_OFF | (Default) Fallback connections are disabled. |
| SQL_FB_ON | Fallback connections are enabled. |
Sets the authentication mode to use when connecting to SQL Server. See Using Azure Active Directory for more information.
| Keyword Value | Attribute Value | Description |
|---|---|---|
| SQL_AU_NONE | (Default) Not set. Combination of other attributes determines authentication mode. | |
| SqlPassword | SQL_AU_PASSWORD | SQL Server authentication with username and password. |
| ActiveDirectoryIntegrated | SQL_AU_AD_INTEGRATED | Azure Active Directory Integrated authentication. |
| ActiveDirectoryPassword | SQL_AU_AD_PASSWORD | Azure Active Directory Password authentication. |
| ActiveDirectoryInteractive | SQL_AU_AD_INTERACTIVE | Azure Active Directory Interactive authentication. |
| ActiveDirectoryMsi | SQL_AU_AD_MSI | Azure Active Directory Managed Service Identity authentication. For user-assigned identity, UID is set to the object ID of the user identity. |
| SQL_AU_RESET | Unset. Overrides any DSN or connection string setting. |
Note
When using Authentication keyword or attribute, explicitly specify Encrypt setting to the desired value in connection string / DSN / connection attribute. Refer to Using Connection String Keywords with SQL Server Native Client for details.
Controls transparent column encryption (Always Encrypted). See Using Always Encrypted (ODBC) for more information.
| Keyword Value | Attribute Value | Description |
|---|---|---|
| Enabled | SQL_CE_ENABLED | Enables Always Encrypted. |
| Disabled | SQL_CE_DISABLED | (Default) Disables Always Encrypted. |
| SQL_CE_RESULTSETONLY | Enables decryption only (results and return values). |
Controls the Transparent Network IP Resolution feature, which interacts with MultiSubnetFailover to allow faster reconnection attempts. See Using Transparent Network IP Resolution for more information.
| Keyword Value | Attribute Value | Description |
|---|---|---|
| Enabled | SQL_IS_ON | (Default) Enables Transparent Network IP Resolution. |
| Disabled | SQL_IS_OFF | Disables Transparent Network IP Resolution. |
Controls the use of SET FMTONLY for metadata when connecting to SQL Server 2012 and newer.
| Keyword Value | Description |
|---|---|
| No | (Default) Use sp_describe_first_result_set for metadata if available. |
| Yes | Use SET FMTONLY for metadata. |
Specifies the certificate to be used for authentication. The options are:
| Option Value | Description |
|---|---|
sha1:<hash_value> |
The ODBC driver uses SHA1 hash to locate a certificate in Windows Certificate Store |
subject:<subject> |
The ODBC driver uses subject to locate a certificate in Windows Certificate Store |
file:<file_location>[,password:<password>] |
The ODBC driver uses a certificate file. |
In case if certificate is in PFX format and private key inside the PFX certificate is password protected, the password keyword is required. For certificates in PEM and DER formats ClientKey attribute is required
Specifies a file location of the private key for PEM or DER certificates specified by the ClientCertificate attribute. Format:
| Option Value | Description |
|---|---|
file:<file_location>[,password:<password>] |
Specifies location of the private key file. |
In case if private key file is password protected then password keyword is required. If the password contains any "," characters, an extra "," character is added immediately after each one. For example, if the password is "a,b,c", the escaped password present in the connection string is "a,,b,,c".
Allows the use of an Azure Active Directory access token for authentication. See Using Azure Active Directory for more information.
| Attribute Value | Description |
|---|---|
| NULL | (Default) No access token is supplied. |
| ACCESSTOKEN* | Pointer to an access token. |
Communicates with a loaded keystore provider library. See Controls transparent column encryption (Always Encrypted). This attribute has no default value. See Custom Keystore Providers for more information.
| Attribute Value | Description |
|---|---|
| CEKEYSTOREDATA * | Communication data structure for keystore provider library |
Loads a keystore provider library for Always Encrypted, or retrieves the names of loaded keystore provider libraries. See Custom Keystore Providers for more information. This attribute has no default value.
| Attribute Value | Description |
|---|---|
| char * | Path to a keystore provider library |
To enable XA transactions with an XA-compliant Transaction Processor (TP), the application needs to call SQLSetConnectAttr with SQL_COPT_SS_ENLIST_IN_XA and a pointer to an XACALLPARAM object. This option is supported on Windows, (17.3 and above) Linux and Mac.
SQLSetConnectAttr(hdbc, SQL_COPT_SS_ENLIST_IN_XA, param, SQL_IS_POINTER); // XACALLPARAM *param
To associate an XA transaction with an ODBC connection only, provide TRUE or FALSE with SQL_COPT_SS_ENLIST_IN_XA instead of the pointer when calling SQLSetConnectAttr. This is only valid on Windows and cannot be used to specify XA operations through a client application.
SQLSetConnectAttr(hdbc, SQL_COPT_SS_ENLIST_IN_XA, (SQLPOINTER)TRUE, 0);
| Value | Description | Platforms |
|---|---|---|
| XACALLPARAM object* | The pointer to XACALLPARAM object. |
Windows, Linux and Mac |
| TRUE | Associates the XA transaction with the ODBC connection. All related database activities will be performed under the protection of the XA transaction. | Windows |
| FALSE | Disassociates the transaction with the ODBC connection. | Windows |
See Using XA Transactions for more information about XA transactions.
Retrieves the server process ID of the connection. This is equivalent to the T-SQL @@SPID variable, except that it does not incur an additional round-trip to the server.
| Attribute Value | Description |
|---|---|
| DWORD | SPID |