python - 使用 python 使用 Active Directory 密码连接 Azure SQL Server 数据库(出现错误)

Question

我正在尝试使用 Active Directory 密码和 python 连接 Azure、SQL Server 数据库。但我收到以下错误。

请检查以下错误:-

Traceback (most recent call last):
  File "database_test.py", line 20, in <module>
    main()
  File "database_test.py", line 11, in main
    connection = pyodbc.connect('DRIVER='+driver+';SERVER='+serverName+';PORT=1443;DATABASE='+dbName+';UID='+User_name+';PWD='+ password+';Authentication=ActiveDirectoryPassword')
pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 13 for SQL Server]SQL Server Network Interfaces: The Microsoft Online Services Sign-In Assistant could not be found. Install it from http://go.microsoft.com/fwlink/?Link Id=234947. If it is already present, repair the installation. [2].  (2) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 13 for SQL Server]Client unable to establish connection (2); [08001] [Microsoft][ODBC Driver 13 for SQL Server]In
valid connection string attribute (0)')

请检查以下代码:-

import pyodbc

def main():
    serverName = "<ServerName>"
    dbName = "<DatabaseName>"
    User_name = '<UserName>'
    password = '<Password>'

    driver= '{ODBC Driver 13 for SQL Server}'
    connection = pyodbc.connect('DRIVER='+driver+';SERVER='+serverName+';PORT=1443;DATABASE='+dbName+';UID='+User_name+';PWD='+password+';Authentication=ActiveDirectoryPassword')
    cursor = connection.cursor()
    data = cursor.execute("select * from dbo.test;")
    allData = data.fetchall()
    connection.close()
    for i in allData:
        print(i)

if __name__== "__main__":
    main()

有什么办法可以解决上述问题吗？

是否可以使用 pyodbc 和 Active Directory 密码身份验证连接 azure sql server 数据库？如果可能的话，使用 Active Directory 密码连接 Azure Sql Server 数据库的正确方法是什么？

Answer 1

请确保您已安装Azure SQL数据库的驱动程序。您可以从这个文档下载Quickstart: Use Python to query an Azure SQL database 。

本文档可以提供更多有关 Python 的指南。

根据您的错误消息，您错过了“Microsoft Online Services 登录助手”。请从为您提供的链接下载并安装它:http://go.microsoft.com/fwlink/?Link ID=234947。

这是我的测试Python代码，我做了一些更改，您可以更清楚地看到:

import pyodbc

def main():
    serverName = "****.database.windows.net"
    dbName = "Mydatabase"
    User_name = '****@****.com'
    password = '****'
    Authentication='ActiveDirectoryPassword'
    driver= '{ODBC Driver 17 for SQL Server}'

    connection = pyodbc.connect('DRIVER='+driver+
                      ';Server='+serverName+
                      ';PORT=1433;DATABASE='+dbName+
                      ';UID='+User_name+
                      ';PWD='+ password+
                      ';Authentication='+Authentication)

    cursor = connection.cursor()
    data = cursor.execute("select * from dbo.tb1;")
    allData = data.fetchall()
    connection.close()
    for i in allData:
        print(i)

if __name__== "__main__":
    main()

注意:我在计算机中使用 ODBC Driver 17 for SQL Serve。

希望这有帮助。