Skip to content

Instantly share code, notes, and snippets.

Last active June 28, 2024 20:35
Show Gist options
  • Save lnattrass/a4a91dbf439fc1719d69f7865c1b1791 to your computer and use it in GitHub Desktop.
Save lnattrass/a4a91dbf439fc1719d69f7865c1b1791 to your computer and use it in GitHub Desktop.
A terrible way to connect to MS SQL Server and dump the certificate as a PEM
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']]
# Ensure we have a commandline
if len(sys.argv) != 3:
print("Usage: {} <hostname> <port>".format(sys.argv[0]))
hostname = sys.argv[1]
port = int(sys.argv[2])
# Setup SSL
if hasattr(ssl, 'PROTOCOL_TLS'):
sslProto = ssl.PROTOCOL_TLS
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)
# Connect to the SQL Server
s.connect(( hostname, port ))
# Send the first TDS PRELOGIN message
# 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):
print("# Handshake completed, dumping certificates")
peercert = ssl.DER_cert_to_PEM_cert(tlssock.getpeercert(True))
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 =
# 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
print("# Handshake did not complete / exiting")
Copy link


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

Copy link

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'.

Copy link

TiloGit commented Aug 6, 2019

get same error as @eydelrivero with the above extra indentation

Traceback (most recent call last):
  File "", 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

Copy link

Erik-Horn commented Jun 5, 2020

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.

Copy link

@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]

Copy link

lnattrass commented Jan 13, 2022

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.

Copy link

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

Copy link

brandonros commented May 26, 2022

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

Copy link

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

Copy link

nmrr commented Jun 24, 2024

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

Copy link

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment