如何通过Microsoft账户在VS Code中用Python连接Azure SQL Database？

问题背景 #

使用Visual Studio Code，尝试通过Microsoft账户（基于Azure Active Directory认证）连接Azure SQL Database，采用Python+SQLAlchemy实现，但代码执行时连接失败。当前代码如下：

# Add libraries needed for connecting to Azure SQL Database
import sqlalchemy
from sqlalchemy import exc
import urllib
import time


#~ Connection Parameters for SQL Server
server   = 'Server_name.database.windows.net'
database = 'database_name'
username = 'username'
password = 'password'   
# driver   = '{ODBC Driver 17 for SQL Server}'
driver   = 'SQL Server' # Try this one if top doesn't work
# print (pyodbc.drivers()) # Used to see what drivers you have


#~ Setup connection strings for API
connection_string_1 = f"""Driver={driver};Server=tcp:{server},1433;Database={database}; Uid={username};Pwd={password};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;"""
connection_string_2 = urllib.parse.quote_plus(connection_string_1)
connection_string_3 = 'mssql+pyodbc:///?autocommit=true&odbc_connect={}'.format(connection_string_2)


#~ Try to connect to the DB. Print error and close if unsuccessful.
try:
    engine = sqlalchemy.create_engine(connection_string_3, echo=False) #Echo will display all queries ran in the command prompt. Useful for debugging.
    DB_connection = engine.connect()
except exc.SQLAlchemyError:
    print('LDAP Connection failed: check password.')
    print('Program aborting and closing in 5 seconds.')
    time.sleep(5)
    quit()


print('File Complete - Connection')

核心问题分析 #

当前代码采用SQL Server原生用户名密码认证方式，未适配Azure AD认证要求，同时存在驱动版本不兼容风险：

• 旧版SQL Server驱动不支持Azure AD认证，必须使用ODBC Driver 17 for SQL Server及以上版本
• 缺少Azure AD认证专属参数，无法触发AAD身份验证流程
• 异常处理过于笼统，无法定位具体失败原因

解决步骤 #

• 确认并安装兼容的ODBC驱动
  运行以下代码查看已安装的驱动，确保存在ODBC Driver 17 for SQL Server：

  import pyodbc
  print(pyodbc.drivers())
  

  若未找到，需安装对应版本的ODBC驱动。

• 修改连接字符串适配Azure AD认证
  针对Microsoft账户（AAD用户名密码），需在连接字符串中添加Authentication=ActiveDirectoryPassword参数，同时指定正确的驱动：

  driver = '{ODBC Driver 17 for SQL Server}'
  connection_string_1 = f"""Driver={driver};Server=tcp:{server},1433;Database={database};Uid={username};Pwd={password};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;Authentication=ActiveDirectoryPassword;"""
  

• 验证账户权限

  • 确保该Microsoft账户已被添加为Azure SQL Database的数据库用户，并分配相应权限（如db_datareader、db_datawriter）
  • 确认Azure SQL Server已配置Azure AD管理员（若未配置，需在Azure门户中完成设置）

• 优化异常处理以定位问题
  将异常处理改为打印具体错误信息，方便排查：

  except exc.SQLAlchemyError as e:
      print(f'连接失败，具体错误信息：{str(e)}')
      print('程序将在5秒后退出')
      time.sleep(5)
      quit()
  

修正后的完整代码 #

# 导入所需库
import sqlalchemy
from sqlalchemy import exc
import urllib
import time
import pyodbc

# 连接参数
server   = 'Server_name.database.windows.net'
database = 'database_name'
username = 'your_microsoft_account@xxx.com'  # 填写完整的Microsoft账户邮箱
password = 'your_account_password'   
driver   = '{ODBC Driver 17 for SQL Server}'  # 必须使用支持AAD认证的驱动

# 生成连接字符串
connection_string_1 = f"""Driver={driver};Server=tcp:{server},1433;Database={database};Uid={username};Pwd={password};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;Authentication=ActiveDirectoryPassword;"""
connection_string_2 = urllib.parse.quote_plus(connection_string_1)
connection_string_3 = 'mssql+pyodbc:///?autocommit=true&odbc_connect={}'.format(connection_string_2)

# 尝试连接
try:
    engine = sqlalchemy.create_engine(connection_string_3, echo=False)
    DB_connection = engine.connect()
    print('连接成功！')
except exc.SQLAlchemyError as e:
    print(f'连接失败，具体错误信息：{str(e)}')
    print('程序将在5秒后退出')
    time.sleep(5)
    quit()

print('操作完成 - 已建立连接')

内容的提问来源于stack exchange，提问作者A.gonzalez