Unfortunately, SQL Server doesn’t expose the information about the cipher suite used for Tabular Data Stream (TDS) encryption. But we can get this information by tracing the TCP connection with Microsoft (MS) Message Analyzer. MS Message Analyzer is a network tracing tool which can also reliably interpret higher protocols.
Mind that the tool doesn’t know that the used protocol is TDS if the SQL Server instance is running on a non-default port. Therefore, you’d need to assign the instance port number to the protocol. This is done through the menu option “Tools -> Options -> Parsing -> TDS -> Ports”
Also, it makes sense to filter the traffic based on the instance port number and the client address, like “*Port==xxxx and *Address==x.x.x.x”.
The cipher suite is negotiated during the handshake.
First, the client sends a ClientHello message containing all of the client-supported cipher suites:
Name Value Bit Offset Bit Length Type
body ClientHello{client_version=TLS
...
cipher_suites_length_in_bytes 56 608 16 UInt16
cipher_suites [TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384,TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256,TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA,TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA,TLS_DHE_RSA_WITH_AES_256_GCM_SHA384,TLS_DHE_RSA_WITH_AES_128_GCM_SHA256,TLS_DHE_RSA_WITH_AES_256_CBC_SHA,TLS_DHE_RSA_WITH_AES_128_CBC_SHA,TLS_RSA_WITH_AES_256_GCM_SHA384,TLS_RSA_WITH_AES_128_GCM_SHA256,TLS_RSA_WITH_AES_256_CBC_SHA256,TLS_RSA_WITH_AES_128_CBC_SHA256,TLS_RSA_WITH_AES_256_CBC_SHA,TLS_RSA_WITH_AES_128_CBC_SHA,TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384,TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256,TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA384,TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256,TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA,TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA,TLS_DHE_DSS_WITH_AES_256_CBC_SHA256,TLS_DHE_DSS_WITH_AES_128_CBC_SHA256,TLS_DHE_DSS_WITH_AES_256_CBC_SHA,TLS_DHE_DSS_WITH_AES_128_CBC_SHA,TLS_RSA_WITH_3DES_EDE_CBC_SHA,TLS_DHE_DSS_WITH_3DES_EDE_CBC_SHA,TLS_RSA_WITH_RC4_128_SHA,TLS_RSA_WITH_RC4_128_MD5] 624 448 ArrayValue`1
[0] TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384(49192) UInt16
[1] TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256(49191) UInt16
[2] TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA(49172) UInt16
[3] TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA(49171) UInt16
[4] TLS_DHE_RSA_WITH_AES_256_GCM_SHA384(159) UInt16
[5] TLS_DHE_RSA_WITH_AES_128_GCM_SHA256(158) UInt16
[6] TLS_DHE_RSA_WITH_AES_256_CBC_SHA(57) UInt16
[7] TLS_DHE_RSA_WITH_AES_128_CBC_SHA(51) UInt16
[8] TLS_RSA_WITH_AES_256_GCM_SHA384(157) UInt16
[9] TLS_RSA_WITH_AES_128_GCM_SHA256(156) UInt16
[10] TLS_RSA_WITH_AES_256_CBC_SHA256(61) UInt16
[11] TLS_RSA_WITH_AES_128_CBC_SHA256(60) UInt16
[12] TLS_RSA_WITH_AES_256_CBC_SHA(53) UInt16
[13] TLS_RSA_WITH_AES_128_CBC_SHA(47) UInt16
[14] TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384(49196) UInt16
[15] TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256(49195) UInt16
[16] TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA384(49188) UInt16
[17] TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256(49187) UInt16
[18] TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA(49162) UInt16
[19] TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA(49161) UInt16
[20] TLS_DHE_DSS_WITH_AES_256_CBC_SHA256(106) UInt16
[21] TLS_DHE_DSS_WITH_AES_128_CBC_SHA256(64) UInt16
[22] TLS_DHE_DSS_WITH_AES_256_CBC_SHA(56) UInt16
[23] TLS_DHE_DSS_WITH_AES_128_CBC_SHA(50) UInt16
[24] TLS_RSA_WITH_3DES_EDE_CBC_SHA(10) UInt16
[25] TLS_DHE_DSS_WITH_3DES_EDE_CBC_SHA(19) UInt16
[26] TLS_RSA_WITH_RC4_128_SHA(5) UInt16
[27] TLS_RSA_WITH_RC4_128_MD5(4) UInt16 Next, the server answers with a ServerHello mesage containing the chosen cipher suite:
Name Value Bit Offset Bit Length Type
body ServerHello{server_version=TLS
...
cipher_suite TLS_DHE_RSA_WITH_AES_256_GCM_SHA384(159) 608 16 IANA.CipherSuiteFinally, the client sends a ChangeCipherSpec message to signal the encryption activation:
Name Value Bit Offset Bit Length Type
records [ChangeCipherSpec,Handshake(Encrypted)] 0 408 ArrayValue`1 Reference:
