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 = {}
tdspacket += sock.recv(4096)
for i in range(0,5):
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")

This comment has been minimized.

Copy link
Owner Author

commented Feb 14, 2017


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


This comment has been minimized.

Copy link

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


This comment has been minimized.

Copy link

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.