[SqlAlchemy][mssql][tls1.2] How to enforce TLS 1.2 for SqlAlchemy connecting to MS SQL Server using pyodbc from windows?

Question

Recent hardening standards have made us disable TLS 1.0 and 1.1.

Registry Settings for TLS 1.0 and 1.1: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Client

Now the following code:

from sqlalchemy import create_engine

db = create_engine(
    "mssql+pyodbc://__OUR_SERVER_NAME__/__OUR_DATABSE_NAME__?driver=SQL+Server+Native+Client+11.0&Trusted_Connection=yes&Encrypt=yes&TrustServerCertificate=Yes&ssl=True",
    connect_args={
        # 'sslmode': 'require',    # did not work
        # 'tls-version': 'tls1.2', # did not work
        # 'ssl': True,             # did not work
    },
    echo=True,
)

with db.begin() as conn:
    conn.execute("SELECT TOP 5 * FROM sys.tables")

Will throw this exception on db.begin():

Exception has occurred: OperationalError (pyodbc.OperationalError) ('08001', '[08001] [Microsoft][SQL Server Native Client 11.0]Encryption not supported on the client. (21) (SQLDriverConnect); [08001] [Microsoft][SQL Server Native Client 11.0]SSL Provider: The client and server cannot communicate, because they do not possess a common algorithm.\r\n (-2146893007); [08001] [Microsoft][SQL Server Native Client 11.0]Client unable to establish connection (21); [08001] [Microsoft][SQL Server Native Client 11.0]Invalid connection string attribute (0); [08001] [Microsoft][SQL Server Native Client 11.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (-2146893007)')

TLS 1.2 is enabled and works fine with Azure Data Studio on the same client server.

Even python shows openssl version is resonable:

import ssl
print(f'SSL Version = {ssl.OPENSSL_VERSION}')

which outputs:

SSL Version = OpenSSL 1.1.1d  10 Sep 2019

Python version:

python --version
Python 3.7.6

I have tried looking at SSLContext but can't seem to find how to make it work with SqlAlchemy.

Any help would be greatly appreciated!!!

Possible duplicate question (with less info): Connecting to SQL Server using pyodc with TLS 1.2

Rohit Mistry Rohit Mistry · Accepted Answer · 2021-01-04T17:08:33

Further investigation showed an issue with the ODBC driver.

The app was on Windows Server 2012 with ODBC version:

SQL Server Native Client 11.0

Version: 2011.110.3000.00

Date: 10/20/2012

Updating the driver to ODBC Driver 17 for SQL Server with new connection string worked:

from sqlalchemy import create_engine

db = create_engine(
    "mssql+pyodbc://__OUR_SERVER_NAME__/__OUR_DATABSE_NAME__?driver=ODBC+Driver+17+for+SQL+Server&Trusted_Connection=yes&Encrypt=yes&TrustServerCertificate=Yes&ssl=True",
    connect_args={
        # 'sslmode': 'require',    # did not work
        # 'tls-version': 'tls1.2', # did not work
        # 'ssl': True,             # did not work
    },
    echo=True,
)

[SqlAlchemy][mssql][tls1.2] How to enforce TLS 1.2 for SqlAlchemy connecting to MS SQL Server using pyodbc from windows?

1 Answers