A terrible way to connect to MS SQL Server and dump the certificate as a PEM

get_tds_cert.py

import sys
import pprint
import struct
import socket
import ssl
from time import sleep 

# Standard "HELLO" message for TDS
prelogin_msg = bytearray([      0x12, 0x01, 0x00, 0x2f, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x1a, 0x00, 0x06, 0x01, 0x00, 0x20,
                                0x00, 0x01, 0x02, 0x00, 0x21, 0x00, 0x01, 0x03, 0x00, 0x22, 0x00, 0x04, 0x04, 0x00, 0x26, 0x00,
                                0x01, 0xff, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00 ])

# Prep Header function
def prep_header(data):
        data_len = len(data)
        prelogin_head = bytearray([ 0x12, 0x01 ])
        header_len = 8
        total_len = header_len + data_len
        data_head = prelogin_head + total_len.to_bytes(2, 'big')
        data_head += bytearray([ 0x00, 0x00, 0x01, 0x00])
        return data_head + data
        
def read_header(data):
    if len(data) != 8:
        raise ValueError("prelogin header is > 8-bytes", data)
    
    format = ">bbhhbb"
    sct = struct.Struct(format)
    unpacked = sct.unpack(data)
    return {    "type": unpacked[0], 
                "status": unpacked[1],
                "length": unpacked[2],
                "channel": unpacked[3],
                "packet": unpacked[4],
                "window": unpacked[5]
    }
    
tdspbuf = bytearray()
def recv_tdspacket(sock):
    global tdspbuf
    tdspacket = tdspbuf
    header = {}
    
    for i in range(0,5):
        tdspacket += sock.recv(4096)
        print("\n# get_tdspacket: {}, tdspacket len: {} ".format(i, len(tdspacket)))
        if len(tdspacket) >= 8:
            header = read_header(tdspacket[:8])
            print("# Header: ", header)
            if len(tdspacket) >= header['length']:
                tdspbuf = tdspacket[header['length']:]
                print("# Remaining tdspbuf length: {}\n".format(len(tdspbuf)))
                return header, tdspacket[8:header['length']]
                
        sleep(0.05)

# Ensure we have a commandline
if len(sys.argv) != 3:
        print("Usage: {} <hostname> <port>".format(sys.argv[0]))
        sys.exit(1)

hostname = sys.argv[1]
port = int(sys.argv[2])


# Setup SSL
if hasattr(ssl, 'PROTOCOL_TLS'):
    sslProto = ssl.PROTOCOL_TLS
else:
    sslProto = ssl.PROTOCOL_SSLv23
    
sslctx = ssl.SSLContext(sslProto)
sslctx.check_hostname = False
tls_in_buf = ssl.MemoryBIO()
tls_out_buf = ssl.MemoryBIO()

# Create the SSLObj connected to the tls_in_buf and tls_out_buf
tlssock = sslctx.wrap_bio(tls_in_buf, tls_out_buf)

# create an INET, STREAMing socket
s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
s.setblocking(0)
s.settimeout(1)

# Connect to the SQL Server
s.connect(( hostname, port ))

# Send the first TDS PRELOGIN message
s.send(prelogin_msg)

# Get the response and ignore. We will try to negotiate encryption anyway. 
header, data = recv_tdspacket(s)
while header['status']==0:
    header, ext_data = recv_tdspacket(s)
    data += ext_data
    

print("# Starting TLS handshake loop..")
# Craft the packet
for i in range(0,5):
    try:
        tlssock.do_handshake()
        print("# Handshake completed, dumping certificates")
        peercert = ssl.DER_cert_to_PEM_cert(tlssock.getpeercert(True))
        print(peercert)
        sys.exit(0)
    except ssl.SSLWantReadError as err:
        # TLS wants to keep shaking hands, but because we're controlling the R/W buffers it throws an exception
        print("# Shaking ({}/5)".format(i))
    
    tls_data = tls_out_buf.read()
    s.sendall(prep_header(tls_data))
    # TDS Packets can be split over two frames, each with their own headers.
    # We have to concat these for TLS to handle nego properly
    header, data = recv_tdspacket(s)
    while header['status']==0:
        header, ext_data = recv_tdspacket(s)
        data += ext_data
    
    tls_in_buf.write(data)
    
print("# Handshake did not complete / exiting")

I used the script in this form to verify that one of our SQL server has indeed dropped the SSL certificate and has generated a fallback self signed SSL certificate.

python get_tds_cert.py sql.server.name 1433 > sql_cert.crt

Our build pipeline failures events had coincided with the beginning of the validity of the certificate:

cat sql_cert.crt | openssl x509 -text

   Version: 3 (0x2)
   Serial Number:
       54:50:57:70:4a:a5:9d:8f:43:f7:00:4a:69:96:06:4f
   Signature Algorithm: sha256WithRSAEncryption
   Issuer: CN = SSL_Self_Signed_Fallback
   Validity
       Not Before: Feb 21 08:35:47 2021 GMT
       Not After : Feb 21 08:35:47 2051 GMT
   Subject: CN = SSL_Self_Signed_Fallback
   Subject Public Key Info:
       Public Key Algorithm: rsaEncryption
           RSA Public-Key: (2048 bit)

Output:

get_tdspacket: 0, tdspacket len: 43
Header: {'type': 4, 'status': 1, 'length': 43, 'channel': 0, 'packet': 1, 'window': 0}
Remaining tdspbuf length: 0

Starting TLS handshake loop..
Shaking (0/5)

get_tdspacket: 0, tdspacket len: 1184
Header: {'type': 18, 'status': 1, 'length': 1184, 'channel': 0, 'packet': 0, 'window': 0}
Remaining tdspbuf length: 0

Shaking (1/5)

get_tdspacket: 0, tdspacket len: 59
Header: {'type': 18, 'status': 1, 'length': 59, 'channel': 0, 'packet': 0, 'window': 0}
Remaining tdspbuf length: 0

Handshake completed, dumping certificates

Related posts and articles:

• How can SQL Server client get information on TLS certificate being used?
• Fetching SQL Server public certificate
• The certificate received from the remote server was issued by an untrusted certificate authority error when you connect to SQL Server
• Where Does SQL Server Store Its Certificates
• Setting up SSL encryption for SQL Server using certificates – Issues, tips & tricks