Skip to content
HeliosDB HeliosDB Full

Authentication Flow Diagrams

Authentication Flow Diagrams

HeliosDB Multi-Protocol Authentication Analysis

Researcher Agent Documentation Date: 2025-10-10

1. PostgreSQL SCRAM-SHA-256 Authentication

Flow Diagram

Client                                    Server
  |                                         |
  |-------- Startup Message --------------->|
  |  (protocol=3.0, user=demo, db=demo)     |
  |                                         |
  |<------- AuthenticationSASL -------------|
  |  [SCRAM-SHA-256-PLUS, SCRAM-SHA-256]    |
  |                                         |
  |-------- SASLInitialResponse ----------->|
  |  mechanism: SCRAM-SHA-256               |
  |  client-first-message:                  |
  |    "n,,n=demo,r=<client-nonce>"         |
  |                                         |
  |<------- AuthenticationSASLContinue -----|
  |  server-first-message:                  |
  |    "r=<client+server-nonce>,            |
  |     s=<salt>,i=<iteration-count>"       |
  |                                         |
  |-------- SASLResponse ------------------>|
  |  client-final-message:                  |
  |    "c=<channel-binding>,                |
  |     r=<nonce>,p=<client-proof>"         |
  |                                         |
  |<------- AuthenticationSASLFinal --------|
  |  server-final-message:                  |
  |    "v=<server-signature>"               |
  |                                         |
  |<------- AuthenticationOk ---------------|
  |                                         |
  |<------- ReadyForQuery ------------------|
  |  (transaction status: Idle)             |
  |                                         |

Detailed Steps

Step 1: Startup Message (Client → Server)

Message Type: None (first message has no type byte)
Length: 4 bytes (includes length field)
Protocol: 0x00030000 (version 3.0)
Parameters:
  - user: "demo"
  - database: "demo"
  - application_name: "python_app"
  - client_encoding: "UTF8"

Step 2: AuthenticationSASL (Server → Client)

Message Type: 'R' (Authentication request)
Length: Variable
Auth Type: 10 (SASL)
Mechanisms:
  - "SCRAM-SHA-256-PLUS" (length-prefixed string)
  - "SCRAM-SHA-256" (length-prefixed string)

Step 3: SASLInitialResponse (Client → Server)

Message Type: 'p' (password message repurposed)
Length: Variable
Selected Mechanism: "SCRAM-SHA-256" (length-prefixed)
Initial Response Length: 4 bytes
Initial Response: "n,,n=demo,r=fyko+d2lbbFgONRv9qkxdawL"
  - n,, = no channel binding
  - n=demo = username (ignored by server, uses startup username)
  - r=<nonce> = client-generated random nonce (base64)

Step 4: AuthenticationSASLContinue (Server → Client)

Message Type: 'R'
Length: Variable
Auth Type: 11 (SASL Continue)
Server Message:
  "r=fyko+d2lbbFgONRv9qkxdawL3rfcNHYJY1ZVvWVs7j,s=QSXCR+Q6sek8bf92,i=4096"
  - r = client_nonce + server_nonce
  - s = base64(salt)
  - i = iteration count (4096 minimum)

Step 5: SASLResponse (Client → Server)

Message Type: 'p'
Length: Variable
Response: "c=biws,r=<nonce>,p=<client-proof>"
  - c = base64(channel-binding) = "biws" for "n,,"
  - r = combined nonce from step 4
  - p = base64(ClientProof)


ClientProof Calculation:
  SaltedPassword = PBKDF2(password, salt, iterations)
  ClientKey = HMAC(SaltedPassword, "Client Key")
  StoredKey = SHA256(ClientKey)
  AuthMessage = client-first-bare + "," + server-first + "," + client-final-without-proof
  ClientSignature = HMAC(StoredKey, AuthMessage)
  ClientProof = ClientKey XOR ClientSignature

Step 6: AuthenticationSASLFinal (Server → Client)

Message Type: 'R'
Length: Variable
Auth Type: 12 (SASL Final)
Final Message: "v=<server-signature>"
  - v = base64(ServerSignature)


Server Verification:
  ServerKey = HMAC(SaltedPassword, "Server Key")
  ServerSignature = HMAC(ServerKey, AuthMessage)
  (Client verifies this signature)

Step 7: AuthenticationOk (Server → Client)

Message Type: 'R'
Length: 8
Auth Type: 0 (OK)

Step 8: ReadyForQuery (Server → Client)

Message Type: 'Z'
Length: 5
Transaction Status: 'I' (Idle), 'T' (In Transaction), 'E' (Failed Transaction)

TLS Integration with ALPN

Pre-Authentication TLS Negotiation:

Client                                    Server
  |                                         |
  |-------- SSLRequest ------------------->|
  |  (Length=8, SSL code=80877103)          |
  |                                         |
  |<------- 'S' (SSL OK) -------------------|
  |                                         |
  |====== TLS ClientHello ================>|
  |  ALPN Extension: ["postgresql"]         |
  |                                         |
  |<===== TLS ServerHello ==================|
  |  ALPN Selected: "postgresql"            |
  |                                         |
  |<==== TLS Certificate, Finished ========>|
  |                                         |
  |-- Startup Message (over TLS) --------->|
  |  (SCRAM flow continues over TLS)        |
  |                                         |

Implementation Checklist

  • PBKDF2 implementation (iteration count ≥ 4096)
  • HMAC-SHA-256 support
  • Base64 encoding/decoding
  • Nonce generation (cryptographically secure random)
  • Channel binding support (SCRAM-SHA-256-PLUS with TLS)
  • Username validation (ignore SCRAM username, use startup username)
  • Error handling (invalid proof, wrong password)
  • TLS ALPN extension (“postgresql” identifier)

