如何使用Active Directory集成身份验证通过python SQL炼金术连接到Azure sql数据库 [英] How to connect to Azure sql database with python SQL alchemy using Active directory integrated authentication

问题描述

我正在尝试使用python中的SQL Alchemy连接到Azure SQL数据库.该数据库最近已从本地移至Azure，据我了解，azure不支持Windows Auth.
我可以使用Active Directory集成身份验证从SSMS连接到数据库.

I am trying to connect to an Azure SQL Database using SQL Alchemy in python. The database was recently moved to Azure from on-prem and as I understand that azure does not support Windows Auth.
I can connect to the DB from SSMS using Active Directory Integrated Auth.

当Db处于Prem状态时，我将使用以下连接字符串，并且该字符串有效:

When the Db was on prem i would use the following connection string and it worked:

"mssql+pyodbc://@*Server*/*DB*?driver=SQL Server"

我尝试了其他一些连接字符串，但无法使其正常工作.

I have tried a few other connection string and cannot get it to work.

"mssql+pyodbc://@*Server*/*DB*?driver=SQL Server?Integrated Security=true"
"mssql+pyodbc://@*Server*/*DB*?driver=SQL Server?Trusted_Connection=true"

我一直收到以下错误，看来sql alchemy默认情况下正尝试通过Windows auth进行连接，无论如何我可以解决此问题吗?

i keep getting the following error, it seems like sql alchemy is trying to connect via windows auth by default is there anyway i can fix this ?

(pyodbc.Error) ('HY000', '[HY000] [Microsoft][ODBC SQL Server Driver][SQL Server]Windows logins are not supported in this version of SQL Server. (40607) (SQLDriverConnect); [HY000] [Microsoft][ODBC SQL Server Driver][SQL Server]Windows logins are not supported in this version of SQL Server. (40607)')
(Background on this error at: http://sqlalche.me/e/dbapi)

推荐答案

众所周知，您的所有需求都在官方文档

As I known, all of your need are in the offical document Using Azure Active Directory with the ODBC Driver.

首先，如果要通过pyodbc连接到Azure SQL数据库，则仅MS SQL Server的odbc驱动程序17版本支持Active Directory集成身份验证.因此，请确保您已经安装了用于SQL Server的最新odbc驱动程序，或者可以从

First, only the odbc driver 17 version for MS SQL Server supports Active Directory Integrated Authentication if you want to connect to Azure SQL Database by pyodbc. So please make sure you have installed the lastest odbc driver for SQL Server, or you can download from https://docs.microsoft.com/en-us/sql/connect/odbc/microsoft-odbc-driver-for-sql-server?view=sql-server-2017.

第二，请遵循

Second, please follow the section UI Additions for Azure Active Directory (Windows driver only) to configure the DSN for Azure Active Directory Integrated authentication to SQL Azure.

然后，您可以按照下面的代码使用pyodbc通过SQL Alchemy连接到SQL Azure.

Then, you can follow the code below to connect to SQL Azure by 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和PWD选项.

Note: The value of connection string above, you can copy it from the `` tab on Azure portal, but please notice to change the odbc driver version and remove the UID and PWD options.

使用Windows Integrated或Active Directory Integrated连接 (仅Windows驱动程序)身份验证，指定 连接字符串中的 Authentication = ActiveDirectoryIntegrated .这 驱动程序将自动选择正确的身份验证模式. UID 和 PWD 不能指定.

To connect using Windows Integrated or Active Directory Integrated (Windows driver only) authentication, specify Authentication=ActiveDirectoryIntegrated in the connection string. The driver will choose the correct authentication mode automatically. UID and PWD must not be specified.

或者您可以考虑使用Authentication=ActiveDirectoryPassword，它比Authentication=ActiveDirectoryIntegrated更容易，并且代码如下.

Or you can consider to use Authentication=ActiveDirectoryPassword which be easier than Authentication=ActiveDirectoryIntegrated and the code as below.

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

希望有帮助.

这篇关于如何使用Active Directory集成身份验证通过python SQL炼金术连接到Azure sql数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！