MySQL Workbench从MSSQL迁移：AzureAD Windows身份验证故障排查

尝试通过MySQL Workbench 8.0.27迁移向导，将已导入MSSQL的BAK文件迁移至MySQL，但无法通过AzureAD绑定的Windows身份验证连接源MSSQL，不确定是否必须创建独立迁移用户。

背景与调试步骤 #

已成功将BAK文件导入Microsoft SQL Server Management Studio 18，使用迁移向导时遇到以下问题：

1. 使用默认连接方式，错误日志：

Starting...
Connect to source DBMS...
- Connecting to source...
Connecting to Mssql@MS Access Database...
Opening ODBC connection to DSN=MS Access Database;DATABASE=;UID=AzureAD\ZachLonghofer;PWD=XXXX...
ERROR: Connection failed: No open connection to Mssql@MS Access Database
Connecting to Mssql@MS Access Database...
Opening ODBC connection to DSN=MS Access Database;DATABASE=;UID=AzureAD\ZachLonghofer;PWD=XXXX...
ERROR: Connection failed: No open connection to Mssql@MS Access Database

Traceback (most recent call last):
  File "C:\Program Files\MySQL\MySQL Workbench 8.0\workbench\wizard_progress_page_widget.py", line 71, in run
    self.func()
  File "C:\Program Files\MySQL\MySQL Workbench 8.0\modules\migration_source_selection.py", line 455, in task_connect
    raise e
  File "C:\Program Files\MySQL\MySQL Workbench 8.0\modules\migration_source_selection.py", line 439, in task_connect
    if not self.main.plan.migrationSource.connect():
  File "C:\Program Files\MySQL\MySQL Workbench 8.0\modules\migration.py", line 199, in connect
    self._rev_eng_module.connect(self.connection, self.password or "")
SystemError: NotConnectedError("No open connection to Mssql@MS Access Database"): error calling Python module function DbMssqlRE.connect
ERROR: Error during Connect to source DBMS: NotConnectedError("No open connection to Mssql@MS Access Database"): error calling Python module function DbMssqlRE.connect
ERROR: Exception in task 'Connect to source DBMS': SystemError('NotConnectedError("No open connection to Mssql@MS Access Database"): error calling Python module function DbMssqlRE.connect')

Failed

2. 尝试ODBC（原生）并指定服务器ZLONGHOFER\SQLEXPRESS01，错误日志：

Starting...
Connect to source DBMS...
- Connecting to source...
Connecting to Mssql@DRIVER=SQL Server;SERVER=ZLONGHOFER\SQLEXPRESS01...
Opening ODBC connection to DRIVER={SQL Server};SERVER=ZLONGHOFER\SQLEXPRESS01;DATABASE={};UID=AzureAD\ZachLonghofer;PWD=XXXX;...
Connecting to Mssql@DRIVER=SQL Server;SERVER=ZLONGHOFER\SQLEXPRESS01...
Opening ODBC connection to DRIVER={SQL Server};SERVER=ZLONGHOFER\SQLEXPRESS01;DATABASE={};UID=AzureAD\ZachLonghofer;PWD=XXXX;...
Connecting to Mssql@DRIVER=SQL Server;SERVER=ZLONGHOFER\SQLEXPRESS01...
Opening ODBC connection to DRIVER={SQL Server};SERVER=ZLONGHOFER\SQLEXPRESS01;DATABASE={};UID=AzureAD\ZachLonghofer;PWD=XXXX;...

Traceback (most recent call last):
  File "C:\Program Files\MySQL\MySQL Workbench 8.0\workbench\wizard_progress_page_widget.py", line 71, in run
    self.func()
  File "C:\Program Files\MySQL\MySQL Workbench 8.0\modules\migration_source_selection.py", line 455, in task_connect
    raise e
  File "C:\Program Files\MySQL\MySQL Workbench 8.0\modules\migration_source_selection.py", line 439, in task_connect
    if not self.main.plan.migrationSource.connect():
  File "C:\Program Files\MySQL\MySQL Workbench 8.0\modules\migration.py", line 199, in connect
    self._rev_eng_module.connect(self.connection, self.password or "")
grt.DBLoginError: [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'AzureAD\ZachLonghofer'. (18456) (SQLDriverConnect); [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'AzureAD\ZachLonghofer'. (18456)
ERROR: Error during Connect to source DBMS: [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'AzureAD\ZachLonghofer'. (18456) (SQLDriverConnect);
 [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'AzureAD\ZachLonghofer'. (18456)
ERROR: Exception in task 'Connect to source DBMS': DBLoginError("[28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'AzureAD\\ZachLonghofer'. (18456) (SQLDriverConnect); [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'AzureAD\\ZachLonghofer'. (18456)")

Failed

解决方案 #

方案1：用新版ODBC驱动实现Windows身份验证（无需新建用户） #

MySQL Workbench迁移向导依赖ODBC驱动连接MSSQL，旧版SQL Server Driver对AzureAD Windows身份验证支持不足，需改用新版驱动：

1. 安装微软官方的ODBC Driver 17或18 for SQL Server
2. 在MySQL Workbench迁移向导的源数据库连接设置中，选择ODBC（原生），手动填写连接字符串：

   DRIVER={ODBC Driver 18 for SQL Server};SERVER=ZLONGHOFER\SQLEXPRESS01;DATABASE=你的目标数据库名;Trusted_Connection=YES;Encrypt=YES;TrustServerCertificate=YES
   

   • Trusted_Connection=YES：启用Windows身份验证，无需输入用户名密码
   • Encrypt=YES和TrustServerCertificate=YES：适配AzureAD环境的加密连接要求
3. 跳过用户名、密码输入框，直接测试连接

方案2：创建SQL Server身份验证用户（备选） #

若ODBC驱动配置仍有问题，可创建独立迁移用户：

1. 打开SSMS，用Windows身份验证连接MSSQL服务器
2. 展开安全性>登录名，右键选择新建登录名
3. 选择SQL Server身份验证，设置用户名（如MigrationUser）和密码，取消勾选强制密码过期
4. 在用户映射选项卡，勾选需要迁移的数据库，授予db_datareader和db_datawriter权限（或db_owner权限）
5. 在MySQL Workbench迁移向导中，选择SQL Server身份验证，输入刚创建的用户名和密码，连接服务器ZLONGHOFER\SQLEXPRESS01

关键注意事项 #

• 第一个错误日志出现“MS Access Database”是因为误选了错误的数据源类型，需确保在迁移向导中选择Microsoft SQL Server作为源数据库类型
• 以管理员身份运行MySQL Workbench，避免Windows权限不足导致的身份验证失败
• 若MSSQL服务器为“仅Windows身份验证”模式，需先改为“SQL Server和Windows身份验证模式”（SSMS中右键服务器>属性>安全性），才能创建SQL Server用户

内容的提问来源于stack exchange，提问作者Zach Longhofer