2. MySQL caching_sha2_password Authentication

Flow Diagram

Client                                    Server
  |                                         |
  |<------- HandshakeV10 -------------------|
  |  protocol=10, auth_plugin_data,         |
  |  plugin=caching_sha2_password           |
  |                                         |
  |-------- HandshakeResponse41 ----------->|
  |  capability_flags, username,            |
  |  auth_response (SHA256 hash),           |
  |  database, plugin=caching_sha2_password |
  |                                         |
  |<------- AuthSwitchRequest (optional) ---|
  |  (if plugin negotiation needed)         |
  |                                         |
  |-------- AuthSwitchResponse ------------>|
  |  (new auth data)                        |
  |                                         |
  |<------- AuthMoreData -------------------|
  |  status=0x01 (fast auth success) OR     |
  |  status=0x03 (perform full auth) OR     |
  |  status=0x04 (request public key)       |
  |                                         |
  [If fast auth (cached)]
  |<------- OK_Packet ----------------------|
  |                                         |
  [If full auth required + TLS]
  |-------- Password (plaintext) ---------->|
  |  (sent over TLS channel)                |
  |                                         |
  |<------- OK_Packet ----------------------|
  |                                         |
  [If full auth required + no TLS]
  |<------- PublicKey (0x01) ---------------|
  |  (RSA public key in PEM format)         |
  |                                         |
  |-------- Encrypted Password ------------>|
  |  (RSA encrypted with public key)        |
  |                                         |
  |<------- OK_Packet ----------------------|
  |                                         |

Detailed Steps

Step 1: HandshakeV10 (Server → Client)

Payload Length: 3 bytes
Sequence ID: 0x00
Protocol Version: 0x0a (10)
Server Version: "8.0.35\0"
Connection ID: 4 bytes
Auth Plugin Data Part 1: 8 bytes (random)
Filler: 0x00
Capability Flags Lower: 2 bytes
Character Set: 0x21 (utf8mb4_general_ci)
Status Flags: 2 bytes (SERVER_STATUS_AUTOCOMMIT)
Capability Flags Upper: 2 bytes
Auth Plugin Data Length: 21 (if CLIENT_PLUGIN_AUTH)
Reserved: 10 bytes (0x00)
Auth Plugin Data Part 2: 12 bytes (random)
Auth Plugin Name: "caching_sha2_password\0"

Step 2: HandshakeResponse41 (Client → Server)

Payload Length: Variable
Sequence ID: 0x01
Capability Flags: 4 bytes (CLIENT_PROTOCOL_41 | CLIENT_SECURE_CONNECTION | ...)
Max Packet Size: 4 bytes (0x01000000 = 16MB)
Character Set: 1 byte (0x21 = utf8mb4_general_ci)
Reserved: 23 bytes (0x00)
Username: "demo\0" (NULL-terminated)
Auth Response Length: 1 byte (if CLIENT_PLUGIN_AUTH_LENENC_CLIENT_DATA)
Auth Response: 32 bytes (SHA256 hash)
  Hash = XOR(SHA256(password), SHA256(SHA256(SHA256(password)) + auth_plugin_data))
Database: "demo\0" (if CLIENT_CONNECT_WITH_DB)
Auth Plugin Name: "caching_sha2_password\0" (if CLIENT_PLUGIN_AUTH)

Step 3a: Fast Path - AuthMoreData (Server → Client)

Payload Length: 2
Sequence ID: 0x02
Packet Type: 0x01 (AuthMoreData)
Status: 0x03 (fast auth successful, password cached)


Followed by:
Payload Length: Variable
Sequence ID: 0x03
Packet Type: 0x00 (OK_Packet)

Step 3b: Full Auth - AuthMoreData (Server → Client)

Payload Length: 2
Sequence ID: 0x02
Packet Type: 0x01 (AuthMoreData)
Status: 0x04 (perform full authentication)

Step 4a: Full Auth with TLS (Client → Server)

Payload Length: Variable
Sequence ID: 0x03
Password: "demo_password\0" (plaintext over TLS)

Step 4b: Full Auth without TLS - Request Public Key (Client → Server)

Payload Length: 2
Sequence ID: 0x03
Request: 0x02 (request public key)


Server Response:
Payload Length: Variable
Sequence ID: 0x04
Packet Type: 0x01 (AuthMoreData)
Public Key: "-----BEGIN PUBLIC KEY-----\n...\n-----END PUBLIC KEY-----\n"


Client Response:
Payload Length: Variable
Sequence ID: 0x05
Encrypted Password: RSA(password XOR auth_plugin_data, public_key)

Step 5: OK_Packet (Server → Client)

Payload Length: Variable
Sequence ID: Variable
Packet Type: 0x00 (OK)
Affected Rows: 0 (encoded)
Last Insert ID: 0 (encoded)
Status Flags: 2 bytes (SERVER_STATUS_AUTOCOMMIT)
Warnings: 2 bytes
Info: "Welcome message" (optional)
Session State Changes: Variable (if CLIENT_SESSION_TRACK)

Auth Response Calculation

Initial Hash (Step 2):

stage1 = SHA256(password)
stage2 = SHA256(stage1)
stage3 = SHA256(stage2 + auth_plugin_data)
auth_response = XOR(stage1, stage3)

RSA Encryption (Step 4b):

# XOR password with auth_plugin_data
password_bytes = password.encode('utf8') + b'\0'
auth_data = auth_plugin_data
xor_result = bytes([a ^ b for a, b in zip(password_bytes, cycle(auth_data))])


# Encrypt with RSA public key (OAEP padding)
public_key = load_pem_public_key(server_public_key)
encrypted = public_key.encrypt(xor_result, OAEP(MGF1(SHA1()), SHA1(), None))

