If a client connects to SQL Server, and a certificate is used to power TLS encryption, how can i get information about that certificate?

Ideally i want all the details, but i'd be happy with

Issuer: SSL_Self_Signed_Fallback
Issued to: SSL_Self_Signed_Fallback
SHA1 fingerprint: 6640479c9cac2b7ca63c9708c71a9245b951b4c7

Obviously i can't just look at the configuration on the server; as i'm trying to verify no MitM.

Also, i want to verify the correct certificate is being used (especially when no certificate is selected for use in the SQL Server Configuration Manager).

Either way: i want the client to be able to verify the certificate.

How can i get information about the certificate.

Assume:

Whichever helps.

Bonus - Decoding network traffic

You can spy on the network traffic, and view the raw TLS handshake:

16 03 01 04 d0 02 00 00 51 03 01 5d a7 35 91 e7 c2 8d f3 3e d5 e4 6d ec 9a b9 7f af c1 43 ad d3 51 41 b7 44 4f 57 4e 47 52 44 00 20 d1 03 00 00 5b 82 d8 85 fe e2 b6 9a 6c ec 88 69 fc ee d3 61
23 e4 ae 17 3b be 1a e2 8f 29 23 ca c0 14 00 00 09 00 17 00 00 ff 01 00 01 00 0b 00 03 08 00 03 05 00 03 02 30 82 02 fe 30 82 01 e6 a0 03 02 01 02 02 10 20 9e 8f 8c 08 72 c1 bf 45 ad f8 df 73
1f 21 47 30 0d 06 09 2a 86 48 86 f7 0d 01 01 0b 05 00 30 3b 31 39 30 37 06 03 55 04 03 1e 30 00 53 00 53 00 4c 00 5f 00 53 00 65 00 6c 00 66 00 5f 00 53 00 69 00 67 00 6e 00 65 00 64 00 5f 00
46 00 61 00 6c 00 6c 00 62 00 61 00 63 00 6b 30 1e 17 0d 31 39 31 30 31 36 31 34 30 31 30 36 5a 17 0d 34 39 31 30 31 36 31 34 30 31 30 36 5a 30 3b 31 39 30 37 06 03 55 04 03 1e 30 00 53 00 53
00 4c 00 5f 00 53 00 65 00 6c 00 66 00 5f 00 53 00 69 00 67 00 6e 00 65 00 64 00 5f 00 46 00 61 00 6c 00 6c 00 62 00 61 00 63 00 6b 30 82 01 22 30 0d 06 09 2a 86 48 86 f7 0d 01 01 01 05 00 03
82 01 0f 00 30 82 01 0a 02 82 01 01 00 b6 dc 05 25 3e 1e 41 82 f6 b4 bc 97 1c c5 1e 02 a6 bb 1a c7 34 ed 51 cc 65 a6 60 72 0c 3c 0e ac 57 df 4f df 18 15 14 6f e0 9c d4 39 36 a1 8e 5b 17 85 0a
93 c2 78 02 70 89 f5 35 18 55 37 14 b0 6e 4c cd 0d e7 a0 81 33 c8 42 bc 55 f6 71 fa 4e d2 98 5e 63 5b 32 a6 c3 04 21 3d 22 df 8c ee a7 6e 53 4b be 3d e6 39 c9 90 c6 a9 ed df 7a a8 46 83 b9 7c
f1 03 7c 45 b1 be 42 ee 71 aa bc d6 7b 27 41 f3 b8 c4 16 2a 18 8a 38 4c 19 51 25 78 56 ce 12 75 d0 eb 29 19 c5 70 b1 c0 13 18 77 77 a6 9b 47 1f fe 1f 27 5a 50 fb 3f 73 03 c3 a0 5d 2f f4 dd 37
fd df 87 f0 80 e9 de a7 16 df ee b8 00 c2 1e 68 49 19 ce 44 f4 af ac bf d3 5f b8 1c 64 d5 95 1f 8b 32 c1 5a 80 a8 21 2d 2c 66 b8 7e 29 13 11 f8 1f e1 e9 0f 68 4e 0a b8 1b 3c f3 28 6f b7 46 85
b1 17 c9 c6 82 e4 3c 28 d4 6d d9 5b dd 02 03 01 00 01 30 0d 06 09 2a 86 48 86 f7 0d 01 01 0b 05 00 03 82 01 01 00 3e f8 c8 54 30 c1 c1 12 5e eb 01 e5 50 41 57 55 de 9e 2e 69 2a 14 fa 9a 5a 9f
d3 c3 1c 28 49 28 b2 c2 e0 cf 3d 7f 48 88 3c 25 e3 30 b7 93 e1 5c a5 9c 6f 41 b0 fb 55 67 69 e5 c5 0e 46 7c e6 2b b4 2b d8 ac 42 62 1e 18 01 69 cd e0 85 59 77 19 bd f9 17 fc 1d 10 ba 8a 93 aa
88 8e e9 73 2c 56 2e d1 5e 22 ca 20 61 4c 03 f0 70 9f a7 a7 ed 16 3a fa 00 0e 9e 39 e8 6b 99 a1 72 cb 48 b8 88 26 2d 9d 3f d6 48 70 f0 a5 51 ee c0 a9 0a e8 cf e6 ed 27 79 4d f1 23 a6 f4 64 a0
72 04 2a 3a b9 30 aa 84 e4 d2 26 9d 8c 3e b5 fe 87 ef 2c 21 c7 4e 38 1f b7 b1 25 69 68 d8 42 b1 8d 25 f3 90 14 78 d2 39 9f 0f 17 2a fc 24 2e 3b 3b 40 99 c6 fb c9 0a b0 af cd 66 f1 c9 84 c5 be
dc e9 38 bb 36 2d be 6b db db 86 95 96 c6 84 4b 7d b0 a1 ea f7 42 b2 56 93 ec e9 fd 86 8f a5 ad f0 57 63 78 d0 5a 0c 00 01 67 03 00 18 61 04 6d f6 56 8c e9 8a 82 29 0a d4 da ea be 89 52 30 bf
cd 49 66 77 7f 18 12 b1 75 44 96 d1 9e 6d 15 da 69 ba a7 7c ee 6f ab 96 ea b0 0d 64 0f c4 22 ba d7 9f 54 71 15 42 84 50 95 a4 ad 16 15 71 56 a7 0a 80 60 2d 83 86 80 0a 96 7b 30 6a aa b7 be 8f
45 9b 96 1a 5c b2 d3 e6 28 6f c4 e4 bb 70 ce 01 00 23 e6 8d 9e bf fc 90 66 f8 07 b6 6b 44 bb 03 b5 3b fd 72 fe 7b 5a e6 15 5d 10 d6 51 22 b8 44 8d 27 85 20 53 04 fb 06 32 b7 03 9e ba 7a 32 62
f1 61 25 8a c4 42 3a 02 1f be cf 92 4d 6e 48 d3 48 8b 8f 03 dc 66 d8 8b 63 21 be 6f b0 53 1c 00 37 4e 7e 4f e8 1e ba d2 c4 45 0f c0 13 ed 71 ee 47 f3 4c 0d 1d af d5 d5 bb 52 78 4b b3 55 70 4e
bc a4 f1 da 4a ec 60 7a af 81 23 49 16 57 db 8e 8b 22 65 91 3f 5b 1c 0a 13 d0 e4 b0 a4 d3 10 33 a3 bd c9 39 b6 ea 9b 73 67 11 49 ed 4f 6d 71 ae ce a3 2a 3a 6d 07 d9 d6 24 89 27 2d e6 5a e9 e1
e0 6c 47 5c c1 c0 72 01 e6 63 a6 06 7f 0d b0 16 68 7d 82 49 86 df 38 9b b4 95 5a 95 f8 e8 03 e9 63 83 7c c4 07 62 a1 f5 ce 48 0a 0c 6e 9c da e5 bf 5e e4 56 a1 de 98 80 e5 65 df eb 25 7c 38 97
8a e1 8d a3 c9 a8 a6 f8 bb 77 06 16 ec 21 8a 5c 0e 0e 00 00 00

