5 Appendix A: Product Behavior

  • Article

The information in this specification is applicable to the following Microsoft products or supplemental software. References to product versions include updates to those products.

  • 2007 Microsoft Office system

  • Microsoft Office 2010 system

  • Microsoft Office 2013 system

  • Microsoft Office 2016

  • Microsoft Office 2019

  • Microsoft Office 2021

  • Microsoft SQL Server 2005

  • Microsoft SQL Server 2008

  • Microsoft SQL Server 2008 R2

  • Microsoft SQL Server 2012

  • Microsoft SQL Server 2014

  • Microsoft SQL Server 2016

  • Microsoft SQL Server 2017

  • Microsoft SQL Server 2019

  • Microsoft SQL Server 2022

  • Windows Vista operating system

  • Windows Server 2008 operating system

  • Windows 7 operating system

  • Windows Server 2008 R2 operating system

  • Windows 8 operating system

  • Windows Server 2012 operating system

  • Windows 8.1 operating system

  • Windows Server 2012 R2 operating system

  • Windows 10 operating system

  • Windows Server 2016 operating system

  • Windows Server operating system

  • Windows Server 2019 operating system

  • Windows Server 2022 operating system

  • Windows 11 operating system

Exceptions, if any, are noted in this section. If an update version, service pack or Knowledge Base (KB) number appears with a product name, the behavior changed in that update. The new behavior also applies to subsequent updates unless otherwise specified. If a product edition appears with the product version, behavior is different in that product edition.

Unless otherwise specified, any statement of optional behavior in this specification that is prescribed using the terms "SHOULD" or "SHOULD NOT" implies product behavior in accordance with the SHOULD or SHOULD NOT prescription. Unless otherwise specified, the term "MAY" implies that the product does not follow the prescription.

<1> Section 2.2: The SqlConnectionString structure is the connection string that is used by the SQL Server ODBC Driver and is an implementation of ODBCConnectionString that puts additional restrictions on the generic keys and defines driver-specific keys.

SqlConnectionString Additional Restrictions

The additional restrictions on the generic keys that apply to SqlConnectionString are the following:

  • The length of a value of a generic key or a driver-specific key has to be less than or equal to 260 characters. Values longer than 260 characters have to be truncated.

  • If there are multiple occurrences of a single driver-specific key, the value from the first KeyValuePair takes precedence. This is opposite of the rule for generic keys that is specified in section 2.2.3.

  • If the DSN key precedes the FileDSN and Driver keys, the connection information is obtained from the settings as specified by the value of the DSN key. This protocol can be used after the DSN has been created and exists in the system. If there is no DSN key in the system, it is equivalent to having no DSN key specified. KeyValuePairs of driver-specific keys that are specified in the connection string have to take precedence over the KeyValuePairs specified in the DSN.

  • Both the key and value of generic keys and driver-specific keys are case-insensitive, with the exception of the value of the PWD key.

  • There are no default values for driver-specific keys if the KeyValuePair is missing, unless otherwise specified.

Driver-Specific Keys

Only the following driver-specific keys are valid in the SqlConnectionString structure. Any other driver-specific KeyValuePairs have to be ignored. The table lists the driver-specific keys that are accepted by SqlConnectionString and a brief description of each.

Key

Meaning

Addr

Synonym for the key Address.

Address

Specifies the network address of an instance of the database server.

If the value of the Address key is not specified, the default value is the value of the Server key.

Address has to be one of the following two formats:

  • TCP Format

    tcp:<host name>\<instance name>

    tcp:<host name>,<TCP/IP port number>

    The TCP format has to start with the prefix "tcp:" and is followed by the database instance, specified by a <host name> and an <instance name>.

    The <host name> has to be specified in one of three ways:

    The <instance name> is used to resolve to a particular TCP/IP port number [RFC793], on which a database instance is hosted. Alternatively, specifying a <TCP/IP port number> directly is also allowed. If both <instance name> and <port number> are not present, the default database instance is used. For more information about instance name, see [MSDN-UNI].

  • NP Format

    np:\\<host name>\pipe\<pipe name>

    The NP format has to start with the prefix "np:" and is followed by a named pipe name.

    The <host name> has to be specified in one of three ways:

    • NetBIOSName [RFC1002]

    • IPv4Address [RFC791]

    • IPv6Address [RFC2460]

    The <pipe name> is used to identify the database instance to which to be connected.