Capability Flags (Critical)

CLIENT_LONG_PASSWORD          = 0x00000001
CLIENT_FOUND_ROWS             = 0x00000002
CLIENT_LONG_FLAG              = 0x00000004
CLIENT_CONNECT_WITH_DB        = 0x00000008
CLIENT_NO_SCHEMA              = 0x00000010
CLIENT_COMPRESS               = 0x00000020
CLIENT_ODBC                   = 0x00000040
CLIENT_LOCAL_FILES            = 0x00000080
CLIENT_IGNORE_SPACE           = 0x00000100
CLIENT_PROTOCOL_41            = 0x00000200  # Required
CLIENT_INTERACTIVE            = 0x00000400
CLIENT_SSL                    = 0x00000800  # TLS support
CLIENT_IGNORE_SIGPIPE         = 0x00001000
CLIENT_TRANSACTIONS           = 0x00002000  # Transaction status
CLIENT_RESERVED               = 0x00004000
CLIENT_SECURE_CONNECTION      = 0x00008000  # Required
CLIENT_MULTI_STATEMENTS       = 0x00010000
CLIENT_MULTI_RESULTS          = 0x00020000
CLIENT_PS_MULTI_RESULTS       = 0x00040000
CLIENT_PLUGIN_AUTH            = 0x00080000  # Required for caching_sha2
CLIENT_CONNECT_ATTRS          = 0x00100000
CLIENT_PLUGIN_AUTH_LENENC     = 0x00200000
CLIENT_CAN_HANDLE_EXPIRED_PW  = 0x00400000
CLIENT_SESSION_TRACK          = 0x00800000
CLIENT_DEPRECATE_EOF          = 0x01000000
CLIENT_OPTIONAL_RESULTSET_MD  = 0x02000000
CLIENT_ZSTD_COMPRESSION       = 0x04000000
CLIENT_QUERY_ATTRIBUTES       = 0x08000000

Implementation Checklist

  • Server-first handshake (send HandshakeV10)
  • SHA-256 hashing (auth response calculation)
  • Capability flag negotiation
  • Auth plugin framework (caching_sha2_password, mysql_native_password)
  • Fast auth path (cached password verification)
  • Full auth path (TLS plaintext or RSA encryption)
  • RSA public key generation and transmission
  • TLS support (SERVER_SSL capability)
  • Character set handling (utf8mb4)
  • Error packet handling (0xFF packet type)

3. TDS 7.4 (SQL Server) Authentication

Flow Diagram

Client                                    Server
  |                                         |
  |-------- PRELOGIN ---------------------->|
  |  VERSION, ENCRYPTION, INSTANCE, MARS    |
  |                                         |
  |<------- PRELOGIN Response --------------|
  |  VERSION, ENCRYPTION (negotiated),      |
  |  INSTANCE (if applicable)               |
  |                                         |
  [If ENCRYPTION=ON or ENCRYPT_REQ]
  |====== TLS ClientHello (in PRELOGIN) ===>|
  |                                         |
  |<===== TLS ServerHello (in PRELOGIN) ====|
  |                                         |
  |<==== TLS Handshake Complete ==========>|
  |                                         |
  |-------- LOGIN7 (encrypted) ------------>|
  |  Hostname, Username, Password,          |
  |  App name, Server name, Database        |
  |                                         |
  |<------- LOGINACK ----------------------|
  |  Interface, TDS version, Server name    |
  |                                         |
  |<------- ENVCHANGE ----------------------|
  |  Database, Language, Collation changes  |
  |                                         |
  |<------- INFO tokens ---------------------|
  |  (informational messages)               |
  |                                         |
  |<------- DONE token ----------------------|
  |  (login process complete)               |
  |                                         |

Detailed Steps

Step 1: PRELOGIN (Client → Server)

Packet Header:
  Type: 0x12 (PRELOGIN)
  Status: 0x01 (EOM - End of Message)
  Length: Variable (e.g., 0x0034 = 52 bytes)
  SPID: 0x0000
  Packet ID: 0x01
  Window: 0x00


Token List (offset/length pairs):
  VERSION:    offset=0x0000, length=0x0006
  ENCRYPTION: offset=0x0006, length=0x0001
  INSTOPT:    offset=0x0007, length=0x0001
  THREADID:   offset=0x0008, length=0x0004
  MARS:       offset=0x000C, length=0x0001
  TRACEID:    offset=0x000D, length=0x0024
  TERMINATOR: 0xFF


Token Data:
  VERSION:    UL_VERSION=0x0F000000 (15.0), US_SUBBUILD=0x0000
  ENCRYPTION: 0x01 (ENCRYPT_ON) or 0x00 (ENCRYPT_OFF)
  INSTOPT:    0x00 (no instance)
  THREADID:   0x00000001 (client thread ID)
  MARS:       0x00 (MARS disabled)
  TRACEID:    CONNID (16 bytes), ActivityID (16 bytes), Sequence (4 bytes)

Step 2: PRELOGIN Response (Server → Client)

Packet Header:
  Type: 0x04 (TABULAR RESULT)
  Status: 0x01 (EOM)
  Length: Variable
  SPID: 0x0000
  Packet ID: 0x01
  Window: 0x00


Token List:
  VERSION:    offset=0x0000, length=0x0006
  ENCRYPTION: offset=0x0006, length=0x0001
  INSTOPT:    offset=0x0007, length=0x0001
  THREADID:   offset=0x0008, length=0x0000 (not returned)
  MARS:       offset=0x0008, length=0x0001
  TERMINATOR: 0xFF