Which can then be decoded:

Record Header

16              ; type 0x16 (22) = Handshake record
03 01           ; protocol version { 3, 1 } TLS 1.0
04 d0           ; 0x04d0 bytes of handshake message follows

Handshake

02                       ; Handshake type 0x02 (2=server_hello)
00 51                    ; bytes in message 0x0051 (88-bytes)
03 01                    ; server_version { 3, 1 } (TLS 1.0)
5d a7 35 91              ; random.gmt_unix_time (1571239313 = 10/16/2019 3:21pm UTC)
e7 c2 8d f3 3e d5 e4 6d  ; random.random_bytes 28-bytes 
ec 9a b9 7f af c1 43 ad  
d3 51 41 b7 44 4f 57 4e 
47 52 44 00 20 d1 03 00  ; "çÂ.ó>Õämìš¹¯ÁCÓQA·DOWNGRD\0"

00 5b 82 d8 85 fe e2 b6  ; SessionID (variable length 2..32-bytes)
9a 6c ec 88 69 fc ee d3 
61 23 e4 ae 17 3b be 1a 
e2 8f 29 23 ca c0 14 00 

00 09                    ; CipherSuite { 0, 9} (TLS_RSA_WITH_DES_CBC_SHA)
00 17                    ; CipherSuite { 0, 0x17 } = (TLS_DH_anon_EXPORT_WITH_RC4_40_MD5)
00 00                    ; CipherSuite { 0, 0 } (TLS_NULL_WITH_NULL_NULL)

