DSN and Connection String Keywords and Attributes

Članak
02/15/2022
8 min. za čitanje

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
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
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
SaveFile		LMW
Server		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_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
ClientCertificate		LMW
ClientKey		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 Azure Active Directory.

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 Identity authentication. For user-assigned identity, UID is set to the object ID of the user identity.
ActiveDirectoryServicePrincipal	SQL_AU_AD_SPA	Azure Active Directory 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 won't be used.
Yes	Triggers with the `NOT FOR REPLICATION` option won't fire on the connection.

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 that are 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.

SQL_COPT_SS_ACCESS_TOKEN

Allows the use of an Azure Active Directory access token for authentication. For more information, see Using Azure Active Directory.

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 and above), 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 will be 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