python - 当身份验证类型为 Azure Active Directory 时，通过 Python 连接到 sql-server

Question

我正在尝试查看当身份验证类型为:时是否可以通过 Python 连接到 SQL Server:

Azure Active Directory - Universal with MFA support

我可以使用 Azure 连接到此数据库。下面的行显示了通过 azure data studio 连接时所需的凭据:

connection type : Microsoft SQL Server
server : ***.net
Authentication Type : Azure Active Directory - Universal with MFA support
account : ***.***@mycompany.com
database : target
Server group : default

我在网络上到处查看，我看到的有关通过 Python 连接到 SQL Server 的所有内容都是当身份验证类型为 SQL 登录时。如果您知道答案请帮忙。

Answer 1

是的，可以。引用this document了解更多详情。

(Windows driver only.) AAD Interactive Authentication uses Azure Multi-factor Authentication technology to set up connection. In this mode, by providing the login ID, an Azure Authentication dialog is triggered and allows the user to input the password to complete the connection. The username is passed in the connection string.

server=Server;database=Database;UID=UserName;Authentication=ActiveDirectoryInteractive;

我已经对此进行了测试，我的测试代码如下。

import pyodbc
server = 'tonytest920.database.windows.net'
database = 'tonytest'
driver = '{ODBC Driver 17 for SQL Server}'
cnxn = pyodbc.connect('DRIVER='+driver+';SERVER='+server +
                      ';PORT=1433;DATABASE='+database+';Authentication=ActiveDirectoryInteractive;UID=xx@xx.onmicrosoft.com')
cursor = cnxn.cursor()
cursor.execute(
    "select * from Persons")
row = cursor.fetchone()
while row:
    print(str(row[0]) + " " + str(row[1]))
    row = cursor.fetchone()

注意:您需要安装adalsql.dll第一的。请记住选择ENU\x86\adalsql.msi。