在 Azure 数据工作室使用 Pyodbc 与 SQL 服务器的连接问题
原标题：Connection Issue with SQL Server using pyodbc in Azure Data Studio
I am currently using Azure Data Studio to connect to a SQL Server instance. The authentication type I m using is Microsoft Intra ID with MFA support. I m able to successfully connect, create tables, and manage schemas within Azure Data Studio.
However, I want to load data into my tables using a Python script executed in Jupyter Lab on my local system. My current connection string looks like this:
import pyodbc
Database connection parameters
server = server
database = database name
username = username
password = password
driver = {ODBC Driver 18 for SQL Server}
Connection String:
conn = pyodbc.connect(f DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password} )
When I attempt to connect using this code, I receive the following error:
InterfaceError: ( 28000 , "[28000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Login failed for user username . (18456) (SQLDriverConnect); [28000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Login failed for user username . (18456)")
How can I modify my Python connection string or approach to successfully connect to the SQL Server using this authentication method?
Any guidance or examples would be greatly appreciated!
问题回答
Error: ( 28000 , "[28000] [Microsoft][SQL Server ********][SQL Server] Login failed for user DOMAINusername . (18456) (SQLDriverConnect); [28000] [Microsoft] [SQL Server *****][SQL Server]Login failed for user DOMAINusername . (18456)")
If the user doesn t have access to the database or if the provided credentials are incorrect then you may get above error. Make sure user is accessible to the database and provided correct credentials i.e. username and password. As per this article MFA is the default method of login for all azure tenants. So, when you try to connect Azure SQL db with ActiveDirectoryPassword authentication you may get below error:
( FA004 , "[FA004] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Failed to authenticate the user **** in Active Directory (Authentication option is ActiveDirectoryPassword ).
Error code 0xCAA2000C; state 10
AADSTS50076: Due to a configuration change made by your administrator, or because you moved to a new location, you must use multi-factor authentication to access **** . Trace ID: 9827ec0f-865a-4b15-9c70-207409726100 Correlation ID: 16bf58ec-0c82-45e6-bdbc-9eca4a0a9e11 Timestamp: 2024-08-12 05:46:53Z (0) (SQLDriverConnect); [FA004] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Failed to authenticate the user **** in Active Directory (Authentication option is ActiveDirectoryPassword ).
Error code 0xCAA2000C; state 10
AADSTS50076: Due to a configuration change made by your administrator, or because you moved to a new location, you must use multi-factor authentication to access 022907d3-0f1b-48f7-badc-1ba6abab6d66 . Trace ID: 9827ec0f-865a-4b15-9c70-207409726100 Correlation ID: 16bf58ec-0c82-45e6-bdbc-9eca4a0a9e11 Timestamp: 2024-08-12 05:46:53Z (0)")
So, use MFA authentication to connect Azure SQL db python ActiveDirectoryInteractive authentication method using below code:
import pyodbc
server = tcp:
.database.windows.net
database = ,dbName>
username =
auth = ActiveDirectoryInteractive
cnxn = pyodbc.connect( DRIVER={ODBC Driver 17 for SQL Server};SERVER= +server+ ;DATABASE= +database+ ;Authentication= +auth+ ;UID= +username+ ;ENCRYPT=yes; )
cursor = cnxn.cursor()
cursor.execute("SELECT * FROM ")
rows = cursor.fetchall()
for row in rows:
print(row)
It will connect to SQL db and read the table successfully from data studio as shown below:
