Connection to MSSQL Server with {ODBC Driver 17 for SQL Server} with AD system user fails

Question

I'm trying to connect a windows system user in my SQLAlchemy framework with an MSSQL Server. I'm developing on an Max M1 with FreeTDS Driver with the same user. To connect, I set the authentification to ["authentication"] = "ActiveDirectoryPassword" and everthing works like a charm. When I try to run the same code on a windows machine with ODBC Driver insead of FreeTDS (with a different user than the user with the database access, so I can't simply use the trusted_connection="yes" argument) the connection is refused because the username is deleted in the connection string.

InterfaceError('(pyodbc.InterfaceError) (\'28000\', "[28000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Login failed for user \'\'. (18456) (SQLDriverConnect); [28000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Login failed for user \'\'. (18456)")')

I create my collection string like this:

from sqlalchemy.engine import URL
from sqlalchemy import create_engine

if "MacBook" in socket.gethostname():
    driver = "FreeTDS"
else:
    driver = "ODBC Driver 17 for SQL Server"
self.db_api = "pyodbc"

query_dict = {
        "driver": driver,
        "TrustServerCertificate": "yes", 
        "authentication": "ActiveDirectoryPassword" 
    }


connection_url = URL.create(
    f"mssql+{self.db_api}",
    username=self.user,
    password=self.password,
    host=self.server,
    port=self.port,
    database=self.database,
    query=query_dict,
)
self.engine = create_engine(connection_url)
con=Connection(self.engine)

On the mac I use Python 3.9.12 and SQLAlchemy==1.4.42. On the windows machine is a Edition Windows Server 2022 Datacenter Version 21H2 Installed on ‎12/‎20/‎2022 OS build 20348.1487 with Python 3.9.2 and SQLAlchemy==1.4.42.

I tried changing the driver to "ODBC Driver 18 for SQL Server" with the same outcome. When setting the "TrustServerCertificate" to "no " I get another error:

OperationalError("(pyodbc.OperationalError) ('08001', '[08001] [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: The certificate chain was issued by an authority that is not trusted.\\r\\n (-2146893019) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection

I also tried disabling the Encryption and all the other authentifications provided here without any effect. Leaving authentification method blank:

 '28000', "[28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'xxx\\xxxx'

Setting authentification method to "ActiveDirectoryIntegrated":

('FA002', "[FA002] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot use Authentication option 'ActiveDirectoryIntegrated' with User or Password options.

Answer 1

Found a solution for my problem in this answer! Not sure if I stumbled upon a bug in the pyodbc package or what but changing the db_api to pymssql did the trick! My connection string now is as follows:

if "MacBook" in socket.gethostname():
    driver = "FreeTDS"
    self.db_api = "pyodbc"
    query_dict = {
        "driver": driver,
        "TrustServerCertificate": "yes", 
        "authentication": "ActiveDirectoryPassword" 
    }
else:
    driver = "ODBC Driver 17 for SQL Server"
    self.db_api = "pymssql"
    query_dict = {
    }

connection_url = URL.create(
    f"mssql+{self.db_api}",
    username=self.user,
    password=self.password,
    host=self.server,
    port=self.port,
    database=self.database,
    query=query_dict,
)