Only when the value of the Network key is specified as "DBNETLIB" can the protocol prefixes "tcp:" and "np:" be used. If any other value of the Network key is specified, the prefixes "tcp:" and "np:" cannot be used.

For more information about the format of the Address key, see [MSKB-313295].

For more information about named pipes, see [MSDN-NP].

AnsiNPW

Specifies whether American National Standards Institute (ANSI)-defined behavior is used for handling NULL comparisons, character data padding, warnings, and NULL concatenation as specified in [ISO/IEC9075-3:2008]. The valid values are "Yes" and "No". If the value "Yes" is not specified, the value "No" is used.

If the AnsiNPW key is not specified in the connection string, the value has to be obtained from the contents of the settings of the File DSN. If the key (AnsiNPW) is not specified in the contents of a File DSN or the given File DSN does not exist, the default value is "Yes".

APP

Sets the application identifier as specified by the ibAppName and cchAppName fields in [MS-TDS] section 2.2.6.4. The default value is the name of the current ODBC application.

AttachDBFileName

Sets the name of the primary file of an attachable database as specified by the ibAtchDBFile and cchAtchDBFile fields in [MS-TDS] section 2.2.6.4. The file path has to conform to the format specified in [MSDN-FILE].

If the value of the AttachDBFileName key is specified in the connection string, the database is attached and becomes the default database for the connection.

If the Database key is not specified and if the database was previously attached it will not be reattached. The previously attached database will be used as the default database for the connection.

If the Database key is specified together with the AttachDBFileName key, the value of the Database key is used as the alias. But if the name is already used in the other attached database, the connection has to fail.

If the AttachDBFileName key is not specified in the connection string, the value has to be obtained from the contents of the settings of the File DSN. If the key (AttachDBFileName) is not specified in the contents of a File DSN or the given File DSN does not exist, the default value is an empty string.

For more information about attachable databases, see [MSDN-DAD].

For more information about default databases, see [MSDN-SD].

AutoTranslate

Specifies whether original equipment manufacturer (OEM) characters or characters in the ANSI character set are translated between the code page of the client and the code page of the database when characters are retrieved from, or sent to, the database. The valid values are "Yes" or "No". If the value "Yes" is not specified, the value "No" is used.

If the AutoTranslate key is not specified in the connection string, the value has to be obtained from the contents of the settings of the File DSN. If the key (AutoTranslate) is not specified in the contents of a File DSN or the given File DSN does not exist, the default value is "Yes".

In Microsoft implementations, if the value is "Yes", OEM/ANSI character translation is enabled. Otherwise, OEM/ANSI character translation is not performed. When AutoTranslate is set to "Yes", the ODBC driver uses Unicode to convert data moved between character variables on the client and character columns, variables, or parameters in a SQL Server database.

When data is sent from a character variable on the client to a character column, variable, or parameter in a SQL Server database, the ODBC driver first converts from SQL_C_CHAR to Unicode by using the active code page of the client, then from Unicode back to character by using the code page of the server. When data is sent from a character column, variable, or parameter in a SQL Server database to a character variable on the client, the ODBC driver first converts from character to Unicode by using the code page of the server, then from Unicode back to character by using the active code page of the client.

Because all of these conversions are done by the ODBC driver executing on the client, the server code page needs to be one of the code pages installed on the client computer.

The AutoTranslate setting has no effect on these conversions: moving data between character client variables and Unicode columns, variables, or parameters in SQL Server databases, and moving data between Unicode client variables and character columns, variables, or parameters in SQL Server databases.

ClientCertificate

Specifies the certificate to be used for client authentication.

Supported certificate file formats are the following:

  • DER (Distinguished Encoding Rules)

  • PFX (Personal Information Exchange)

  • PEM (Privacy Enhanced Mail)

For certificates in DER and PEM formats, an accompanying ClientKey MUST specify the location of the certificate's corresponding private key file. If file location is used for the authentication of a certificate in PFX format, an optional password can be specified if the private key inside the certificate is encrypted.

