2.2.6.4 LOGIN7

Length

The total length of the LOGIN7 structure.

TDSVersion

The highest TDS version being used by the client (for example, 0x00000071 for TDS 7.1). If the TDSVersion value sent by the client is greater than the value that the server recognizes, the server MUST use the highest TDS version that it can use. This provides a mechanism for clients to discover the server TDS by sending a standard LOGIN7 message. If the TDSVersion value sent by the client is lower than the highest TDS version the server recognizes, the server MUST use the TDS version sent by the client.<17>

For information about what the server sends to the client, see the LOGINACK token (section 2.2.7.14).

PacketSize

The packet size being requested by the client.

ClientProgVer

The version of the interface library (for example, ODBC or OLEDB) being used by the client.

ClientPID

The process ID of the client application.

ConnectionID

The connection ID of the primary Server. Used when connecting to an "Always Up" backup server.

OptionFlags1

• Represented in least significant bit order.

• fByteOrder: The byte order used by client for numeric and datetime data types.

  • 0 = ORDER_X86

  • 1 = ORDER_68000<18>

• fChar: The character set used on the client.

  • 0 = CHARSET_ASCII

  • 1 = CHARSET_EBCDIC

• fFloat: The type of floating point representation used by the client.<19>

  • 0 = FLOAT_IEEE_754

  • 1 = FLOAT_VAX

  • 2 = ND5000

• fDumpLoad: Set is dump/load or BCP capabilities are needed by the client.

  • 0 = DUMPLOAD_ON

  • 1 = DUMPLOAD_OFF

• fUseDB: Set if the client requires warning messages on execution of the USE SQL statement. If this flag is not set, the server MUST NOT inform the client when the database changes, and therefore the client is unaware of any accompanying collation changes.

  • 0 = USE_DB_OFF

  • 1 = USE_DB_ON

• fDatabase: Set if the change to initial database needs to succeed if the connection is to succeed.

  • 0 = INIT_DB_WARN

  • 1 = INIT_DB_FATAL

• fSetLang: Set if the client requires warning messages on execution of a language change statement.

  • 0 = SET_LANG_OFF

  • 1 = SET_LANG_ON

OptionFlags2

• Represented in least significant bit order.

• fLanguage: Set if the change to initial language needs to succeed if the connect is to succeed.

  • 0 = INIT_LANG_WARN

  • 1 = INIT_LANG_FATAL

• fODBC: Set if the client is the ODBC driver. This causes the server to set ANSI_DEFAULTS to ON, CURSOR_CLOSE_ON_COMMIT and IMPLICIT_TRANSACTIONS to OFF, TEXTSIZE to 0x7FFFFFFF (2GB) (TDS 7.2 and earlier), TEXTSIZE to infinite (introduced in TDS 7.3), and ROWCOUNT to infinite.<20>

  • 0 = ODBC_OFF

  • 1 = ODBC_ON

• fTranBoundary

• fCacheConnect

• fUserType: The type of user connecting to the server.

  • 0 = USER_NORMAL—regular logins

  • 1 = USER_SERVER—reserved

  • 2 = USER_REMUSER—Distributed Query login

  • 3 = USER_SQLREPL—replication login

• fIntSecurity: The type of security required by the client.

  • 0 = INTEGRATED_SECURTY_OFF

  • 1 = INTEGRATED_SECURITY_ON

TypeFlags

• Represented in least significant bit order.

• fSQLType: The type of SQL the client sends to the server.

  • 0 = SQL_DFLT

  • 1 = SQL_TSQL

• fOLEDB: Set if the client is the OLEDB driver. This causes the server to set ANSI_DEFAULTS to ON, CURSOR_CLOSE_ON_COMMIT and IMPLICIT_TRANSACTIONS to OFF, TEXTSIZE to 0x7FFFFFFF (2GB) (TDS 7.2 and earlier), TEXTSIZE to infinite (introduced in TDS 7.3), and ROWCOUNT to infinite.<21>

  • 0 = OLEDB_OFF

  • 1 = OLEDB_ON

• fReadOnlyIntent: This bit was introduced in TDS 7.4; however, TDS 7.1, 7.2, and 7.3 clients can also use this bit in LOGIN7 to specify that the application intent of the connection is read-only. The server SHOULD ignore this bit if the highest TDS version supported by the server is lower than TDS 7.4.

OptionFlags3

• Represented in least significant bit order.

• fChangePassword: Specifies whether the login request SHOULD change password.

  • 0 = No change request. ibChangePassword MUST be 0.

  • 1 = Request to change login's password.