Token Data:
  VERSION:    Server version (e.g., 0x10000FA0 = SQL Server 2019)
  ENCRYPTION: 0x01 (server requires encryption) or 0x00 (not required)
  INSTOPT:    0x00
  MARS:       0x00 (MARS not supported) or 0x01 (supported)

Step 3: TLS Handshake (if encryption negotiated)

Client → Server (PRELOGIN packet with TLS payload):
  Packet Header: Type=0x12, Status=0x00 (more packets)
  TLS ClientHello: (raw bytes)


Server → Client (PRELOGIN packet with TLS payload):
  Packet Header: Type=0x04, Status=0x00
  TLS ServerHello: (raw bytes)


... (TLS handshake continues in PRELOGIN packets)


Final handshake packet:
  Packet Header: Type=0x12/0x04, Status=0x01 (EOM, handshake complete)

Step 4: LOGIN7 (Client → Server)

Packet Header:
  Type: 0x10 (LOGIN7)
  Status: 0x01 (EOM)
  Length: Variable
  SPID: 0x0000
  Packet ID: 0x01 (or next sequence if TLS used)
  Window: 0x00


LOGIN7 Structure:
  Length: 4 bytes (total length)
  TDS Version: 0x74000004 (TDS 7.4)
  Packet Size: 4 bytes (0x00001000 = 4096)
  Client Version: 4 bytes (e.g., 0x0F000000)
  Client PID: 4 bytes
  Connection ID: 4 bytes (0x00000000)
  OptionFlags1: 1 byte
    - BYTE_ORDER (0x00=little endian, 0x01=big endian)
    - CHAR (0x00=ASCII, 0x02=EBCDIC)
    - FLOAT (0x00=IEEE 754, 0x0C=VAX, 0x10=ND5000)
    - DUMPLOAD (0x20=dump/load on, 0x00=off)
    - USE_DB_WARN (0x40=warn on use database, 0x00=don't warn)
    - INIT_DB_FATAL (0x80=fatal on init db, 0x00=not fatal)
  OptionFlags2: 1 byte
    - INIT_LANG_WARN (0x01=warn on init language)
    - ODBC_ON (0x02=ODBC driver)
    - TRAN_BOUNDARY (0x04=transaction boundary)
    - CACHE_CONNECT (0x08=cache connection)
    - USER_NORMAL (0x00), USER_SERVER (0x10), USER_REMUSER (0x20), USER_SQLREPL (0x30)
    - INTEGRATED_SECURITY (0x80=Windows auth)
  TypeFlags: 1 byte
    - SQL_TYPE (0x00=SQL, 0x01=DFLT, 0x02=TSQL)
    - OLEDB (0x10=OLEDB)
    - READ_ONLY_INTENT (0x20=read-only)
  OptionFlags3: 1 byte
    - CHANGE_PASSWORD (0x01)
    - SEND_YUKON_BINARY_XML (0x02)
    - USER_INSTANCE (0x04)
    - UNKNOWN_COLLATION_HANDLING (0x08)
    - EXTENSION (0x10=feature extension present)
  ClientTimeZone: 4 bytes (minutes offset from UTC)
  ClientLCID: 4 bytes (locale ID)


  Offset/Length Pairs (2 bytes offset, 2 bytes length for each):
    HostName:    offset, length (in characters, UTF-16LE)
    UserName:    offset, length
    Password:    offset, length (obfuscated)
    AppName:     offset, length
    ServerName:  offset, length
    Extension:   offset, length (if OptionFlags3 & EXTENSION)
    CltIntName:  offset, length (client interface library)
    Language:    offset, length
    Database:    offset, length
    ClientID:    6 bytes (MAC address or unique ID)
    SSPI:        offset, length (Windows auth blob)
    AtchDBFile:  offset, length (attach DB filename)
    ChangePassword: offset, length (new password)
    SSPILong:    4 bytes length (long SSPI)


  Variable Data:
    (all strings in UTF-16LE, password obfuscated)

Password Obfuscation Algorithm:

def obfuscate_password(password_utf16):
    obfuscated = bytearray()
    for byte in password_utf16:
        # XOR with 0xA5
        byte ^= 0xA5
        # Swap nibbles
        byte = ((byte & 0x0F) << 4) | ((byte & 0xF0) >> 4)
        obfuscated.append(byte)
    return bytes(obfuscated)


def deobfuscate_password(obfuscated_utf16):
    password = bytearray()
    for byte in obfuscated_utf16:
        # Swap nibbles back
        byte = ((byte & 0x0F) << 4) | ((byte & 0xF0) >> 4)
        # XOR with 0xA5
        byte ^= 0xA5
        password.append(byte)
    return bytes(password).decode('utf-16-le')

Step 5: LOGINACK (Server → Client)

Packet Header:
  Type: 0x04 (TABULAR RESULT)
  Status: 0x01 (EOM)


Token Stream:
  Token Type: 0xAD (LOGINACK)
  Token Length: 2 bytes
  Interface: 1 byte (0x00=SQL_DFLT, 0x01=SQL_TSQL)
  TDS Version: 4 bytes (0x74000004 = TDS 7.4)
  ProgName Length: 1 byte
  ProgName: Variable (UTF-16LE, server product name)
  MajorVer: 1 byte
  MinorVer: 1 byte
  BuildNumHi: 1 byte
  BuildNumLow: 1 byte

Step 6: ENVCHANGE (Server → Client)

Token Type: 0xE3 (ENVCHANGE)
Token Length: 2 bytes
Type: 1 byte
  - 0x01: Database changed
  - 0x02: Language changed
  - 0x03: Character set changed
  - 0x04: Packet size changed
  - 0x07: Collation changed
  - 0x0D: Begin transaction
  - 0x0E: Commit transaction
  - 0x0F: Rollback transaction
NewValue Length: 1 byte (B_VARCHAR)
NewValue: Variable (UTF-16LE)
OldValue Length: 1 byte
OldValue: Variable (UTF-16LE)

Step 7: DONE (Server → Client)

Token Type: 0xFD (DONE)
Status: 2 bytes
  - 0x0000: DONE_FINAL
  - 0x0001: DONE_MORE
  - 0x0002: DONE_ERROR
  - 0x0010: DONE_COUNT
CurCmd: 2 bytes (0x0000 for login)
DoneRowCount: 8 bytes (0 for login)

Implementation Checklist

  • PRELOGIN packet construction and parsing
  • Encryption negotiation (ENCRYPT_OFF, ENCRYPT_ON, ENCRYPT_REQ)
  • TLS handshake wrapping in PRELOGIN packets
  • LOGIN7 packet construction
  • Password obfuscation (XOR 0xA5 + nibble swap)
  • UTF-16LE encoding for strings
  • Token stream parsing (LOGINACK, ENVCHANGE, DONE, ERROR)
  • MARS support negotiation (optional)
  • Connection ID tracking (TRACEID)
  • Error handling (ERROR token 0xAA)

4. DRDA (DB2) Authentication

Flow Diagram

Client                                    Server
  |                                         |
  |-------- EXCSAT ------------------------>|
  |  (Exchange Server Attributes)           |
  |  EXTNAM, SRVCLSNM, SRVNAM, SRVRLSLV     |
  |                                         |
  |<------- EXCSATRD -----------------------|
  |  (EXCSAT Reply Data)                    |
  |  Server attributes                      |
  |                                         |
  |-------- ACCSEC ------------------------>|
  |  (Access Security)                      |
  |  SECMEC (security mechanism)            |
  |  RDBNAM (database name, optional)       |
  |                                         |
  |<------- ACCSECRD -----------------------|
  |  (Access Security Reply Data)           |
  |  Selected SECMEC, security token        |
  |                                         |
  [If SECMEC=USRIDPWD or USRENCPWD]
  |-------- SECCHK ------------------------>|
  |  (Security Check)                       |
  |  USRID, PASSWORD (or encrypted)         |
  |                                         |
  |<------- SECCHKRD -----------------------|
  |  (Security Check Reply Data)            |
  |  (or SECCHKRM if error)                 |
  |                                         |
  |-------- ACCRDB ------------------------>|
  |  (Access RDB)                           |
  |  RDBNAM, RDBACCCL, PRDID, TYPDEFNAM     |
  |                                         |
  |<------- ACCRDBRM -----------------------|
  |  (Access RDB Reply Message)             |
  |  PRDID, SRVCLSNM, connection status     |
  |                                         |
  |<------- SQLCARD (optional) -------------|
  |  (SQL Communications Area Reply Data)   |
  |                                         |

Detailed Steps

Step 1: EXCSAT (Client → Server)

DDM Packet:
  Length: 0x002A (42 bytes)
  Magic: 0xD0
  Format: 0x01
  Code Point: 0x1041 (EXCSAT)


Objects (nested DDM structures):
  EXTNAM (0x115E):
    Length: 0x001C (28 bytes)
    Magic: 0xD0
    Format: 0x01
    Code Point: 0x115E
    Data: 20-byte app ID + 8-byte process/thread ID
      App ID: "PYTHON_APP_V1.0     " (padded to 20)
      Process ID: 0x00001234 (4 bytes) + Thread ID: 0x00005678 (4 bytes)


  SRVCLSNM (0x1147) [optional]:
    Length: 0x000C
    Magic: 0xD0
    Format: 0x01
    Code Point: 0x1147
    Data: "SQLDB2   " (server class, EBCDIC)


  SRVNAM (0x116D) [optional]:
    Length: 0x000C
    Magic: 0xD0
    Format: 0x01
    Code Point: 0x116D
    Data: "CLIENT01" (server name, EBCDIC)


  SRVRLSLV (0x115A) [optional]:
    Length: 0x000A
    Magic: 0xD0
    Format: 0x01
    Code Point: 0x115A
    Data: "QDB2/LNX 11.5.8.0" (release level)

Step 2: EXCSATRD (Server → Client)

DDM Packet:
  Length: Variable
  Magic: 0xD0
  Format: 0x01
  Code Point: 0x1443 (EXCSATRD)


Objects:
  SRVCLSNM (0x1147):
    Server class: "SQLDB2   "


  SRVNAM (0x116D):
    Server name: "DB2SERVER"


  SRVRLSLV (0x115A):
    Server release: "QDB2/LNX 11.5.8.0"


  EXTNAM (0x115E) [optional]:
    Server's external name

Step 3: ACCSEC (Client → Server)

DDM Packet:
  Length: Variable
  Magic: 0xD0
  Format: 0x01
  Code Point: 0x106D (ACCSEC)


Objects:
  SECMEC (0x11A4):
    Length: 0x0008
    Magic: 0xD0
    Format: 0x01
    Code Point: 0x11A4
    Data: 2-byte security mechanism code
      - 0x0003: USRIDPWD (user ID + password)
      - 0x0004: USRIDONL (user ID only)
      - 0x0007: USRENCPWD (encrypted password)
      - 0x0009: EUSRIDPWD (external user + password)
      - 0x000B: DCE (Kerberos)


  RDBNAM (0x2110) [optional]:
    Length: 0x0018
    Magic: 0xD0
    Format: 0x01
    Code Point: 0x2110
    Data: "SAMPLE            " (18 bytes, padded with spaces)


  SECTKN (0x11DC) [optional, for DCE/Kerberos]:
    Security token data

Step 4: ACCSECRD (Server → Client)

DDM Packet:
  Length: Variable
  Magic: 0xD0
  Format: 0x01
  Code Point: 0x14AC (ACCSECRD)


Objects:
  SECMEC (0x11A4):
    Selected security mechanism (echoed from client)


  SECTKN (0x11DC) [if applicable]:
    Server security token (challenge for auth)

Step 5: SECCHK (Client → Server) [if USRIDPWD/USRENCPWD]

DDM Packet:
  Length: Variable
  Magic: 0xD0
  Format: 0x01
  Code Point: 0x106E (SECCHK)


Objects:
  USRID (0x11A0):
    Length: Variable
    Magic: 0xD0
    Format: 0x01
    Code Point: 0x11A0
    Data: User ID (EBCDIC or ASCII based on negotiation)
      "DEMO    " (padded to 8 bytes)


  PASSWORD (0x11A1) [if USRIDPWD]:
    Length: Variable
    Magic: 0xD0
    Format: 0x01
    Code Point: 0x11A1
    Data: Password (EBCDIC or ASCII)
      "DEMOPASS" (max 255 bytes)


  ENCUSRID (0x11A2) [if USRENCPWD]:
    Encrypted user ID


  ENCPASSWORD (0x11A3) [if USRENCPWD]:
    Encrypted password

Step 6: SECCHKRD (Server → Client)

DDM Packet:
  Length: 0x0006
  Magic: 0xD0
  Format: 0x01
  Code Point: 0x1219 (SECCHKRD)


(No additional data for successful auth)


OR (if error):
DDM Packet:
  Code Point: 0x1121 (SECCHKRM - Security Check Reply Message)
  SECCHKCD (0x11A5): Security check code (error code)

Step 7: ACCRDB (Client → Server)

DDM Packet:
  Length: Variable
  Magic: 0xD0
  Format: 0x01
  Code Point: 0x2001 (ACCRDB)


Objects:
  RDBNAM (0x2110):
    Database name: "SAMPLE            " (18 bytes)


  RDBACCCL (0x210F):
    Length: 0x0008
    Magic: 0xD0
    Format: 0x01
    Code Point: 0x210F
    Data: 2-byte access class
      - 0x2407: SQLAM (SQL Application Manager)
      - 0x2408: RDA (Remote Data Access)


  PRDID (0x112E):
    Length: Variable
    Magic: 0xD0
    Format: 0x01
    Code Point: 0x112E
    Data: Product ID (e.g., "JCC04210" for IBM Java driver)


  TYPDEFNAM (0x002F):
    Length: Variable
    Magic: 0xD0
    Format: 0x01
    Code Point: 0x002F
    Data: Type definition name
      - "QTDSQLASC" (ASCII)
      - "QTDSQLX" (mixed EBCDIC/ASCII)
      - "QTDSQLJVM" (Java/UTF-8)


  CRRTKN (0x2135) [optional]:
    Correlation token (connection tracking)


  TRGDFTRT (0x213B) [optional]:
    Target default transaction (auto-commit, etc.)

Step 8: ACCRDBRM (Server → Client)

DDM Packet:
  Length: Variable
  Magic: 0xD0
  Format: 0x01
  Code Point: 0x2201 (ACCRDBRM)


Objects:
  PRDID (0x112E):
    Server product ID: "SQL11058" (DB2 11.5.8)


  SRVCLSNM (0x1147):
    Server class: "SQLDB2   "


  TYPDEFNAM (0x002F):
    Agreed type definition: "QTDSQLASC"


  CRRTKN (0x2135) [optional]:
    Correlation token (echoed or generated)


  SRVDGN (0x1153) [optional]:
    Server diagnostic information

Step 9: SQLCARD (Server → Client) [optional]

DDM Packet:
  Code Point: 0x2408 (SQLCARD)


Objects:
  SQLSTATE: 5-byte SQL state code (e.g., "00000" for success)
  SQLCODE: 4-byte SQL code (0 for success, negative for error)
  SQLERRMC: Error message text
  ... (other SQL diagnostic fields)

Character Encoding Handling

EBCDIC to ASCII Conversion:

def drda_encode_string(string, use_ebcdic=True):
    if use_ebcdic:
        # Convert to EBCDIC (CP037 or CP500)
        return string.encode('cp037')
    else:
        # Convert to ASCII (UTF-8)
        return string.encode('utf-8')


def drda_decode_string(bytes_data, use_ebcdic=True):
    if use_ebcdic:
        return bytes_data.decode('cp037')
    else:
        return bytes_data.decode('utf-8')

Type Definition Negotiation:

  • QTDSQLASC: ASCII encoding, negotiate early to avoid EBCDIC
  • QTDSQLX: Mixed (some EBCDIC, some ASCII)
  • QTDSQLJVM: UTF-8 encoding (modern clients)

Implementation Checklist

  • DDM packet framing (length + 0xD0 + code point)
  • EXCSAT/EXCSATRD exchange
  • ACCSEC/ACCSECRD security negotiation
  • SECCHK/SECCHKRD authentication (USRIDPWD minimum)
  • ACCRDB/ACCRDBRM database access
  • EBCDIC ↔ UTF-8 conversion (or negotiate ASCII early)
  • Security mechanism support (USRIDPWD=0x0003 minimum)
  • Database name padding (18 bytes, space-padded)
  • Code point mapping (request → reply pairs)
  • Error handling (SECCHKRM, ACCRDBRM errors)

5. Oracle Net/TTC Authentication (Simplified)

Flow Diagram

Client                                    Server
  |                                         |
  |-------- CONNECT ----------------------->|
  |  (DESCRIPTION=(...), VERSION, SDU/TDU)  |
  |                                         |
  |<------- ACCEPT or REFUSE ---------------|
  |  (VERSION, SDU/TDU negotiation)         |
  |                                         |
  |-------- DATA (Protocol Negotiation) --->|
  |  (Supported versions: [6,5,4,3,2,1,0])  |
  |                                         |
  |<------- DATA (Protocol Ack) ------------|
  |  (Selected version, character set)      |
  |                                         |
  |-------- DATA (Data Type Negotiation) -->|
  |  (Endianness, number format, etc.)      |
  |                                         |
  |<------- DATA (Data Type Ack) -----------|
  |                                         |
  |-------- DATA (TTI O3LOGON Phase 1) ---->|
  |  (User, Auth params, service name)      |
  |                                         |
  |<------- DATA (Auth Challenge) ----------|
  |  (Server AUTH_SESSKEY, salt)            |
  |                                         |
  |-------- DATA (TTI O3LOGON Phase 2) ---->|
  |  (Password hash, AUTH_SESSKEY)          |
  |                                         |
  |<------- DATA (Auth Result) -------------|
  |  (Success or error)                     |
  |                                         |

Detailed Steps

Step 1: CONNECT (Client → Server)

TNS Header:
  Length: 0x00F4 (244 bytes, big-endian)
  Checksum: 0x0000
  Type: 0x01 (CONNECT)
  Reserved: 0x00
  Header Checksum: 0x0000


CONNECT Data:
  Version: 0x0138 (312 = Oracle 11g/12c)
  Version Compatible: 0x012C (300)
  Service Options: 0x0C41
    - Broken Connect Notify (0x0001)
    - Packet Checksum (0x0040)
    - Header Checksum (0x0200)
    - Full Duplex (0x0400)
    - Half Duplex (0x0800)
  Session Data Unit: 0x2000 (8192 bytes max)
  Max Transmission Unit: 0x7FFF (32767 bytes)
  NT Protocol Characteristics: 0x4E54 ("NT")
  Line Turnaround: 0x0000
  Value of 1: 0x0001 (byte order test)
  Data Length: Variable (e.g., 0x00A0 = 160 bytes)
  Data Offset: 0x003A (58 bytes from start)
  Max Connect Data: 0x00000000
  Connect Flags 0: 0x41
  Connect Flags 1: 0x41
  Trace Facility: Variable
  Connect Data: "(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=DEMO)
                 (CID=(PROGRAM=python)(HOST=client)(USER=demo)))
                 (ADDRESS=(PROTOCOL=TCP)(HOST=server)(PORT=1521)))"

