Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
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

lnattrass commented Feb 14, 2017


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

Copy link

eydelrivero commented Jan 26, 2018

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

anton-johansson commented Jan 13, 2022

@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

anton-johansson commented Jan 13, 2022

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

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