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")

Finally..

This should allow you get a BASE64 encoded PEM certificate from an SQL 2012 server.
Use at your own risk!

Hi, thanks for the script! There is a small error on line 62 hostname = sys.argv[1] (extra indentation). I was able to get the same results using openssl like this: openssl s_client -showcerts -connect <hostname>:<port> </dev/null 2>/dev/null|openssl x509 -outform PEM >dbcertfile.pem as suggested somewhere. I would like to ask you, have you try to use the downloaded certificate to connect to an Azure SQL database using pytds? I've been trying to do that but always failing with 'SSL routines', 'tls_process_server_certificate', 'certificate verify failed'.

get same error as @eydelrivero with the above extra indentation

Traceback (most recent call last):
  File "get_tds_cert.py", line 86, in <module>
    s.connect(( hostname, port ))
NameError: name 'hostname' is not defined

also looks like MS SQL has a fallback SSL cert: CN = SSL_Self_Signed_Fallback

Found a bug:

Line 44: tdspacket += sock.recv(4096)

Needs to be moved into the for loop just below it.

As written, if the TDS response spans multiple IP packets, and the OS/libraries do not merge them together, it will fail.

I found this, and was able to reliably reproduce it using windows native python 3.8.3 and a sql server using a public certificate. Interestingly enough, it works as-is using the ubuntu-within-windows python v3.5.2.

Thank you for this code. It's going to be quite helpful in the project I'm working on.

@TiloGit (and people coming here in the future):

Line 62 is incorrectly indented. It needs to have no indentation:

-        hostname = sys.argv[1]
+hostname = sys.argv[1]

wow, I wrote this a long time ago..
i've updated the gist with @eydelrivero / @Erik-Horn and @anton-johansson's fixes..
Glad folks find it useful, sorry for the crappiness of the code.

Don’t be sorry, your code saved me some time. :)

$ python get_tds_cert.py 10.63.192.51 1433
Traceback (most recent call last):
  File "get_tds_cert.py", line 74, in <module>
    tls_in_buf = ssl.MemoryBIO()
AttributeError: 'module' object has no attribute 'MemoryBIO'
$  python --version
Python 2.7.16

@brandonros: Sorry for the late reply, but try using python3.

Thank you ! In my company, we use your script to monitor the health of our MSSQL instances.

This is great!

It would be fairly simple to make openssl s_client handle this case (e.g. openssl s_client -starttls mssql or openssl s_client -starttls tds depending on how you think the TLS handshaking protocol should be named 😄)

See openssl/openssl@a2d9cfb as a past case where the MySQL handshake was added (openssl s_client -starttls mysql).

Thank you! This worked great for me. I can confirm it works with SQL Server 2019.

Thank you so much! This was just what I needed to troubleshoot some SSL issues with an Azure SQL DB. I did need to bump up the socket timeout as I was getting some intermittent issues like this:

Traceback (most recent call last):
  File "/home/sanelson/src/sql_ssl_test/get_tds_cert.py", line 117, in <module>
    header, ext_data = recv_tdspacket(s)
  File "/home/sanelson/src/sql_ssl_test/get_tds_cert.py", line 45, in recv_tdspacket
    tdspacket += sock.recv(4096)
TimeoutError: timed out

I changed s.settimeout(1) to s.settimeout(5) and then it worked perfectly.