Step 2: ACCEPT (Server → Client)

TNS Header:
  Length: Variable
  Checksum: 0x0000
  Type: 0x02 (ACCEPT)
  Reserved: 0x00
  Header Checksum: 0x0000


ACCEPT Data:
  Version: 0x0138 (312)
  Service Options: 0x0C41 (negotiated)
  Session Data Unit: 0x2000 (agreed SDU)
  Max Transmission Unit: 0x7FFF
  Value of 1: 0x0001
  Data Length: Variable
  Data Offset: Variable
  Connect Flags: Variable
  Connect Data: (optional, typically empty)

Step 3: DATA - Protocol Negotiation (Client → Server)

TNS Header:
  Length: Variable
  Checksum: 0x0000
  Type: 0x06 (DATA)
  Reserved: 0x00
  Header Checksum: 0x0000


Data Flags: 0x0000
Data Type: 0x01 (Protocol Negotiation)
Payload:
  Supported Versions: [0x06, 0x05, 0x04, 0x03, 0x02, 0x01, 0x00]

Step 4: DATA - Protocol Ack (Server → Client)

TNS Header:
  Type: 0x06 (DATA)


Data Flags: 0x0000
Data Type: 0x01
Payload:
  Selected Version: 0x06
  Character Set: "AL32UTF8\0" or "US7ASCII\0"
  National Character Set: "AL16UTF16\0"