The syntax options available for client-certificate authentication are the following:

  • ClientCertificate=sha1:<hash_value>

    The ODBC driver uses a SHA1 hash value to locate a certificate in the Windows certificate store.

  • ClientCertificate=subject:<subject>

    The ODBC driver uses a subject value to locate a certificate in the Windows certificate store.

  • ClientCertificate=file:<path_to_cert_file>

    The ODBC driver uses a file location to locate a certificate. This option is applicable to certificates when the private key is unencrypted.

  • ClientCertificate=file:<path_to_cert_file>,password:<password>

    The ODBC driver uses a file location to locate a certificate and a password to decrypt the private key inside the certificate. This option is applicable to PFX-formatted certificates only.

    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".

Note  The ClientCertificate key is not supported by SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, and SQL Server 2017.

ClientKey

Specifies the file location of the private key that is used with the DER- or PEM-formatted certificate that is specified by ClientCertificate. Along with the location of the key file, an optional password can be specified if the key is encrypted.

Supported private key file formats are the following:

  • PVK (Private Key)

  • PEM (Privacy Enhanced Mail)

The syntax options available for using the private key are the following:

  • ClientKey=file:<path_to_key_file>

    The ODBC driver uses a file location to locate the unencrypted private key.

  • ClientKey =file:<path_to_cert_file>,password:<password>

    The ODBC driver uses a file location to locate the encrypted key and a password to decrypt the key.

    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".

Note  The ClientKey key is not supported by SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, and SQL Server 2017.

Database

Sets the name of the initial or default database of a data source as specified by the ibDatabase and cchDatabase fields in [MS-TDS] section 2.2.6.4.

If the Database key is not specified in the connection string, the value has to be obtained from the contents of the settings of the File DSN. If the key (Database) is not specified in the contents of a File DSN or the given File DSN does not exist, the default value is "(default)," which means the default database.

For more information about default databases, see [MSDN-SD].

Encrypt

Specifies whether encryption is used as specified by [MS-TDS] section 2.2.6.5. The valid values are "Yes" and "No". If the value "Yes" is not specified, the value "No" is used.

If the Encrypt key is not specified in the connection string, the value has to be obtained from the contents of the settings of the File DSN. If the key (Encrypt) is not specified in the contents of a File DSN or the given File DSN does not exist, the default value is "No".

Language

Sets the language used for database server warning or error messages as specified by the ibLanguage and cchLanguage fields in [MS-TDS] section 2.2.6.4.

If the Language key is not specified in the connection string, the value has to be obtained from the contents of the settings of the File DSN. If the key (Language) is not specified in the contents of a File DSN or the given File DSN does not exist, the default value is "(default)", which means the default language.

If the language specified by the value of the Language key is not installed on the server, the key is ignored by the server, and error or warning messages will be sent to the ODBC application in English.

For more information about default language, see [MSDN-DLO].

Net

Synonym for the Network key.

Network

Specifies the network component that is used in communication between the client and the data source. The behavior is platform-dependent.

If the Network key is specified and both the Server and Address keys are present, the value of the Address key takes precedence over the value of the Server key.

If the Network key is not specified and both the Server and Address keys are present, the value of the Server key takes precedence over the value of the Address key.

For Microsoft implementations, the values and their behaviors for the various components are described here.

  • DBNMPNTW -- The component DBNMPNTW implements the named pipes protocol [MSDN-NP].

  • DBMSSOCN -- The component DBMSSOCN implements the TCP/IP protocol.

  • DBMSSPXN -- The component DBMSSPXN implements the NWLink IPX/SPX protocol.

  • DBMSRPCN -- The component DBMSRPCN implements the Multi-Protocol protocol.

  • DBMSVINN -- The component DBMSVINN implements the Banyan Vines protocol.

  • DBMSADSN -- The component DBMSADSN implements the ADSP protocol.

  • DBMSSHRN -- The component DBMSSHRN implements the Shared Memory protocol.

  • DBMSLPCN -- The component DBMSLPCN implements the Shared Memory protocol.

  • DBNETLIB -- The default search order of network component can be used.

This key does not have a default value. If the value that is specified is not listed above or if the Network key is not specified, the default search order of network component can be used. For more information about the default search order of network component, see [MSKB-328383].

QueryLog_On

