Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

2.2.6.3 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
                     (fTransBoundary / 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

ClientTimZone    =   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

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
FeatureDataLen   =   DWORD
FeatureData      =   *BYTE

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)
                     ClientTimZone
                     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.<8>

For information about what the server sends to the client, see the LOGINACK token.

PacketSize

The desired 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

  • fChar: The character set used on the client.

    • 0 = CHARSET_ASCII

    • 1 = CHARSET_EBDDIC

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

    • 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 desires 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 will be 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 desires 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, 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.

    • 0 = ODBC_OFF

    • 1 = ODBC_ON

  • fTransBoundary

  • 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, 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.

    • 0 = OLEDB_OFF

    • 1 = OLEDB_ON

  • fReadOnlyIntent: This bit is 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.

  • 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 or 7.4 clients. See [MSDN-SQLCollation] and [MS-LCID] documents 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

The time zone of the client machine.

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 used NIC address).

  • ibSSPI & cbSSPI: SSPI data.

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

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

    If cbSSPILong > 0, then that value MUST be used. If cbSSPILong ==0, then cbSSPI (USHRT_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==USHRT_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. See detailed description in the table below.

FeatureDataLen

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

FeatureData

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

Data for existing features are defined in the table below.

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 if the server accepts the feature option.

Optionally, a feature can use FeatureExtAck token to acknowledge the feature along with LOGINACK. The detailed acknowledge data SHOULD be defined by the feature itself.

FeatureExt Feature Option and Description

FeatureId

FeatureData Description

%0x01

(SESSIONRECOVERY)

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.19. 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 was 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 currently on the server.

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

Once 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 as well as 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 the client and the 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 received from the server. The client MUST NOT try to recover a dead connection if the any latest fRecovery bit is FALSE.

%0x02

(FEDAUTH)

Presence of the FEDAUTH FeatureExt indicates that the client is authenticating by federated authentication. If this 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.

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.

fFederatedAuthEcho: The intention of this flag is for the client to echo the server’s FEDAUTHREQUIRED prelogin option, so that the server can validate that it was not tampered with. When the FederatedAuth field is present, 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, 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 the 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.

fFederatedAuthEcho: The intention of this flag is for the client to echo the server’s FEDAUTHREQUIRED prelogin option, so that the server can validate that it was not tampered with. When the FederatedAuth field is present, 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, echoed back to the server by the client. This field MUST be present if the server’s PRELOGIN message included NONCE field. Otherwise, this field MUST NOT be present.

All other values of bFedAuthLibrary are reserved.

%xFF

(TERMINATOR)

This is the last option in FeatureExt.

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, 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, the server SHOULD first do a bit-XOR with 0xA5 (10100101) and then swap the four high bits with the four low bits.

 
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.