Step 5: DATA - Data Type Negotiation (Client → Server)

TNS Header:
  Type: 0x06 (DATA)


Data Flags: 0x0000
Data Type: 0x02 (Data Type Negotiation)
Payload:
  Endianness: 0x01 (little-endian) or 0x00 (big-endian)
  Character Set Form: 0x01 (explicit)
  Conversion: 0x00
  ... (internal data representations)

Step 6: DATA - TTI O3LOGON Phase 1 (Client → Server)

TNS Header:
  Type: 0x06 (DATA)


Data Flags: 0x0000
Data Type: 0x03 (TTI Function Call)
Payload:
  TTI Opcode: 0x76 (O3LOGON)
  Function Call Data:
    - AUTH_TERMINAL: Client hostname
    - AUTH_PROGRAM_NM: "python\0"
    - AUTH_MACHINE: Client machine name
    - AUTH_PID: Process ID
    - AUTH_SID: User name
    - AUTH_LOGON_USER: OS user
    - AUTH_ALTER_SESSION: "ALTER SESSION SET ..."
    - AUTH_DRIVER_NAME: "python-oracledb : 2.1.0"
    - SERVICE_NAME: "DEMO"
    - AUTH_VERSION: Client version

Step 7: DATA - Auth Challenge (Server → Client)