• fSendYukonBinaryXML: 1 if XML data type instances are returned as binary XML.<22>

• fUserInstance: 1 if client is requesting separate process to be spawned as user instance.

• fUnknownCollationHandling: This bit is used by the server to determine if a client is able to properly handle collations introduced after TDS 7.2. TDS 7.2 and earlier clients are encouraged to use this login packet bit. Servers MUST ignore this bit when it is sent by TDS 7.3 and later clients. See [MSDN-SQLCollation] and [MS-LCID] for the complete list of collations for a database server that supports SQL and LCIDs.

  • 0 = The server MUST restrict the collations sent to a specific set of collations. It MAY disconnect or send an error if some other value is outside the specific collation set. The client MUST properly support all collations within the collation set.

  • 1 = The server MAY send any collation that fits in the storage space. The client MUST be able to both properly support collations and gracefully fail for those it does not support.

• fExtension: Specifies whether ibExtension/cbExtension fields are used.

  • 0 = ibExtension/cbExtension fields are not used. The fields are treated the same as ibUnused/cchUnused.

  • 1 = ibExtension/cbExtension fields are used.

ClientTimeZone

This field is not used and can be set to zero.

ClientLCID

The language code identifier (LCID) value for the client collation. If ClientLCID is specified, the specified collation is set as the session collation. Note that the total ClientLCID is 4 bytes, which implies that there is no support for SQL Sort orders.

OffsetLength

The variable portion of this message. A stream of bytes in the order shown, indicates the offset (from the start of the message) and length of various parameters:

• ibHostname & cchHostName: The client machine name.

• ibUserName & cchUserName: The client user ID.

• ibPassword & cchPassword: The password supplied by the client.

• ibAppName & cchAppName: The client application name.

• ibServerName & cchServerName: The server name.

• ibUnused & cbUnused: These parameters were reserved until TDS 7.4.

• ibExtension & cbExtension: This points to an extension block. Introduced in TDS 7.4 when fExtension is 1. The content pointed by ibExtension is defined as follows:

      ibFeatureExtLong      =  DWORD
      Extension             =  ibFeatureExtLong
  

  ibFeatureExtLong provides the offset (from the start of the message) of FeatureExt block. ibFeatureExtLong MUST be 0 if FeatureExt block does not exist.

  Extension block can be extended in future. The client MUST NOT send more data than needed. The server SHOULD ignore any appended data that is unknown to the server.

• ibCltIntName & cchCltIntName: The interface library name (ODBC or OLEDB).

