据我所知,你的所有需求都在官方文档中Using Azure Active Directory with the ODBC Driver.
首先,如果您想通过以下方式连接到 Azure SQL 数据库,则只有 MS SQL Server 的 odbc 驱动程序 17 版本支持 Active Directory 集成身份验证pyodbc
。因此,请确保您已经安装了 SQL Server 的最新 odbc 驱动程序,或者您可以从以下位置下载https://learn.microsoft.com/en-us/sql/connect/odbc/microsoft-odbc-driver-for-sql-server?view=sql-server-2017.
其次,请遵循以下部分UI Additions for Azure Active Directory (Windows driver only)为 SQL Azure 的 Azure Active Directory 集成身份验证配置 DSN。
然后,您可以按照以下代码连接到 SQL Azure:SQL Alchemy
with pyodbc
.
from urllib import parse
from sqlalchemy import create_engine
connecting_string = 'Driver={ODBC Driver 17 for SQL Server};Server=tcp:<your sql azure server name>.database.windows.net,1433;Database=<your database name>;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;Authentication=ActiveDirectoryIntegrated'
params = parse.quote_plus(connecting_string)
engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)
connection = engine.connect()
result = connection.execute("select 1+1 as res")
for row in result:
print("res:", row['res'])
connection.close()
注意:上面连接字符串的值,您可以从Azure门户上的``选项卡中复制它,但请注意更改odbc驱动程序版本并删除UID
and PWD
选项。
使用 Windows Integrated 或 Active Directory Integrated 进行连接
(仅限 Windows 驱动程序)身份验证,指定身份验证=ActiveDirectoryIntegrated在连接字符串中。这
驱动程序将自动选择正确的身份验证模式。UID
and PWD不得指定。
或者你可以考虑使用Authentication=ActiveDirectoryPassword
这比Authentication=ActiveDirectoryIntegrated
和代码如下。
from urllib import parse
from sqlalchemy import create_engine
your_user_name = '<your AAD user or configured in SQL Azure Server as the figure below>'
your_password_here = '<your AAD account password>'
#connecting_string = 'Driver={ODBC Driver 17 for SQL Server};Server=tcp:sqlserverleon.database.windows.net,1433;Database=dbleon;Uid='+your_user_name+';Pwd='+your_password_here+';Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;Authentication=ActiveDirectoryPassword'
connecting_string = 'Driver={ODBC Driver 17 for SQL Server};Server=tcp:sqlserverleon.database.windows.net,1433;Database=dbleon;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;Authentication=ActiveDirectoryIntegrated'
params = parse.quote_plus(connecting_string)
engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)
connection = engine.connect()
result = connection.execute("select 1+1 as res")
for row in result:
print("res:", row['res'])
connection.close()
希望能帮助到你。