如何在authorization=ActiveDirectoryIntegrated的情况下使用pyodbc.connect（）？

Question

尝试通过VS代码中的python函数访问Azure SQL，身份验证设置为Active Directory Integrated。使用pyodbc进行连接

在本地运行时工作正常，但部署到Azure后出现错误。如果我使用SQL登录，但我想使用Active Directory集成，也可以正常工作。我已经把自己定为广告管理员了

我正在尝试的是：

cnxn = pyodbc.connect("Driver={ODBC Driver 17 for SQL Server};Server=tcp:khawajaserver1.database.windows.net,1433;Database=KhawajaDB1;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;Authentication=ActiveDirectoryIntegrated")

我得到的错误是：

Result: Failure
Exception: Error: ('HY000', '[HY000] [Microsoft][ODBC Driver 17 for SQL Server]MAX_PROVS: Error code 0x57 (87) (SQLDriverConnect)')
Stack:   File "/usr/local/lib/python3.6/site-packages/azure/functions_worker/dispatcher.py", line 308, in _handle__invocation_request

Answer 1

我知道Active Directory密码，因为身份验证类型有效

db_list = [TEST_DB1, TEST_DB2]
sql_conn = None
for db in db_list:
    try:
        conn_string = 'DRIVER={ODBC Driver 17 for SQL Server};' \
                      'SERVER=' + <db_url> + \
                      ';DATABASE=' + <db_name> + \
                      ';UID=' + <db_username> + \
                      ';PWD=' + <db_password> + \
                      ';Authentication=ActiveDirectoryPassword'
        print conn_string
        sql_conn = pyodbc.connect(conn_string)
except Exception as e:
    print "Exception:::", e
    print 'Cannot connect to DB' + str(sys.exc_info()[0])
    return None
sql_conn.cursor().execute(<some SQL Query>)
sql_conn.close()

Answer 2

下面的代码对我有用

# Connection to SQL Server using AADIntegrated

import pyodbc 

server = 'data1.database.windows.net' 
database = 'MyTestDB' 
authentication = 'ActiveDirectoryIntegrated'
kpi_server_connection = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';Authentication='+authentication+';TrustServerCertificate='+ 'no')

query_string = '''
    select top 10 * from [SomeTable] 
    '''
df = pd.read_sql(query_string, kpi_server_connection)
df

Answer 3

当使用SQL Server的ODBC驱动程序17时，当您使用某种形式的托管标识连接到Azure SQL实例时，以下操作有效：

conn_str = 'Driver={};SERVER=tcp:{},1433;DATABASE=CustomerProfiling;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;Authentication=ActiveDirectoryMsi;'.format("{ODBC Driver 17 for SQL Server}", os.environ["SQL_SERVER"])
conn = pyodbc.connect(conn_str)

关键是使用ActiveDirectoryMsi身份验证属性