TNS Header:
  Type: 0x06 (DATA)


Data Type: 0x03 (TTI)
Payload:
  TTI Opcode: 0x76 (O3LOGON response)
  AUTH_SESSKEY: 64-byte session key (hex)
  AUTH_VFR_DATA: Server version, features
  AUTH_SVR_DN: Server distinguished name (optional)
  AUTH_SALT: Salt for password hashing (optional)

Step 8: DATA - TTI O3LOGON Phase 2 (Client → Server)

TNS Header:
  Type: 0x06 (DATA)


Data Type: 0x03 (TTI)
Payload:
  TTI Opcode: 0x76 (O3LOGON continuation)
  AUTH_PASSWORD: Hashed password (algorithm varies)
    - Legacy: DES-based hash
    - Modern: SHA-1 or SHA-2 based
  AUTH_SESSKEY: Session key from server (echoed)
  AUTH_RTT: Round-trip time

Password Hashing (Simplified):

Legacy (pre-11g):
  - DES encryption of username with password as key
  - Highly insecure, not recommended


11g+ (SHA-1 based):
  - PBKDF2-like derivation
  - Hash = SHA1(password + AUTH_SALT)
  - Multiple rounds for security


12c+ (Enhanced):
  - SHA-256 or SHA-512 based
  - PBKDF2 with salt and iterations

