Revision Summary Date Revision History Revision Class Comments 3/14/2008 0.1 Major Initial Availability. 6/20/2008 0.1.1 Editorial Changed language and formatting in the technical content. 7/25/2008 0.1.2 Editorial Changed language and formatting in the technical content. 8/29/2008 0.1.3 Editorial Changed language and formatting in the technical content. 10/24/2008 0.1.4 Editorial Changed language and formatting in the technical content. 12/5/2008 0.2 Minor Clarified the meaning of the technical content. 1/16/2009 0.3 Minor Clarified the meaning of the technical content. 2/27/2009 0.4 Minor Clarified the meaning of the technical content. 4/10/2009 0.5 Clarified the meaning of the technical content.
5/22/2009 0.5.1 Editorial Changed language and formatting in the technical content. 7/2/2009 1.0 Updated and revised the technical content. 8/14/2009 1.1 Minor Clarified the meaning of the technical content. 9/25/2009 2.0 Major Updated and revised the technical content. 11/6/2009 3.0 Updated and revised the technical content. 12/18/2009 4.0 Major Updated and revised the technical content. 1/29/2010 4.1 Minor Clarified the meaning of the technical content. 3/12/2010 5.0 Major Updated and revised the technical content. 4/23/2010 6.0 Major Updated and revised the technical content. 6/4/2010 7.0 Updated and revised the technical content.
7/16/2010 8.0 Major Updated and revised the technical content. 8/27/2010 8.0 None No changes to the meaning, language, or formatting of the technical content. 10/8/2010 9.0 Major Updated and revised the technical content. 11/19/2010 9.0 None No changes to the meaning, language, or formatting of the technical content. 1/7/2011 9.1 Minor Clarified the meaning of the technical content. 2/11/2011 9.2 Minor Clarified the meaning of the technical content. 3/25/2011 9.3 Minor Clarified the meaning of the technical content. 5/6/2011 9.4 Minor Clarified the meaning of the technical content. 6/17/2011 10.0 Major Updated and revised the technical content.
9/23/2011 11.0 Updated and revised the technical content. 12/16/2011 12.0 Major Updated and revised the technical content. 3/30/2012 12.1 Minor Clarified the meaning of the technical content. 7/12/2012 12.2 Minor Clarified the meaning of the technical content. 10/25/2012 12.2 None No changes to the meaning, language, or formatting of the technical content. 1/31/2013 13.0 Major Updated and revised the technical content. 8/8/2013 14.0 Major Updated and revised the technical content. 11/14/2013 15.0 Major Updated and revised the technical content. 2/13/2014 16.0 Major Updated and revised the technical content. 5/15/2014 17.0 Major Updated and revised the
16: Transaction Manager Address<36> 17: Transaction ended 18: RESETCONNECTION/RESETCONNECTIONSKIPTRAN Completion Acknowledgement 19: Sends back name of user instance started per login request 20: Sends routing information to client Type Old Value New Value 1: Database OLDVALUE = B_VARCHAR NEWVALUE = B_VARCHAR 2: Language OLDVALUE = B_VARCHAR NEWVALUE = B_VARCHAR 3: Character Set OLDVALUE = B_VARCHAR NEWVALUE = B_VARCHAR 4: Packet Size OLDVALUE = B_VARCHAR NEWVALUE = B_VARCHAR 5: Unicode data sorting local id OLDVALUE = %x00 NEWVALUE = B_VARCHAR 6: Unicode data sorting comparison flags OLDVALUE = %x00 NEWVALUE = B_VARCHAR 7: SQL Collation OLDVALUE = B_VARBYTE NEWVALUE = B_VARBYTE 8: Begin Transaction OLDVALUE = %x00 NEWVALUE = B_VARBYTE 9: Commit Transaction OLDVALUE = B_VARBYTE NEWVALUE = "0x00" 10: Rollback Transaction OLDVALUE = B_VARBYTE NEWVALUE = %x00 11: Enlist DTC Transaction OLDVALUE = B_VARBYTE NEWVALUE = %x00 12: Defect Transaction OLDVALUE = %x00 NEWVALUE = B_VARBYTE
13: Database Mirroring Partner OLDVALUE = %x00 PARTNER_NODE = B_VARCHAR NEWVALUE = PARTNER_NODE 15: Promote Transaction OLDVALUE = %x00 DTC_TOKEN = L_VARBYTE; NEWVALUE = DTC_TOKEN 16: Transaction Manager Address (not used) OLDVALUE = %x00 XACT_MANAGER_ADDRESS = B_VARBYTE NEWVALUE = XACT_MANAGER_ADDRESS 17: Transaction Ended OLDVALUE = B_VARBYTE NEWVALUE = %x00 18: Reset Completion Acknowledgement OLDVALUE = %x00 NEWVALUE = %x00 19: Sends back info of user instance for logins (login7) requesting so. OLDVALUE = %x00 NEWVALUE = B_VARCHAR 20: Routing OLDVALUE = %x00 %x00 Protocol = BYTE ProtocolProperty = USHORT AlternateServer = US_VARCHAR Protocol MUST be 0, specifying TCP-IP protocol. ProtocolProperty represents the TCP-IP port when Protocol is 0. A ProtocolProperty value of zero is not allowed when Protocol is TCP-IP. RoutingDataValue = Protocol ProtocolProperty AlternateServer RoutingDataValueLength = USHORT RoutingDataValueLength is the total length, in bytes, of the following fields: Protocol, ProtocolProperty, and AlternateServer. RoutingData = RoutingDataValueLength [RoutingDataValue] NEWVALUE = RoutingData Notes
%0x02 (SPN) A Unicode string that represents the Service Principal Name (SPN) to use for acquiring a Federated Authentication Token. SPN is a string that represents the resource in a directory. INFO Token Stream Name: INFO Token Stream Function: Used to send an information message to the client. Token Stream Comments The token value is 0xAB. Token Stream-Specific Rules: TokenType = BYTE Length = USHORT Number = LONG State = BYTE Class = BYTE MsgText = US_VARCHAR ServerName = B_VARCHAR ProcName = B_VARCHAR LineNumber = USHORT / ULONG; (Changed to ULONG in TDS 7.2) The type of the LineNumber element depends on the version of TDS. Token Stream Definition: INFO = TokenType Length Number State Class MsgText ServerName ProcName LineNumber
<SessionStateData> <StateId> <BYTE>09 </BYTE> </StateId> <StateLen> <BYTE>04 </BYTE> </StateLen> <StateValue> <BYTES>FF FF FF FF </BYTES> </StateValue> </SessionStateData> </SessionStateDataSet> </InitSessionRecoveryData> <SessionRecoveryDataToBe> <Length> <DWORD>09 00 00 00 </DWORD> </Length> <RecoveryDatabase> <B_VARCHAR> <BYTE>00 </BYTE> ascii=""> </BYTES> </B_VARCHAR> </RecoveryDatabase> <RecoveryCollation> <BYTELEN> <BYTE>00 </BYTE> </BYTELEN> <BYTES> </BYTES> </RecoveryCollation> <RecoveryLanguage> <B_VARCHAR> <BYTE>00 </BYTE> <BYTES ascii=""> </BYTES> </B_VARCHAR> </RecoveryLanguage> <SessionStateDataSet> <SessionStateData> <StateId> <BYTE>09 </BYTE> </StateId> <StateLen> <BYTE>04 </BYTE> </StateLen> <StateValue> 23 00 00 </BYTES> </StateValue> </SessionStateData> </SessionStateDataSet> </SessionRecoveryDataToBe> </FeatureData> </FeatureOpt> <FeatureOpt> <TERMINATOR> <BYTE>FF </BYTE> </TERMINATOR> </FeatureOpt> </FeatureExt> </Login7> </PacketData> </tds> FeatureExtAck with SESSIONRECOVERY Feature Data A response message that contains FeatureExtAck data for the SESSIONRECOVERY feature:
Change Tracking This section identifies changes that were made to this document the last release. Changes are classified as Major, Minor, or None. The revision class Major means that the technical content in the document was significantly revised. Major changes affect protocol interoperability or implementation. Examples of major changes are: A document revision that incorporates changes to interoperability requirements. A document revision that captures changes to protocol functionality. The revision Minor means that the meaning of the technical content was clarified. Minor changes do not affect protocol interoperability or implementation. Examples of minor changes are updates to clarify ambiguity at the sentence, paragraph, or table level. The revision class None means that no new technical changes were introduced. Minor editorial and formatting changes may have been made, but the relevant technical content is identical to the last released version. The changes made to this document are listed in the following table. For more information, please contact . Section Description Revision class All Removed duplicate product behavior notes about no SQL Server support for Federated Authentication. Minor PacketID 8167 : product behavior note regarding the starting value for PacketID. Major Collation Rule Definition 8169 : Clarified the description of a SQL collation. Major Trace Activity Header 8164 : Updated the stream-specific rules. Major XML Values 8168 : Added product behavior note regarding the use of binary XML. Major LOGIN7 8171 : Clarified that the rule for password obfuscation is mandatory and applies to both ibPassword and ibChangePassword. Major
2.2.6.4 LOGIN7 8168 : Added product behavior note regarding the use of binary XML. Major PRELOGIN 8164 : Updated the stream-specific rules. Major COLMETADATA 8182 : product behavior note regarding how the fHidden bit flag is set. Major Initial State 8165 : Removed the phrase "with Encryption" from the first bullet and the phrase "without Encryption" from the second bullet. Major
A Abstract data model client ( 113, 119) server ( 113, 127) ALL_HEADERS rule definition 33 34 34 15 19 157 179 27 C 15 187 Client abstract data model ( 113, 119) higher-layer triggered events ( 113, 121) initialization ( 113, 120) 118 message processing ( 113, 122) 126 125 125 125 122 124 124 123 messages 19 18 17 18 19 18 18 19 126 overview ( 113, 119) sequencing rules ( 113, 122) 126 125 125 125 122 124 124 123 timer events ( 118, 126) timers ( 113, 120) 17 132 D Data model - abstract client ( 113, 119) server ( 113, 127) Data stream types 31 31 31 Data type definitions 35 35 39 42 Table Valued Parameter 43 45 43 47 41 36 42 27 E 21 Examples 157 135 137 144 147 134 134 154 156 151 152 158 157 159 F 15 Final state ( 126, 133) 26 G 8 Grammar definition - token description 50 data stream types 31 31 31 data type definitions 35 35 39 42 43
41 36 42 general rules 30 30 27 packet data stream headers 33 34 34 48 27 H Higher-layer triggered events client ( 113, 121) server ( 113, 128) I 180 180 21 11 128 Initialization client ( 113, 120) server ( 113, 128) 8 L Local events client ( 118, 126) 118 Logged In ( 125, 132) 129 135 137 144 147 M Message processing client ( 113, 122) 126 125 125 125 122 124 124 123 server ( 113, 128) 132 133 128 132 129 131 129 Messages 17 27 17 25 77 21 19 syntax 17 27 17 25 77 50 21 19 17 N 10 O local events 126 133 179 12 P Packet data - token stream definition 77 79 80 82 85 86 87 89 92 95 99 100 101 103 103 104 105 107 108 110 111 110 Packet data stream headers 33 34 34 25 77 Packet header message type - stream definition 51 50 167
172 53 64 68 162 72 73 177 73 160 Packets 21 24 packet header 24 22 24 24 23 22 24 21 180 14 134 14 181 Protocol Details 113 Q 34 R 10 11 10 14 19 154 156 S Security 180 180 179 125 125 122 124 124 123 Sequencing rules client ( 113, 122) 126 125 125 125 122 124 124 123 server ( 113, 128) 132 133 128 132 129 131 129 Server abstract data model ( 113, 127) higher-layer triggered events ( 113, 128) initialization ( 113, 128) 118 message processing ( 113, 128) 132 133 128 132 129 131 129 messages 21 21 20 19 20 21 20 20 20 133 overview ( 113, 126) sequencing rules ( 113, 128) 132 133 128 132 129 131 129 timer events ( 118, 133) timers ( 113, 128) 19 131 179 151 152 18 18 158 157 15 Syntax client messages 19 18 17 18 19 18 18 19 grammar definition for description
50 31 35 27 27 33 48 17 packet data token and tokenless data streams 27 25 26 179 25 packet data token stream definition 77 79 80 82 85 86 87 89 92 95 99 100 101 103 103 77 104 105 107 108 110 111 110 packet header message type - stream definition 51 50 167 172 53 64 68 162 72 73 177 73 160 160 packets 21 24 22 server messages 21 21 20 19 20 21 20 20 20 T Timer events client ( 118, 126) server ( 118, 133) Timers client ( 113, 120) server ( 113, 128) 129 Token data stream 26 token definition 26 26 27 26 26 Token data stream definition 77 79 80 82 85 86 87 89 92 95 99 100 101 103 103 77 104 105 107 108 110 111 110 Token data stream examples 179 179 179 Token description - grammar definition 50 data stream types 31 31 31 data type definitions 35 35 39 42 43 41 36 42 general 30