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

Scherlac commented Feb 27, 2021

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 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:
   Signature Algorithm: sha256WithRSAEncryption
   Issuer: CN = SSL_Self_Signed_Fallback
       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)

Copy link

Scherlac commented Feb 27, 2021


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

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