• ibLanguage & cchLanguage: The initial language (overrides the user ID's default language).

• ibDatabase & cchDatabase: The initial database (overrides the user ID's default database).

• ClientID: The unique client ID (created by using the NIC address). ClientID is the MAC address of the physical network layer. It is used to identify the client that is connecting to the server. This value is mainly informational, and no processing steps on the server side use it.

• ibSSPI & cbSSPI: SSPI data.

  If cbSSPI < USHORT_MAX, then this length MUST be used for SSPI and cbSSPILong MUST be ignored.

  If cbSSPI == USHORT_MAX, then cbSSPILong MUST be checked.

  If cbSSPILong > 0, then that value MUST be used. If cbSSPILong ==0, then cbSSPI (USHORT_MAX) MUST be used.

• ibAtchDBFile & cchAtchDBFile: The file name for a database that is to be attached during the connection process.

• ibChangePassword & cchChangePassword: New password for the specified login. Introduced in TDS 7.2.

• cbSSPILong: Used for large SSPI data when cbSSPI==USHORT_MAX.
  Introduced in TDS 7.2.

Data

The actual variable-length data portion referred to by OffsetLength.

FeatureId

The unique identifier number of a feature. The available features are described in the following table.

Introduced in TDS 7.4.

FeatureDataLen

The length, in bytes, of FeatureData for the corresponding FeatureId.

Introduced in TDS 7.4.

FeatureData

Data of the feature. Each feature defines its own data format.

The data for existing features are defined in the following table.

Introduced in TDS 7.4.

FeatureExt

The data block that can be used to inform and/or negotiate features between client and server. It contains data for one or more optional features. Each feature is assigned an identifier, followed by data length and data. The data for each feature is defined by the feature’s own logic. If the server does not support the specific feature, it MUST skip the feature data and jump to next feature. If needed, each feature SHOULD have its own logic to detect whether the server accepts the feature option.

Optionally, a feature can use a FEATUREEXTACK token (section 2.2.7.11) to acknowledge the feature along with LOGINACK. The detailed acknowledge data SHOULD be defined by the feature itself.

Introduced in TDS 7.4.

%0x01

(SESSIONRECOVERY)

(introduced in TDS 7.4)

Session Recovery feature. This feature is used to recover the session state of a previous connection. Content is defined as follows:

 Length                    =    DWORD
 RecoveryDatabase          =    B_VARCHAR
 RecoveryCollation         =    BYTELEN [COLLATION]
 RecoveryLanguage          =    B_VARCHAR
  
 SessionRecoveryData       =   Length
                               RecoveryDatabase 
                               RecoveryCollation
                               RecoveryLanguage
                               SessionStateDataSet
  
 InitSessionRecoveryData   =   SessionRecoveryData
 SessionRecoveryDataToBe   =   SessionRecoveryData
  
  
 FeatureData      = [InitSessionRecoveryData SessionRecoveryDataToBe]

The Length field is the length, in bytes, of SessionRecoveryData excluding the Length field itself. SessionStateDataSet is described in section 2.2.7.21. The length of SessionStateDataSet can be derived from the Length field and the length of RecoveryDatabase, RecoveryCollation, and RecoveryLanguage. The maximum length for RecoveryDatabase and RecoveryLanguage is 128 Unicode characters.

There are two sets of SessionRecoveryData. The data for the first set, InitSessionRecoveryData, SHOULD come from the initial login response data of the initial connection to be recovered, specifically, the Database/Collation/Language ENVCHANGE data and SessionStateDataSet in FeatureExtAck.

Data for the second set, SessionRecoveryDataToBe, SHOULD come from the latest ENVCHANGE for Database/Collation/Language from the connection to be recovered and the latest data for each StateId in SessionStateData from the connection to be recovered. If login succeeded on this recovery connection, the session state of the connection MUST be set to SessionRecoveryDataToBe. To save space, if data for RecoveryDatabase/RecoveryCollation/RecoveryLanguage in SessionRecoveryDataToBe is the same as data in InitSessionRecoveryData, the length value of each field SHOULD be 0. If data for any session StateId is unchanged from InitSessionRecoveryData, the corresponding StateId data SHOULD be skipped in SessionRecoveryDataToBe.

When this feature option is received and the server supports connection recovery, a FEATUREEXTACK token that contains data for SESSIONRECOVERY feature MUST be returned along with LOGINACK in the login response to indicate that the server supports the feature. If SESSIONRECOVERY is not acknowledged in the login response, the server does not support the feature and the client MUST disable the feature for this connection.

The client can request this feature option with zero FeatureDataLen. This is used during login for the initial connection to indicate that the client prefers this feature.

When the client sends this feature option with non-zero FeatureDataLen during login, the option data SHOULD come from a previous connection. The TDS version in the login request MUST be the same as the TDS version negotiated for the connection to be recovered. The server MUST return the same TDS version in the login response, and if not, the client MUST disconnect the connection and raise an error to the upper layer.

If a login record with non-zero FeatureDataLen of this feature is received and the server supports this feature, the server MUST:

• Force TDS version negotiation to use the TDS version requested by the client, and fail the login if the requested TDS version is not known to the server, for example, a TDS version that is later than the highest one on the server.

• Validate the content in SessionRecoveryData, and fail the login if any data is invalid or any unknown session state exists.

After the feature is negotiated to be enabled, the server SHOULD send session state updates to the client via a SESSIONSTATE token during the lifetime of the connection. The client MUST track the initial session state data and the latest session state data. Session state data is updated via a SESSIONSTATE token incrementally.

When a client requests RESETCONNECTION/RESETCONNECTIONSKIPTRAN and the server acknowledges the request, both client and server MUST update the baseline of the session state data to be the same as the initial state as defined by InitSessionRecoveryData, and any further state update SHOULD be on top of the initial state.

Session state data can be used to recover a dead connection as defined by SessionRecoveryData. The client SHOULD try to recover a dead connection if the latest fRecovery bit is TRUE for all StateId that were received from the server. The client MUST NOT try to recover a dead connection if the any latest fRecovery bit is FALSE.

%0x02

(FEDAUTH)<23>

(introduced in TDS 7.4)

The presence of the FEDAUTH FeatureExt indicates that the client is authenticating by federated authentication. If the FEDAUTH FeatureId is present, the value of fIntSecurity MUST be 0. The format of the data is as described below based on the bFedAuthLibrary that is used.

bFedAuthLibrary = 0x7F is a reserved value.

When the bFedAuthLibrary is Live ID Compact Token, the format is as follows:

 bFedAuthLibrary       = 7BIT
 fFedAuthEcho          = BIT
 Options               = bFedAuthLibrary
                         fFedAuthEcho
  
 FedAuthToken          = L_VARBYTE
  
 Nonce                 = 32BYTE
 ChannelBindingToken   = BYTESTREAM
 Signature             = 32BYTE
  
 SignedData            = Nonce
                         [ChannelBindingToken]
                         Signature
  
 FeatureData           = Options
                         FedAuthToken
                         SignedData

bFedAuthLibrary: 7 bits, collectively treated as a 7-bit unsigned integer, indicating the library that is used by the client for federated authentication.

0x00 = Live ID Compact Token. The format of the Live ID Compact Token and the way in which the Live ID Compact Token is obtained are out of the scope of this document.

fFedAuthEcho: The intention of this flag is for the client to echo the server’s FEDAUTHREQUIRED prelogin option, so that the server can validate that the response was not tampered with. The client MUST assign this flag to 1 if and only if the server’s PRELOGIN response contained a FEDAUTHREQUIRED option with a B_FEDAUTHREQUIRED value of 0x01.

FedAuthToken: The binary authentication token generated by the specified federated authentication library. The length of FedAuthToken MUST NOT be 0.

Nonce: The nonce provided by the server during the pre-login exchange, echoed back to the server by the client.

ChannelBindingToken: This optional field MAY be omitted, but if encryption is being used for the lifetime of the TDS connection and the client is able to generate a channel binding token, the field SHOULD be included in the payload. When present, ChannelBindingToken contains the channel binding token associated with the underlying SSL stream.

Signature: The HMAC-SHA-256 [RFC6234] hash of the server-specified nonce and, if it is present in the FeatureData, the ChannelBindingToken, is generated by using the session key retrieved from the federated authentication context as the shared secret.

The length of the ChannelBindingToken field is not explicitly conveyed in the protocol but can be determined by comparing the FeatureDataLen against the length of the remainder of the feature data, which is explicitly transmitted in the protocol.

When bFedAuthLibrary is Security Token, the format is as follows:

 bFedAuthLibrary       = 7BIT
 fFedAuthEcho          = BIT
 Options               = bFedAuthLibrary
                         fFedAuthEcho
  
 FedAuthToken          = L_VARBYTE
  
 Nonce                 = 32BYTE
  
 OtherData             = Nonce
  
 FeatureData           = Options
                         FedAuthToken
                         [OtherData]

bFedAuthLibrary: 7 bits, collectively treated as a 7-bit unsigned integer, indicating the library that is used by the client for federated authentication.

0x01 = Security Token. The format of the token and the way in which this token is obtained are out of the scope of this document.

fFedAuthEcho: The intention of this flag is for the client to echo the server’s FEDAUTHREQUIRED prelogin option, so that the server can validate that the response was not tampered with. The client MUST assign this flag to 1 if and only if the server’s PRELOGIN response contained a FEDAUTHREQUIRED option with a B_FEDAUTHREQUIRED value of 0x01.

FedAuthToken: The binary authentication token generated by the specified federated authentication library. The length of FedAuthToken MUST NOT be 0.

Nonce: The nonce provided by the server during the Prelogin exchange and echoed back to the server by the client. This field MUST be present if the server’s PRELOGIN message included a NONCE field. Otherwise, this field MUST NOT be present.

When bFedAuthLibrary is Azure Active Directory Authentication Library (ADAL) [that is, 0x02], the format is as follows:

 bFedAuthLibrary       = 7BIT
 fFedAuthEcho          = BIT
 Workflow              = BYTE
 Options               = bFedAuthLibrary
                         fFedAuthEcho
                         Workflow

bFedAuthLibrary: 7 bits, collectively treated as a 7-bit unsigned integer that indicates the library that is used by the client for federated authentication.

0x02 = ADAL. After the client establishes the intent to use ADAL, for which additional information is required by the client to generate a token, the server MUST respond with the Federated Authentication Information token to the client with "FedAuthInfoIDs: STSURL, SPN".

fFedAuthEcho: The intention of this flag is for the client to echo the server’s FEDAUTHREQUIRED pre-login option so that the server can validate that the response was not tampered with. The client MUST assign this flag to 1 if and only if the server’s PRELOGIN Response contains a FEDAUTHREQUIRED option with a B_FEDAUTHREQUIRED value of 0x01.

Workflow: Indicates the ADAL (that is, 0x02) workflow that is being used.

0x01 = Username/password. A username and password are passed to ADAL to retrieve a token.

0x02 = Integrated. A Windows identity is passed to ADAL to retrieve a token.

All other values of bFedAuthLibrary are reserved.

%0x04

(COLUMNENCRYPTION)

(introduced in TDS 7.4)

The presence of the COLUMNENCRYPTION FeatureExt indicates that the client SHOULD<24> be capable of performing cryptographic operations on data. The feature data are described as follows:

 COLUMNENCRYPTION_VERSION        = BYTE
  
 FeatureData                     = COLUMNENCRYPTION_VERSION

COLUMNENCRYPTION_VERSION: This field describes the cryptographic protocol version that the client understands. The values of this field are as follows:

• 1 = The client supports column encryption without enclave computations.

• 2 = The client SHOULD<25> support column encryption when encrypted data require enclave computations.

• 3 = The client SHOULD<26> support column encryption when encrypted data require enclave computations with the additional ability to cache column encryption keys that are to be sent to the enclave and the ability to retry queries when the keys sent by the client do not match what is needed for the query to run.

%0x05

(GLOBALTRANSACTIONS)<27>

(introduced in TDS 7.4)

The presence of the GLOBALTRANSACTIONS FeatureExt indicates that the client is capable of performing Global Transactions. The feature data is described as follows:

 FeatureData                     = NO DATA

NO DATA: No feature data is sent with the GLOBALTRANSACTIONS FeatureExt.

%0x08

(AZURESQLSUPPORT)

(introduced in TDS 7.4)

The presence of the AZURESQLSUPPORT FeatureExt indicates whether the client MAY<28> support failover partner login with read-only intent in Azure SQL Database. For information about failover partner, see [MSDOCS-DBMirror].

The feature data is described as follows:

 FeatureData                     = BYTE

BYTE: The Bit 0 flag specifies whether failover partner login with read-only intent is supported. The values of this BYTE are as follows:

• 0 = The server does not support the AZURESQLSUPPORT feature extension.

• 1 = The server supports the AZURESQLSUPPORT feature extension.

%0x09

(DATACLASSIFICATION)

(introduced in TDS 7.4)

The DATACLASSIFICATION FeatureExt SHOULD<29> indicate that the client is capable of accepting data classification information about a query result set. The feature data is described as follows:

 DATACLASSIFICATION_VERSION      = BYTE
  
 FeatureData                     = DATACLASSIFICATION_VERSION
                                   VersionSpecificData

DATACLASSIFICATION_VERSION: This field specifies the maximum version number of the DATACLASSIFICATION token that the client can support. This value MUST be one of the following:

• 1 = The server does not send sensitivity-rank data as part of the DATACLASSIFICATION token.

• 2 = The server sends sensitivity-rank data as part of the DATACLASSIFICATION token.

VersionSpecificData: This field specifies the version-specific data that is required for the DATACLASSIFICATION feature extension request. The values of this field are as follows.

When the value of the DATACLASSIFICATION_VERSION field is 1 or 2, there is no version-specific data.

%0x0A

(UTF8_SUPPORT)

(introduced in TDS 7.4)

The presence of the UTF8_SUPPORT FeatureExt indicates whether the client’s ability to send and receive UTF-8 encoded data SHOULD<30> be supported. The feature data is described as follows:

 FeatureData                       = BYTE

BYTE: The Bit 0 flag specifies whether the client supports UTF-8 data. The values of this BYTE are as follows:

• 0 = The client does not support UTF-8 encoded data.

• 1 = The client supports UTF-8 encoded data.

Failure of the client to receive an acknowledgement of UTF-8 feature extension support from the server indicates that the server cannot send or receive UTF-8 encoded data.

%0x0B

(AZURESQLDNSCACHING)<31>

(introduced in TDS 7.4)

The presence of the AZURESQLDNSCACHING FeatureExt indicates whether the client has the ability to store the mapping between the TDS server endpoint’s application domain, identified by its fully qualified domain name (FQDN), and the equivalent IP address in the client’s application cache.

The feature data is described as follows:

 FeatureData                     = NO DATA

NO DATA: No feature data is sent with the AZURESQLDNSCACHING FeatureExt. The presence of this FeatureExt token indicates to the server that the client can support the feature.

%0x0D (JSONSUPPORT) (introduced in TDS 7.4)

The presence of JSONSUPPORT FeatureExt indicates whether the client has the ability to send and receive JSON datatype SHOULD<32> be supported. The feature data is described as follows:

    JSONSUPPORT_VERSION   = BYTE 
    FeatureData                    = JSONSUPPORT_VERSION

JSONSUPPORT_VERSION: This field specifies the version number of the json datatype that is to be used for this connection. This value is 1. 

%xFF

(TERMINATOR)

This option signals the end of the FeatureExt feature and MUST be the feature’s last option.