Step 9: DATA - Auth Result (Server → Client)

TNS Header:
  Type: 0x06 (DATA)


Data Type: 0x03 (TTI)
Payload:
  TTI Opcode: 0x76 (O3LOGON result)
  AUTH_STATUS: 0x00 (success) or error code
  AUTH_SESSION_ID: Server session ID
  AUTH_SERIAL_NUM: Session serial number


  OR (if error):
  ORA-01017: invalid username/password; logon denied

Connect Data Format

Structure:

(DESCRIPTION=
  (CONNECT_DATA=
    (SERVICE_NAME=<service>)
    (SID=<sid>)  # Alternative to SERVICE_NAME
    (SERVER=DEDICATED)  # or SHARED
    (INSTANCE_NAME=<instance>)
    (CID=
      (PROGRAM=<program>)
      (HOST=<hostname>)
      (USER=<os_user>)
    )
  )
  (ADDRESS_LIST=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=<server>)
      (PORT=<port>)
    )
  )
)

Parsing:

import re


def parse_connect_data(connect_string):
    # Simplified parser
    service_match = re.search(r'SERVICE_NAME=([^)]+)', connect_string)
    host_match = re.search(r'HOST=([^)]+)', connect_string)
    port_match = re.search(r'PORT=(\d+)', connect_string)


    return {
        'service_name': service_match.group(1) if service_match else None,
        'host': host_match.group(1) if host_match else None,
        'port': int(port_match.group(1)) if port_match else 1521
    }

Implementation Checklist (Bronze Level)

  • TNS packet framing (big-endian length + type)
  • CONNECT/ACCEPT handshake
  • Connect data parsing (SERVICE_NAME extraction)
  • Protocol version negotiation (version 6 recommended)
  • Data type negotiation (endianness, character set)
  • TTC/TTI basic support (O3LOGON opcode)
  • Password hashing (legacy or modern, based on server)
  • Character set handling (AL32UTF8)
  • Error response parsing (ORA-xxxxx codes)
  • “SELECT 1 FROM DUAL” support (OSQL opcode)

Note: Full Oracle protocol implementation is complex. Bronze level focuses on basic connectivity and simple queries. Silver/Gold levels would require reverse engineering or using oracledb Thin mode as reference.

Summary & Recommendations

Implementation Priority

  1. PostgreSQL SCRAM-SHA-256 (Highest complexity, most value)

    • RFC-compliant implementation
    • TLS ALPN integration
    • Excellent for testing auth framework

  2. MySQL caching_sha2_password (Medium complexity, high value)

    • Server-first handshake (unique pattern)
    • RSA encryption for non-TLS connections
    • Tests capability flag negotiation

  3. TDS 7.4 LOGIN7 (Medium-high complexity)

    • TLS wrapping in PRELOGIN
    • Password obfuscation
    • Bronze level: basic auth only

  4. DRDA USRIDPWD (Medium complexity)

    • DDM packet framing
    • EBCDIC handling (or negotiate ASCII)
    • Bronze level: simple auth sufficient

  5. Oracle O3LOGON (Highest complexity, lowest priority)

    • Reverse-engineered protocol
    • Start with Bronze level only
    • Consider using oracledb Thin as reference

Testing Strategy

Unit Tests:

  • Password hashing algorithms (SCRAM, SHA256, obfuscation)
  • Packet framing and parsing
  • Character encoding conversion

Integration Tests:

  • Each Python client library (psycopg2, asyncpg, PyMySQL, etc.)
  • Authentication success/failure scenarios
  • TLS negotiation and ALPN selection

Security Tests:

  • Invalid credentials handling
  • TLS enforcement
  • Replay attack prevention (nonce validation)

Common Implementation Patterns

Packet Framing:

class ProtocolHandler:
    def read_packet(self, socket):
        # Read length prefix (protocol-specific)
        # Read packet type (if applicable)
        # Read payload
        # Return structured packet
        pass


    def write_packet(self, socket, packet):
        # Encode packet type
        # Encode length prefix
        # Encode payload
        # Send to socket
        pass

Authentication State Machine:

class AuthStateMachine:
    STATES = ['INIT', 'CHALLENGE', 'RESPONSE', 'SUCCESS', 'FAILURE']


    def __init__(self):
        self.state = 'INIT'


    def process(self, message):
        if self.state == 'INIT':
            # Send challenge or request credentials
            self.state = 'CHALLENGE'
        elif self.state == 'CHALLENGE':
            # Verify credentials
            if valid:
                self.state = 'SUCCESS'
            else:
                self.state = 'FAILURE'
        # ...

Error Handling:

  • Map protocol-specific errors to generic HeliosDB errors
  • Preserve original error codes for debugging
  • Log authentication attempts and failures

Document Status: Complete Next Steps: Coordinate with architect on auth framework design, coordinate with coder on implementation modules