TrustServerCertificate=yes not honored

[sakvaka]

Describe the bug

I am working behind a corporate security proxy which signs internet traffic using a self-signed certificate. I tried to use msql-python to connect to a SQL Server 2022 instance and use TrustServerCertificate=yes to skip the SSL/TLS validation, but I suspect that it isn't being honored; the module reports a certificate verify error after a successful authentication.

When I use ODBC Driver 18 for SQL Server to build the connection myself, the same parameter TrustServerCertificate=yes works (and using no gives a certificate validation error).

I also tried the options Yes, True, and true, with no success. The documentation examples say however that the syntax is yes.

The full exceptions details below (message and stack trace).

Exception message: RuntimeError: [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: [error:0A000086:SSL routines::certificate verify failed:self-signed certificate]

Stack trace:
Traceback (most recent call last):
  File "<python-input-19>", line 4, in <module>
    conn = mssql_python.connect(conn_str)
  File "/home/XXX/miniconda3/envs/python3.14/lib/python3.14/site-packages/mssql_python/db_connection.py", line 46, in connect
    conn = Connection(
        connection_str,
    ...<3 lines>...
        **kwargs
    )
  File "/home/XXX/miniconda3/envs/python3.14/lib/python3.14/site-packages/mssql_python/connection.py", line 239, in __init__
    self._conn = ddbc_bindings.Connection(
                 ~~~~~~~~~~~~~~~~~~~~~~~~^
        self.connection_str, self._pooling, self._attrs_before
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
RuntimeError: [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: [error:0A000086:SSL routines::certificate verify failed:self-signed certificate]

To reproduce

Reproducing requires that you are behind a corporate proxy that uses a self-signed certificate to sign off network traffic. I have appropriately set the HTTPS_PROXY, HTTP_PROXY, CURL_CA_BUNDLE, REQUESTS_CA_BUNDLE environment variables.

import mssql_python

conn_str = "Server=REDACTED;Database=REDACTED;Authentication=ActiveDirectoryInteractive;TrustServerCertificate=yes;"
conn = mssql_python.connect(conn_str) # opens web browser and authenticates successfully, but then prints the error listed above

# -----------------------
import pyodbc

# I have aquired a Kerberos ticket, so that Trusted_Connection works
connection_string = (
    'DRIVER={ODBC Driver 18 for SQL Server};'
    'SERVER=REDACTED;'
    'DATABASE=REDACTED;'
    'Trusted_Connection=yes;'
    'TrustServerCertificate=yes;'
)

cnxn = pyodbc.connect(connection_string) # succeeds

Expected behavior

I expect the parameter TrustServerCertificate=yes; to be honored by the DDBC so that the driver doesn't complain about a self-signed certificate in the chain.

Further technical details

Python version: 3.14.1
SQL Server version: SQL Server 2022
MSSQL-Python: 1.0.0
Operating system: Ubuntu 20.04.6 on Windows Subsystem for Linux

Additional context
Reproducing requires that you are behind a corporate proxy that uses a self-signed certificate to sign off network traffic.

[sumitmsft]

Hi @sakvaka

Thanks for reporting this issue.

The issue you reported sounds like TrustServerCertificate is being honored correctly by mssql-python and passed to the ODBC driver, but the error seems to be occurring at the SSL/TLS layer during certificate verification, which happens before the ODBC driver can apply the TrustServerCertificate setting.

Here is our hypothesis of what may be happening here:

The error:
`RuntimeError: [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: [error:0A000086:SSL routines::certificate verify failed:self-signed certificate]'

is an OpenSSL error occurring at the transport layer, likely because your corporate proxy's self-signed certificate is being encountered during the initial SSL handshake.

There is a subtle difference between the way pyodbc handles AAD Interactive auth and how mssql-python does it. The key difference is when and where the SSL certificate verification happens in each case:

pyodbc:
pyodbc ==> ODBC Driver ==> [SQL Server with TrustServerCertificate=yes]
**Direct connection, TrustServerCertificate honored

mssql-python:
mssql-python → azure-identity library → [HTTPS to login.microsoftonline.com]
Fails here at SSL verification
Never reaches SQL Server
TrustServerCertificate never applied

TrustServerCertificate=yes only affects the ODBC Driver's SSL connection to SQL Server. It does NOT affect:

• Python's azure-identity library
• Python's requests library
• Python's urllib3 library
• Any HTTPS connections made by Python code before the ODBC connection

A possible solution we could think of is:

1. Make Ubuntu trust your corporate certificate (for system tools, curl, etc.)
2. Let Python's requests library use Ubuntu's certificate store instead of its own bundled certifi package
3. When azure-identity makes HTTPS calls to login.microsoftonline.com through your proxy, it should now trust your corporate certificate.

Note: Self-signed CA certificate - A root certificate that can sign other certificates (what your proxy uses). This is what you need.

If you can do these steps, I shall let you know the exact steps\commands that may help here.

Thanks,
Sumit

[sakvaka]

I have the cert in my system's certificate store, and my requests library is configured to use it. However, I think the real reason might be that TrustServerCertificate is being cleared from the connection parameters after authentication, because it's listed as one of the sensitive keys to remove before connecting to the DB:

mssql-python/mssql_python/auth.py

Lines 165 to 171 in 5c77e3f

	exclude_keys = [
	"uid=",
	"pwd=",
	"encrypt=",
	"trustservercertificate=",
	"authentication=",
	]

[saurabh500]

In this repro, the access token retrieval succeeded, and the error is from the ODBC driver.

The root cause from @sakvaka makes sense. We don't need to remove "trustservercetificate" from the connection string attribute list before passing it to ODBC. Also "encrypt" shouldn't be removed from the incoming connection string either. It should be passed to ODBC as-is.

Other excluded keys like uid/pwd/authentication, since they will cause ODBC to try to retrieve an access token, or perform non-entra id auth.

[saurabh500]

@sakvaka out of curiosity, are you using entra id auth with an on prem sql server or with azure sql/mi.
I am wondering why certificate check in sql connectivity is failing if the sql server is in azure.

You have mentioned sql 2022 so I assume this is onprem. But wanted to double check