DSN and Connection String Keywords and Attributes
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.
Supported DSN/Connection String Keywords and Connection Attributes
The following table lists the available keywords and the attributes for each platform (L: Linux; M: macOS; W: Windows). Select the keyword or attribute for more details.
Here are some connection string keywords and connection attributes, which aren't documented in Using Connection String Keywords with SQL Server Native Client, SQLSetConnectAttr, and SQLSetConnectAttr Function.
Description
Used to describe the data source.
SQL_COPT_SS_ANSI_OEM
Controls ANSI to OEM conversion of data.
| Attribute Value | Description |
|---|---|
| SQL_AO_OFF | (Default) Translation isn't done. |
| SQL_AO_ON | Translation is done. |
SQL_COPT_SS_AUTOBEGINTXN
Version 17.6+ While autocommit is off, controls automatic BEGIN TRANSACTION after ROLLBACK or COMMIT.
| Attribute Value | Description |
|---|---|
| SQL_AUTOBEGINTXN_ON | (Default) Automatic BEGIN TRANSACTION after ROLLBACK or COMMIT. |
| SQL_AUTOBEGINTXN_OFF | No automatic BEGIN TRANSACTION after ROLLBACK or COMMIT. |
SQL_COPT_SS_FALLBACK_CONNECT
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. |
New Connection String Keywords and Connection Attributes
Authentication - SQL_COPT_SS_AUTHENTICATION
Sets the authentication mode to use when connecting to SQL Server. For more information, see Using Microsoft Entra ID.
| 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 | Microsoft Entra integrated authentication. |
| ActiveDirectoryPassword | SQL_AU_AD_PASSWORD | Microsoft Entra password authentication. |
| ActiveDirectoryInteractive | SQL_AU_AD_INTERACTIVE | Microsoft Entra Interactive authentication. |
| ActiveDirectoryMsi | SQL_AU_AD_MSI | Microsoft Entra managed identity authentication. For user-assigned identity, UID is set to the object ID of the user identity. |
| ActiveDirectoryServicePrincipal | SQL_AU_AD_SPA | Microsoft Entra service principal authentication. UID is set to the client ID of the service principal. PWD is set to the client secret. |
| 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.
ColumnEncryption - SQL_COPT_SS_COLUMN_ENCRYPTION
Controls transparent column encryption (Always Encrypted). For more information, see Using Always Encrypted (ODBC).
| 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). |
Encrypt
Specifies whether connections use TLS encryption over the network. Possible values are yes/mandatory(18.0+), no/optional(18.0+), and strict(18.0+). The default value is yes in version 18.0+ and no in previous versions.
Regardless of the setting for Encrypt, the server login credentials (user name and password) are always encrypted.
Encrypt, TrustServerCertificate, and server-side Force Encryption settings play a part in whether connections are encrypted over the network. The following tables show the effect of these settings.
ODBC Driver 18 and newer
| Encrypt Setting | Trust Server Certificate | Server Force Encryption | Result |
|---|---|---|---|
| No | No | No | Server certificate isn't checked. Data sent between client and server isn't encrypted. |
| No | Yes | No | Server certificate isn't checked. Data sent between client and server isn't encrypted. |
| Yes | No | No | Server certificate is checked. Data sent between client and server is encrypted. |
| Yes | Yes | No | Server certificate isn't checked. Data sent between client and server is encrypted. |
| No | No | Yes | Server certificate is checked. Data sent between client and server is encrypted. |
| No | Yes | Yes | Server certificate isn't checked. Data sent between client and server is encrypted. |
| Yes | No | Yes | Server certificate is checked. Data sent between client and server is encrypted. |
| Yes | Yes | Yes | Server certificate isn't checked. Data sent between client and server is encrypted. |
| Strict | - | - | TrustServerCertificate is ignored. Server certificate is checked. Data sent between client and server is encrypted. |
Note
Strict is only available against servers that support TDS 8.0 connections.
ODBC Driver 17 and older
| Encrypt Setting | Trust Server Certificate | Server Force Encryption | Result |
|---|---|---|---|
| No | No | No | Server certificate isn't checked. Data sent between client and server isn't encrypted. |
| No | Yes | No | Server certificate isn't checked. Data sent between client and server isn't encrypted. |
| Yes | No | No | Server certificate is checked. Data sent between client and server is encrypted. |
| Yes | Yes | No | Server certificate isn't checked. Data sent between client and server is encrypted. |
| No | No | Yes | Server certificate isn't checked. Data sent between client and server is encrypted. |
| No | Yes | Yes | Server certificate isn't checked. Data sent between client and server is encrypted. |
| Yes | No | Yes | Server certificate is checked. Data sent between client and server is encrypted. |
| Yes | Yes | Yes | Server certificate isn't checked. Data sent between client and server is encrypted. |
TransparentNetworkIPResolution - SQL_COPT_SS_TNIR
Controls the Transparent Network IP Resolution feature, which interacts with MultiSubnetFailover to allow faster reconnection attempts. For more information, see Using Transparent Network IP Resolution.
| Keyword Value | Attribute Value | Description |
|---|---|---|
| Enabled | SQL_IS_ON | (Default) Enables Transparent Network IP Resolution. |
| Disabled | SQL_IS_OFF | Disables Transparent Network IP Resolution. |
UseFMTONLY
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. |
Replication
Specifies the use of a replication login on ODBC Driver version 17.8 and newer.
| Keyword Value | Description |
|---|---|
| No | (Default) Replication login isn't used. |
| Yes | Triggers with the NOT FOR REPLICATION option don't fire on the connection. |
RetryExec
Configurable retry logic is available starting in version 18.1. It automatically re-executes specific ODBC function calls based on configurable conditions. This feature can be enabled through the connection string using the RetryExec keyword, along with a list of retry rules. Each retry rule has three colon separated components: an error match, retry policy, and a query match.
The query match determines the retry rule to be used for a given execution, and is matched with the incoming command text (SQLExecDirect) or the prepared command text in the statement object (SQLExecute). If more than one rule matches, the first matching one in the list is used. This behavior allows rules to be listed in order of increasing generality. If no rule matches, then no retry is applied.
When the execution results in an error, and there's an applicable retry rule, its error match is used to determine if the execution should be retried.
The value of the RetryExec keyword is a list of semicolon separated retry rules.
RetryExec={rule1;rule2}
A retry rule is as follows: <errormatch>:<retrypolicy>:<querymatch>
Error Match: A comma separated list of error codes. For example, specifying 1000,2000 would be the error codes you want to retry.
Retry Policy: Specifies the delay until the next retry. The first parameter would be the number of retries while the second would be the delay. For example, 3,10+7 would be 3 tries starting at 10 and each following retry would increment by 7 seconds. If +7 isn't specified, then each following retry is exponentially doubled.
Query Match: Specifies the query you want to match with. If nothing is specified, then it applies to all queries. Specifying SELECT would mean for all queries that start with select.
Combining all three above components together to use in a connection string would be:
RetryExec={1000,2000:3,10+7:SELECT}
Which would mean: "For errors 1000 and 2000, on a query that starts with SELECT. Retry twice with an initial delay of 10 seconds and adding 7 seconds for each following attempt"
Examples
40501,40540:4,5
For errors 40501 and 40540, retry up to four times, with an initial delay of 5 seconds, and exponential doubling between each retry. This rule applies to all queries.
49919:2,10+:CREATE
For error 49919 on a query that starts with CREATE, retry at most twice, initially after 10 seconds, and then 20 seconds.
49918,40501,10928:5,10+5:SELECT c1
For errors, 49918, 40501, and 10928 on queries starting with SELECT c1, retry up to five times, waiting 10 seconds on the first retry and increasing the wait by 5 seconds thereafter.
The above three rules can be specified together in the connection string as follows:
RetryExec={49918,40501,10928:5,10+5:SELECT c1;49919:2,10+:CREATE;40501,40540:4,5}
The most general (match-all) rule is placed at the end, to allow the two more specific rules before it to match their respective queries.
ClientCertificate
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
ClientKey
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.
HostnameInCertificate
Specifies the hostname to be expected in the server's certificate when encryption is negotiated, if it's different from the default value derived from Addr/Address/Server. The HostnameInCertificate option is ignored when using the ServerCertificate option.
IpAddressPreference
Available starting with version 18.1, this option allows the user to specify the type of IP Address they want to prioritize for connections. The possible options are "IpAddress= [ IPv4First | IPv6First | UsePlatformDefault]." UsePlatformDefault connects to addresses in the order they're provided by the system call to resolve the server name. The default value is IPv4First, which corresponds to the behavior in previous versions.
ServerCertificate
Available starting with version 18.1, this option can be used with the strict encryption mode. The ServerCertificate keyword is used to specify the path to a certificate file to match against the SQL Server TLS/SSL certificate. The match is done instead of standard certificate validation (expiry, host name, trust chain, etc.) The accepted certificate formats are PEM, DER, and CER. If specified, the SQL Server certificate is checked by seeing if the ServerCertificate provided is an exact match.
SQL_COPT_SS_ACCESS_TOKEN
Allows the use of a Microsoft Entra access token for authentication. For more information, see Using Microsoft Entra ID.
| Attribute Value | Description |
|---|---|
| NULL | (Default) No access token is supplied. |
| ACCESSTOKEN* | Pointer to an access token. |
SQL_COPT_SS_CEKEYSTOREDATA
Communicates with a loaded keystore provider library. See Controls transparent column encryption (Always Encrypted). This attribute has no default value. For more information, see Custom Keystore Providers.
| Attribute Value | Description |
|---|---|
| CEKEYSTOREDATA * | Communication data structure for keystore provider library |
SQL_COPT_SS_CEKEYSTOREPROVIDER
Loads a keystore provider library for Always Encrypted, or retrieves the names of loaded keystore provider libraries. For more information, see Custom Keystore Providers. This attribute has no default value.
| Attribute Value | Description |
|---|---|
| char * | Path to a keystore provider library |
SQL_COPT_SS_ENLIST_IN_XA
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+), Linux, and macOS.
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 setting is only valid on Windows and can't 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 macOS |
| TRUE | Associates the XA transaction with the ODBC connection. All related database activities are performed under the protection of the XA transaction. | Windows |
| FALSE | Disassociates the transaction with the ODBC connection. | Windows |
For more information about XA transactions, see Using XA Transactions.
SQL_COPT_SS_LONGASMAX
Allows long type data to be sent to servers as max type data.
| Attribute Value | Description |
|---|---|
| No | (Default) Don't convert long types to max types when sending. |
| Yes | Converts data from long types to max types when sending. |
SQL_COPT_SS_SPID
Retrieves the server process ID of the connection. This property is equivalent to the T-SQL @@SPID variable, except that it doesn't incur an extra round trip to the server.
| Attribute Value | Description |
|---|---|
| DWORD | SPID |