ff                       ; compression_method 0xff
01                       ; compression_method 0x01
00                       ; compression_method 0x00 (0=null)

01 00                    ; ???

Handshake header

0b          ; 0x0b (11) handshake message (11=certificate)
00 03 08    ; 0x308 (776) bytes of certificates to follow

Certificates length

00 03 05    ; length of all certificates to follow is 0x305 (773) bytes

Certificate 1 Length

00 03 02    ; certificate 1 length 0x302 (770) bytes

Certificate 1

30 82 02 fe 30 82 01 e6 a0 03 02 01 02 02 10 20 9e 8f 8c 08 72 c1 bf 45 ad f8 df 73 1f 21 47 30 0d 06 09 2a 86 48 86 f7 0d 01 01 0b 05 00 30 3b 31 39 30 37 06 03 55 04 03 1e 30 00
53 00 53 00 4c 00 5f 00 53 00 65 00 6c 00 66 00 5f 00 53 00 69 00 67 00 6e 00 65 00 64 00 5f 00 46 00 61 00 6c 00 6c 00 62 00 61 00 63 00 6b 30 1e 17 0d 31 39 31 30 31 36 31 34 30 31 30 36 5a
17 0d 34 39 31 30 31 36 31 34 30 31 30 36 5a 30 3b 31 39 30 37 06 03 55 04 03 1e 30 00 53 00 53 00 4c 00 5f 00 53 00 65 00 6c 00 66 00 5f 00 53 00 69 00 67 00 6e 00 65 00 64 00 5f 00 46 00 61
00 6c 00 6c 00 62 00 61 00 63 00 6b 30 82 01 22 30 0d 06 09 2a 86 48 86 f7 0d 01 01 01 05 00 03 82 01 0f 00 30 82 01 0a 02 82 01 01 00 b6 dc 05 25 3e 1e 41 82 f6 b4 bc 97 1c c5 1e 02 a6 bb 1a
c7 34 ed 51 cc 65 a6 60 72 0c 3c 0e ac 57 df 4f df 18 15 14 6f e0 9c d4 39 36 a1 8e 5b 17 85 0a 93 c2 78 02 70 89 f5 35 18 55 37 14 b0 6e 4c cd 0d e7 a0 81 33 c8 42 bc 55 f6 71 fa 4e d2 98 5e
63 5b 32 a6 c3 04 21 3d 22 df 8c ee a7 6e 53 4b be 3d e6 39 c9 90 c6 a9 ed df 7a a8 46 83 b9 7c f1 03 7c 45 b1 be 42 ee 71 aa bc d6 7b 27 41 f3 b8 c4 16 2a 18 8a 38 4c 19 51 25 78 56 ce 12 75
d0 eb 29 19 c5 70 b1 c0 13 18 77 77 a6 9b 47 1f fe 1f 27 5a 50 fb 3f 73 03 c3 a0 5d 2f f4 dd 37 fd df 87 f0 80 e9 de a7 16 df ee b8 00 c2 1e 68 49 19 ce 44 f4 af ac bf d3 5f b8 1c 64 d5 95 1f
8b 32 c1 5a 80 a8 21 2d 2c 66 b8 7e 29 13 11 f8 1f e1 e9 0f 68 4e 0a b8 1b 3c f3 28 6f b7 46 85 b1 17 c9 c6 82 e4 3c 28 d4 6d d9 5b dd 02 03 01 00 01 30 0d 06 09 2a 86 48 86 f7 0d 01 01 0b 05
00 03 82 01 01 00 3e f8 c8 54 30 c1 c1 12 5e eb 01 e5 50 41 57 55 de 9e 2e 69 2a 14 fa 9a 5a 9f d3 c3 1c 28 49 28 b2 c2 e0 cf 3d 7f 48 88 3c 25 e3 30 b7 93 e1 5c a5 9c 6f 41 b0 fb 55 67 69 e5
c5 0e 46 7c e6 2b b4 2b d8 ac 42 62 1e 18 01 69 cd e0 85 59 77 19 bd f9 17 fc 1d 10 ba 8a 93 aa 88 8e e9 73 2c 56 2e d1 5e 22 ca 20 61 4c 03 f0 70 9f a7 a7 ed 16 3a fa 00 0e 9e 39 e8 6b 99 a1
72 cb 48 b8 88 26 2d 9d 3f d6 48 70 f0 a5 51 ee c0 a9 0a e8 cf e6 ed 27 79 4d f1 23 a6 f4 64 a0 72 04 2a 3a b9 30 aa 84 e4 d2 26 9d 8c 3e b5 fe 87 ef 2c 21 c7 4e 38 1f b7 b1 25 69 68 d8 42 b1
8d 25 f3 90 14 78 d2 39 9f 0f 17 2a fc 24 2e 3b 3b 40 99 c6 fb c9 0a b0 af cd 66 f1 c9 84 c5 be dc e9 38 bb 36 2d be 6b db db 86 95 96 c6 84 4b 7d b0 a1 ea f7 42 b2 56 93 ec e9 fd 86 8f a5 ad
f0 57 63 78 d0 5a 0c 00 01 67 03 00 18 61 04 6d f6 56 8c e9 8a 82 29 0a d4 da ea be 89 52 30 bf cd 49 66 77 7f 18 12 b1 75 44 96 d1 9e 6d 15 da 69 ba a7 7c ee 6f ab 96 ea b0 0d 64 0f c4 22 ba
d7 9f 54 71 15 42 84 50 95 a4 ad 16 15 71 56 a7 0a 80 60 2d 83 86 80 0a 96 7b 30 6a aa b7 be 8f 45 9b 96 1a 5c b2 d3 e6 28 6f c4 e4 bb 70 ce 01 00 23 e6 8d 9e bf fc 90 66 f8 07 b6 6b 44 bb 03
b5 3b fd 72 fe 7b 5a e6 15 5d 10 d6 51 22 b8 44 8d 27 85 20 53 04 fb 06 32 b7 03 9e ba 7a 32 62 f1 61 25 8a c4 42 3a 02 1f be cf 92 4d 6e 48 d3 48 8b 8f 03 dc 66 d8 8b 63 21 be 6f b0 53 1c 00
37 4e 7e 4f e8 1e ba d2 c4 45 0f c0 13 ed 71 ee 47 f3 4c 0d 1d af d5 d5 bb 52 78 4b b3 55 70 4e bc a4 f1 da 4a ec 60 7a af 81 23 49 16 57 db 8e 8b 22 65 91 3f 5b 1c 0a 13 d0 e4 b0 a4 d3 10 33
a3 bd c9 39 b6 ea 9b 73 67 11 49 ed 4f 6d 71 ae ce a3 2a 3a 6d 07 d9 d6 24 89 27 2d e6 5a e9 e1 e0 6c 47 5c c1 c0 72 01 e6 63 a6 06 7f 0d b0 16 68 7d 82 49 86 df 38 9b b4 95 5a 95 f8 e8 03 e9
63 83 7c c4 07 62 a1 f5 ce 48 0a 0c 6e 9c da e5 bf 5e e4 56 a1 de 98 80 e5 65 df eb 25 7c 38 97 8a e1 8d a3 c9 a8 a6 f8 bb 77 06 16 ec 21 8a 5c 0e 0e 00 00 00

