问题

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 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](https://learn.microsoft.com/en-us/sql/connect/odbc/using-azure-active-directory?view=sql-server-ver16) 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.

<details>
<summary>英文:</summary>

I&#39;m trying to connect a windows system user in my SQLAlchemy framework with an MSSQL Server. 
I&#39;m developing on an Max M1 with FreeTDS Driver with the same user. To connect, I set the authentification to [&quot;authentication&quot;] = &quot;ActiveDirectoryPassword&quot; 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&#39;t simply use the trusted_connection=&quot;yes&quot; 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](https://learn.microsoft.com/en-us/sql/connect/odbc/using-azure-active-directory?view=sql-server-ver16) 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.


</details>


# 答案1
**得分**: 0

在[这个][1]答案中找到了解决我的问题的方法！
不确定是否遇到了pyodbc包中的错误或其他问题，但将db_api更改为pymssql就解决了问题！
我的连接字符串现在如下：

```python
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,
)

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,
)