2.2.6.4 LOGIN7
Stream Name:
-
LOGIN7
Stream Function:
Defines the authentication rules for use between client and server.
Stream Comments:
Packet header type 0x10.
The length of a LOGIN7 stream MUST NOT be longer than 128K-1(byte) bytes.
The OffsetLength and Data rules define the variable-length portions of this data stream. The OffsetLength rule lists the offset from the start of the structure, and the length for each parameter. If the parameter is not used, the parameter length field MUST be 0. The data itself (for example, the Data rule) follows these parameters.
The first parameter of the OffsetLength rule (ibHostName) indicates the start of the variable length portion of this data stream. As such it MUST NOT be 0. This is required for forward compatibility (for example, later versions of TDS, with additional parameters, can be successfully skipped by down-level servers).
A sample LOGIN7 message is in section 4.2.
Stream-Specific Rules:
-
Length = DWORD TDSVersion = DWORD PacketSize = DWORD ClientProgVer = DWORD ClientPID = DWORD ConnectionID = DWORD fByteOrder = BIT fChar = BIT fFloat = 2BIT fDumpLoad = BIT fUseDB = BIT fDatabase = BIT fSetLang = BIT OptionFlags1 = fByteOrder fChar fFloat fDumpLoad fUseDB fDatabase fSetLang fLanguage = BIT fODBC = BIT fTranBoundary = BIT ; (removed in TDS 7.2) fCacheConnect = BIT ; (removed in TDS 7.2) fUserType = 3BIT fIntSecurity = BIT OptionFlags2 = fLanguage fODBC (fTranBoundary / FRESERVEDBIT) (fCacheConnect / FRESERVEDBIT) fUserType fIntSecurity fSQLType = 4BIT fOLEDB = BIT ; (introduced in TDS 7.2) fReadOnlyIntent = BIT ; (introduced in TDS 7.4) TypeFlags = fSQLType (FRESERVEDBIT / fOLEDB) (FRESERVEDBIT / fReadOnlyIntent) 2FRESERVEDBIT fChangePassword = BIT ; (introduced in TDS 7.2) fUserInstance = BIT ; (introduced in TDS 7.2) fSendYukonBinaryXML = BIT ; (introduced in TDS 7.2) fUnknownCollationHandling = BIT ; (introduced in TDS 7.3) fExtension = BIT ; (introduced in TDS 7.4) OptionFlags3 = (FRESERVEDBIT / fChangePassword) (FRESERVEDBIT / fSendYukonBinaryXML) (FRESERVEDBIT / fUserInstance) (FRESERVEDBIT / fUnknownCollationHandling) (FRESERVEDBIT / fExtension) 3FRESERVEDBIT ClientTimeZone = LONG; ClientLCID = LCID ColFlags Version ibHostName = USHORT cchHostName = USHORT ibUserName = USHORT cchUserName = USHORT ibPassword = USHORT cchPassword = USHORT ibAppName = USHORT cchAppName = USHORT ibServerName = USHORT cchServerName = USHORT ibUnused = USHORT cbUnused = USHORT ibExtension = USHORT ; (introduced in TDS 7.4) cbExtension = USHORT ; (introduced in TDS 7.4) ibCltIntName = USHORT cchCltIntName = USHORT ibLanguage = USHORT cchLanguage = USHORT ibDatabase = USHORT cchDatabase = USHORT ClientID = 6BYTE ibSSPI = USHORT cbSSPI = USHORT ibAtchDBFile = USHORT cchAtchDBFile = USHORT ibChangePassword = USHORT ; (introduced in TDS 7.2) cchChangePassword = USHORT ; (introduced in TDS 7.2) cbSSPILong = DWORD ; (introduced in TDS 7.2) OffsetLength = ibHostName cchHostName ibUserName cchUserName ibPassword cchPassword ibAppName cchAppName ibServerName cchServerName (ibUnused / ibExtension) (cchUnused / cbExtension) ibCltIntName cchCltIntName ibLanguage cchLanguage ibDatabase cchDatabase ClientID ibSSPI cbSSPI ibAtchDBFile cchAtchDBFile ibChangePassword cchChangePassword cbSSPILong
Note The ClientLCID value is no longer used to set language parameters and is ignored.
All variable-length fields in the login record are optional. This means that the length of the field can be specified as 0. If the length is specified as 0, then the offset MUST be ignored. The only exception is ibHostName, which MUST always point to the beginning of the variable-length data in the login record even in the case where no variable-length data is included.
-
Data = *BYTE FeatureId = BYTE ; (introduced in TDS 7.4) FeatureDataLen = DWORD ; (introduced in TDS 7.4) FeatureData = *BYTE ; (introduced in TDS 7.4) TERMINATOR = %xFF ; signal of end of feature option FeatureOpt = (FeatureId FeatureDataLen FeatureData) / TERMINATOR FeatureExt = 1*FeatureOpt ; (introduced in TDS 7.4)
Stream Definition:
-
LOGIN7 = Length TDSVersion PacketSize ClientProgVer ClientPID ConnectionID OptionFlags1 OptionFlags2 TypeFlags (FRESERVEDBYTE / OptionFlags3) ClientTimeZone ClientLCID OffsetLength Data [FeatureExt]
Stream Parameter Details
|
Parameter |
Description |
|---|---|
|
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 |
|
|
OptionFlags2 |
|
|
TypeFlags |
|
|
OptionFlags3 |
|
|
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:
|
|
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. |
The following table defines the options that are available in FeatureExt.
|
FeatureId |
FeatureData Description |
|---|---|
|
%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:
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:
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: 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: 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: 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: This field describes the cryptographic protocol version that the client understands. The values of this field are as follows:
|
|
%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:
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:
BYTE: The Bit 0 flag specifies whether failover partner login with read-only intent is supported. The values of this BYTE are as follows:
|
|
%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: This field specifies the maximum version number of the DATACLASSIFICATION token that the client can support. This value MUST be one of the following:
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:
BYTE: The Bit 0 flag specifies whether the client supports UTF-8 data. The values of this BYTE are as follows:
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:
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: 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. |
Login Data Validation Rules
cchHostName MUST specify at most 128 Unicode characters.
cchUserName MUST specify at most 128 Unicode characters.
cchPassword MUST specify at most 128 Unicode characters.
cchAppName MUST specify at most 128 Unicode characters.
cchServerName MUST specify at most 128 Unicode characters.
cbExtension MUST NOT exceed 255 bytes.
cchCltIntName MUST specify at most 128 Unicode characters.
cchLanguage MUST specify at most 128 Unicode characters.
cchDatabase MUST specify at most 128 Unicode characters.
cchAtchDBFile MUST specify at most 260 Unicode characters.
cchChangePassword MUST specify at most 128 Unicode characters.
The value at ibUserName—if specified—is semantically enclosed in brackets ([]) and MUST conform to the rules for valid delimited object identifiers. Login MUST fail otherwise.
The value at ibDatabase—if specified—is semantically enclosed in brackets ([]) and MUST conform to the rules for valid delimited object identifiers. Login MUST fail otherwise.
Before submitting a password from the client to the server, for every byte in the password buffer starting with the position pointed to by ibPassword or ibChangePassword, the client SHOULD first swap the four high bits with the four low bits and then do a bit-XOR with 0xA5 (10100101). After reading a submitted password, for every byte in the password buffer starting with the position pointed to by ibPassword or ibChangePassword, the server SHOULD first do a bit-XOR with 0xA5 (10100101) and then swap the four high bits with the four low bits.