Specifies whether query profiling of the data source is enabled. The valid values are "Yes" or "1" which are equivalent, or "No". If the value "Yes" or "1" is not specified, the value "No" is used.

If the QueryLog_On key is not specified in the connection string, the value has to be obtained from the contents of the settings of the File DSN. If the key (QueryLog_On) is not specified in the contents of a File DSN or the given File DSN does not exist, the default value is "No".

QuotedId

Specifies whether SQL-92 rules are used regarding the use of quotation marks in SQL statements as specified in [ISO/IEC9075-3:2008]. The valid values are "Yes" or "No". If the value "Yes" is not specified, the value "No" is used.

If the QuotedId key is not specified in the connection string, the value has to be obtained from the contents of the settings of the File DSN. If the key (QuotedID) is not specified in the contents of a File DSN or the given File DSN does not exist, the default value is "Yes".

For more information about legacy Transact-SQL rules used prior to SQL-92, see [MSDN-UOMSS].

Regional

Specifies whether client locale settings are used when converting currency, date, and time data to character data. In Microsoft implementations, client locale settings are stored in the registry.

The valid values are "Yes" or "No". If the value "Yes" is not specified, the value "No" is used.

If the value is "No", ODBC standard strings have to be used to represent currency, date, and time data.

The Regional key applies only on result set data and cannot be applied on parameter data.

The Regional key does not control the conversion from character data into currency, date, and time data, in which ODBC standard string has to be used.

If the Regional key is not specified in the connection string, the value has to be obtained from the contents of the settings of the File DSN. If the key (Regional) is not specified in the contents of a File DSN or the given File DSN does not exist, the default value is "No".

For more information about ODBC standard strings, see [MSDN-UDTD-ODTF].

Server

Specifies the name of a database server to which to connect. The value of the Server key can be either the name of a server on the network or the name of a SQL Server Configuration Manager advanced server entry.

If the Server key is not specified in the connection string, the value has to be obtained from the contents of the settings of the File DSN. If the key (Server) is not specified in the contents of a File DSN or the given File DSN does not exist, the default value is the Value of the DSN key. If the Driver key is present, the Server key also has to be present.

When multiple instances of Microsoft SQL Server are running on the same computer, the server name can be specified as ServerName\InstanceName. "(local)" is also a valid name that represents a copy of SQL Server that is running on the same computer. If InstanceName is omitted, the ODBC driver connects to the default database instance. Which database instance is the default is defined when SQL Server is installed on the machine.

StatsLog_On

Specifies whether driver performance statistics are captured. The valid values are "Yes" or "1" which are equivalent, or "No". If the value "Yes" or "1" is not specified, the value "No" is used.

If the StatsLog_On key is not specified in the connection string, the value has to be obtained from the contents of the settings of the File DSN. If the key (StatsLog_On) is not specified in the contents of a File DSN or the given File DSN does not exist, the default value is "No".

Trusted_Connection

Specifies whether a user connects through a user account by using either Kerberos [RFC4120] or another platform-specific authentication as specified by the fIntSecurity field (for details, see [MS-TDS] section 2.2.6.4).

The valid values are "Yes", "1", or empty string, which are equivalent, or "No". If the value "No" is not specified, the value "Yes" is used.

If the value is "No", the UID and PWD keys have to be used to establish a connection with the data source.

If the DSN key and the UID key are not included in the connection string or if the value of the UID key is an empty string, the value of the Trusted_Connection key has to be "Yes". If the Trusted_Connection key is not specified in the connection string, the value has to be obtained from the contents of the settings of the File DSN. If the key (Trusted_Connection) is not specified in the contents of a File DSN or the given File DSN does not exist, the default value is "No".

If the value of the Trusted_Connection key is "Yes", both the UID and PWD keys are ignored. Otherwise, the UID key has to be specified.

In Microsoft implementations, this user account is a Windows user account and NTLM authentication [MSDN-NTLM] is used when the value of the Trusted_Connection key is "Yes".

WSID

Sets the workstation identifier as specified by the ibHostName and cchHostName fields in [MS-TDS] section 2.2.6.4. The default value is the name of the workstation that is running the ODBC application.

<2> Section 3.1: In Microsoft implementations, this user account is a Windows user account.