The certificate can then be decoded:

Issued by: SSL_Self_Signed_Fallback
Issued to: SSL_Self_Signed_Fallback
Valid from: 2019-10-16 14:01:06 UTC
Valid to: 2049-10-16 14:01:06 UTC
SHA1 fingerprint: 6640479c9cac2b7ca63c9708c71a9245b951b4c7

Of course that requires a network spying tool, and a lot of work.

As opposed to most of TLS clients (e.g. http, smtp, pop3, ftp, InternetQueryOption) allow you to view the client certificate directly:

Bonus Chatter - The TLS Nonce Easter Egg

The random 28-byte NONCE, generated by SQL Server and sent to the client, contains an easter-egg (of sorts):

; random.random_bytes 28-bytes 
e7 c2 8d f3 3e d5 e4 6d ec 9a b9 7f af c1    çÂ.ó>Õämìš¹.¯Á
43 ad d3 51 41 b7 44 4f 57 4e 47 52 44 00    C.ÓQA·DOWNGRD.

It ends with "DOWNGRD\0".

That's a feature of TLS 1.3 designed to help a client detect a downgrade attack.

If the server is capable of supporting TLS 1.3, but it has been asked by the client to use something lower, it adds "DOWNGRD" to the random nonce:

DOWNGRD\0: If the server was asked to negotiate TLS 1.1 or earlier
DOWNGRD\1: If the server was asked to negotiate TLS 1.2

This way the client can detect if a man-in-the-middle intercepted the client, and altered the list of protocols supposedly supported by the client to trick the client into using TLS 1.2 (weaker), or TLS 1.1 or earlier (broken).

Bonus Reading

How does TLS 1.3 protect against downgrade attacks? ^🕗
OpenSSL: Fetching SQL Server public certificate (asking about OpenSSL)
How can client retrieve SQL Server public SSL certificate? (Asking for anything; including network sniffing)

p.s. And, of course, this question does not belong on dba.stackexchange.com because this is a programming question (e.g. ADO.net, OLEDB), and not a DBA question.

Bonus - Decoding network traffic

Bonus Chatter - The TLS Nonce Easter Egg

Bonus Reading

Recommended topics

Hot tags