连接到MSSQL Server使用{ODBC驱动程序 17 for SQL Server}和AD系统用户失败。

huangapple
go评论140阅读模式
英文:

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

问题

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:

  1. from sqlalchemy import create_engine
  3. if "MacBook" in socket.gethostname():
  4.     driver = "FreeTDS"
  5. else:
  6.     driver = "ODBC Driver 17 for SQL Server"
  7. self.db_api = "pyodbc"
  9. query_dict = {
  10.         "driver": driver,
  11.         "TrustServerCertificate": "yes", 
  12.         "authentication": "ActiveDirectoryPassword" 
  13.     }
  15. connection_url = URL.create(
  16.     f"mssql+{self.db_api}",
  17.     username=self.user,
  18.     password=self.password,
  19.     host=self.server,
  20.     port=self.port,
  21.     database=self.database,
  22.     query=query_dict,
  23. )
  24. self.engine = create_engine(connection_url)
  25. con=Connection(self.engine)```
  27. On the mac I use Python 3.9.12 and SQLAlchemy==1.4.42.
  28. On the windows machine is a
  29. Edition Windows Server 2022 Datacenter
  30. Version 21H2
  31. Installed on ‎12/‎20/‎2022
  32. OS build 20348.1487
  33. with Python 3.9.2 and SQLAlchemy==1.4.42.
  35. I tried changing the driver to "ODBC Driver 18 for SQL Server" with the same outcome.
  36. When setting the "TrustServerCertificate" to "no " I get another error:
  38. ```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")```
  40. 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.
  41. Leaving authentification method blank:
  43. '28000', "[28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'xxx\xxxx'
  45. Setting authentification method to "ActiveDirectoryIntegrated":
  47. ('FA002', "[FA002] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot use Authentication option 'ActiveDirectoryIntegrated' with User or Password options.
  49. <details>
  50. <summary>英文:</summary>
  52. I&#39;m trying to connect a windows system user in my SQLAlchemy framework with an MSSQL Server. 
  53. 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) (&#39;28000&#39;, "[28000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Login failed for user &#39;&#39;. (18456) (SQLDriverConnect); [28000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Login failed for user &#39;&#39;. (18456)")')

  2. 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)

  1. On the mac I use Python 3.9.12 and SQLAlchemy==1.4.42. 
  2. On the windows machine is a 
  3. Edition	Windows Server 2022 Datacenter
  4. Version	21H2
  5. Installed on	‎12/‎20/‎2022
  6. OS build	20348.1487
  7. with Python 3.9.2 and SQLAlchemy==1.4.42. 
  10. I tried changing the driver to &quot;ODBC Driver 18 for SQL Server&quot; with the same outcome. 
  11. When setting the &quot;TrustServerCertificate&quot; to &quot;no &quot; 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

  1. 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.
  2. Leaving authentification method blank: 
  3.    
  5.      &#39;28000&#39;, &quot;[28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user &#39;xxx\\xxxx&#39;
  7. Setting authentification method to &quot;ActiveDirectoryIntegrated&quot;: 
  8.  
  10.     (&#39;FA002&#39;, &quot;[FA002] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot use Authentication option &#39;ActiveDirectoryIntegrated&#39; with User or Password options.
  13. </details>
  16. # 答案1
  17. **得分**: 0
  19. 在[这个][1]答案中找到了解决我的问题的方法！
  20. 不确定是否遇到了pyodbc包中的错误或其他问题，但将db_api更改为pymssql就解决了问题！
  21. 我的连接字符串现在如下：
  23. ```python
  24. if "MacBook" in socket.gethostname():
  25.     driver = "FreeTDS"
  26.     self.db_api = "pyodbc"
  27.     query_dict = {
  28.         "driver": driver,
  29.         "TrustServerCertificate": "yes",
  30.         "authentication": "ActiveDirectoryPassword"
  31.     }
  32. else:
  33.     driver = "ODBC Driver 17 for SQL Server"
  34.     self.db_api = "pymssql"
  35.     query_dict = {
  36.     }
  38. connection_url = URL.create(
  39.     f"mssql+{self.db_api}",
  40.     username=self.user,
  41.     password=self.password,
  42.     host=self.server,
  43.     port=self.port,
  44.     database=self.database,
  45.     query=query_dict,
  46. )
英文:

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:

  1. if &quot;MacBook&quot; in socket.gethostname():
  2.     driver = &quot;FreeTDS&quot;
  3.     self.db_api = &quot;pyodbc&quot;
  4.     query_dict = {
  5.         &quot;driver&quot;: driver,
  6.         &quot;TrustServerCertificate&quot;: &quot;yes&quot;, 
  7.         &quot;authentication&quot;: &quot;ActiveDirectoryPassword&quot; 
  8.     }
  9. else:
  10.     driver = &quot;ODBC Driver 17 for SQL Server&quot;
  11.     self.db_api = &quot;pymssql&quot;
  12.     query_dict = {
  13.     }
  15. connection_url = URL.create(
  16.     f&quot;mssql+{self.db_api}&quot;,
  17.     username=self.user,
  18.     password=self.password,
  19.     host=self.server,
  20.     port=self.port,
  21.     database=self.database,
  22.     query=query_dict,
  23. )

huangapple
  • 本文由 发表于 2023年2月24日 01:56:03
  • 转载请务必保留本文链接：https://go.coder-hub.com/75548576.html
  • azure-active-directory
  • pyodbc
  • python
  • sql-server
  • sqlalchemy
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定