Lesson Learned #522: Troubleshooting TLS and Cipher Suites with Python connecting to Azure SQL DB

A few days ago, we were working on a service request where our customer was experiencing several issues connecting to Azure SQL Database due to TLS version and cipher suite mismatches when using Python and ODBC Driver 18.

Although we were able to get that information through a network trace, I would like to share things that I learned. 

Using the library SSL in Python allows to establish a TLS/SSL context where I can control the TLS version and specify or inspect the cipher suite. 

Here’s a small script that demonstrates how to connect to the Azure SQL Gateway over port 1433 and inspect the TLS configuration:

import ssl
import socket

#ServerName to connect (Only Gateway)
host = 'servername.database.windows.net'
port = 1433

# TLS context 
context = ssl.create_default_context()
print("Python uses:", ssl.OPENSSL_VERSION)
context.minimum_version = ssl.TLSVersion.TLSv1_2
context.maximum_version = ssl.TLSVersion.TLSv1_2
context.check_hostname = True
context.verify_mode = ssl.CERT_REQUIRED

context.load_default_certs()
# Testing the connection.
with socket.create_connection((host, port)) as sock:
    with context.wrap_socket(sock, server_hostname=host) as ssock:
        print("TLS connection established.")
        print("TLS version:", ssock.version())
        print("Cipher suite:", ssock.cipher())
        # CN (Common Name)
        cert = ssock.getpeercert()
        try:
            cn = dict(x[0] for x in cert['subject'])['commonName']
            print(f"\n Certificate CN: {cn}")
        except Exception as e:
            print(" Error extracting CN:", e)

        print("Valid from :", cert.get('notBefore'))
        print("Valid until:", cert.get('notAfter'))

Using this script I was able to:

• Enforce a specific TLS version by setting minimum_version and maximum_version , for example, (1.2 or 1.3)
• Retrieve the cipher suite negotiated. 
• Inspect the details of the certificate. 

Enjoy!