Muokkaa

Jaa


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.

DSN / Connection String Keyword Connection Attribute Platform
Addr LMW
Address LMW
AnsiNPW SQL_COPT_SS_ANSI_NPW LMW
APP LMW
ApplicationIntent SQL_COPT_SS_APPLICATION_INTENT LMW
AttachDBFileName SQL_COPT_SS_ATTACHDBFILENAME LMW
Authentication SQL_COPT_SS_AUTHENTICATION LMW
AutoTranslate SQL_COPT_SS_TRANSLATE LMW
ClientCertificate LMW
ClientKey LMW
ColumnEncryption SQL_COPT_SS_COLUMN_ENCRYPTION LMW
ConnectRetryCount SQL_COPT_SS_CONNECT_RETRY_COUNT LMW
ConnectRetryInterval SQL_COPT_SS_CONNECT_RETRY_INTERVAL LMW
Database SQL_ATTR_CURRENT_CATALOG LMW
Description LMW
Driver LMW
DSN LMW
Encrypt SQL_COPT_SS_ENCRYPT LMW
Failover_Partner SQL_COPT_SS_FAILOVER_PARTNER W
FailoverPartnerSPN SQL_COPT_SS_FAILOVER_PARTNER_SPN W
FileDSN LMW
GetDataExtensions (v18.0+) SQL_COPT_SS_GETDATA_EXTENSIONS LMW
HostnameInCertificate (v18.0+) LMW
IpAddressPreference (v18.1+) LMW
KeepAlive (v17.4+; DSN only prior to 17.8) LMW
KeepAliveInterval (v17.4+; DSN only prior to 17.8) LMW
KeystoreAuthentication LMW
KeystorePrincipalId LMW
KeystoreSecret LMW
Language LMW
LongAsMax (v18.0+) SQL_COPT_SS_LONGASMAX LMW
MARS_Connection SQL_COPT_SS_MARS_ENABLED LMW
MultiSubnetFailover SQL_COPT_SS_MULTISUBNET_FAILOVER LMW
Net LMW
Network LMW
PWD LMW
QueryLog_On SQL_COPT_SS_PERF_QUERY W
QueryLogFile SQL_COPT_SS_PERF_QUERY_LOG W
QueryLogTIme SQL_COPT_SS_PERF_QUERY_INTERVAL W
QuotedId SQL_COPT_SS_QUOTED_IDENT LMW
Regional LMW
Replication LMW
RetryExec (18.1+) LMW
SaveFile LMW
Server LMW
ServerCertificate (v18.1+) LMW
ServerSPN SQL_COPT_SS_SERVER_SPN LMW
StatsLog_On SQL_COPT_SS_PERF_DATA W
StatsLogFile SQL_COPT_SS_PERF_DATA_LOG W
TransparentNetworkIPResolution SQL_COPT_SS_TNIR LMW
Trusted_Connection SQL_COPT_SS_INTEGRATED_SECURITY LMW
TrustServerCertificate SQL_COPT_SS_TRUST_SERVER_CERTIFICATE LMW
UID LMW
UseFMTONLY LMW
WSID LMW
SQL_ATTR_ACCESS_MODE
(SQL_ACCESS_MODE)
LMW
SQL_ATTR_ASYNC_DBC_EVENT W
SQL_ATTR_ASYNC_DBC_FUNCTIONS_ENABLE W
SQL_ATTR_ASYNC_DBC_PCALLBACK W
SQL_ATTR_ASYNC_DBC_PCONTEXT W
SQL_ATTR_ASYNC_ENABLE W
SQL_ATTR_AUTO_IPD LMW
SQL_ATTR_AUTOCOMMIT
(SQL_AUTOCOMMIT)
LMW
SQL_ATTR_CONNECTION_DEAD LMW
SQL_ATTR_CONNECTION_TIMEOUT LMW
SQL_ATTR_DBC_INFO_TOKEN LMW
SQL_ATTR_LOGIN_TIMEOUT
(SQL_LOGIN_TIMEOUT)
LMW
SQL_ATTR_METADATA_ID LMW
SQL_ATTR_ODBC_CURSORS
(SQL_ODBC_CURSORS)
LMW
SQL_ATTR_PACKET_SIZE
(SQL_PACKET_SIZE)
LMW
SQL_ATTR_QUIET_MODE
(SQL_QUIET_MODE)
LMW
SQL_ATTR_RESET_CONNECTION
(SQL_COPT_SS_RESET_CONNECTION)
LMW
SQL_ATTR_TRACE
(SQL_OPT_TRACE)
LMW
SQL_ATTR_TRACEFILE
(SQL_OPT_TRACEFILE)
LMW
SQL_ATTR_TRANSLATE_LIB
(SQL_TRANSLATE_DLL)
LMW
SQL_ATTR_TRANSLATE_OPTION
(SQL_TRANSLATE_OPTION)
LMW
SQL_ATTR_TXN_ISOLATION
(SQL_TXN_ISOLATION)
LMW
SQL_COPT_SS_ACCESS_TOKEN LMW
SQL_COPT_SS_ANSI_OEM W
SQL_COPT_SS_AUTOBEGINTXN LMW
SQL_COPT_SS_BCP LMW
SQL_COPT_SS_BROWSE_CACHE_DATA LMW
SQL_COPT_SS_BROWSE_CONNECT LMW
SQL_COPT_SS_BROWSE_SERVER LMW
SQL_COPT_SS_CEKEYSTOREDATA LMW
SQL_COPT_SS_CEKEYSTOREPROVIDER LMW
SQL_COPT_SS_CLIENT_CONNECTION_ID LMW
SQL_COPT_SS_CONCAT_NULL LMW
SQL_COPT_SS_CONNECTION_DEAD LMW
SQL_COPT_SS_DATACLASSIFICATION_VERSION (v17.4.2+) LMW
SQL_COPT_SS_ENLIST_IN_DTC W
SQL_COPT_SS_ENLIST_IN_XA LMW
SQL_COPT_SS_FALLBACK_CONNECT LMW
SQL_COPT_SS_INTEGRATED_AUTHENTICATION_METHOD LMW
SQL_COPT_SS_MUTUALLY_AUTHENTICATED LMW
SQL_COPT_SS_OLDPWD LMW
SQL_COPT_SS_PERF_DATA_LOG_NOW W
SQL_COPT_SS_PRESERVE_CURSORS LMW
SQL_COPT_SS_SPID (v17.5+) LMW
SQL_COPT_SS_TXN_ISOLATION LMW
SQL_COPT_SS_USER_DATA LMW
SQL_COPT_SS_WARN_ON_CP_ERROR LMW

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