Azure虚拟桌面AAD集成连接Azure SQL遇ODBC认证错误求助

问题场景 #

在Azure虚拟桌面环境中，通过MS Access应用使用ODBC Driver 17/18 for SQL Server，以ActiveDirectoryIntegrated认证方式连接Azure SQL Server时，连接失败，报错如下：

ODBC Driver 17错误 #

Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Failed to authenticate the user '' in Active Directory (Authentication option is 'ActiveDirectoryIntegrated').
Error code 0xCAA9003B; state 10
ADAL received an empty response from the server during a WIA flow and could not continue.

ODBC Driver 18错误 #

Attempting connection
[Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Failed to authenticate the user '' in Active Directory (Authentication option is 'ActiveDirectoryIntegrated').
Error code 0xCAA9003B; state 10
ADAL received an empty response from the server during a WIA flow and could not continue.

已完成的配置：

• 虚拟机已配置AADLoginForWindows扩展，可正常使用MS Entra ID凭据登录
• ODBC数据源已正确配置服务器名、数据库名称，选择了ActiveDirectoryIntegrated认证
• 执行klist命令显示无缓存票据，klist sessions结果如下：

PS C:\Users\my.username> klist

Current LogonId is 0:0x1a05d6

Cached Tickets: (0)
PS klist sessions

Current LogonId is 0:0x1a05d6
[0] Session 2 0:0x1a06ed INTERNAL\my.username CloudAP:RemoteInteractive
[1] Session 2 0:0x1a05d6 INTERNAL\my.username CloudAP:RemoteInteractive
[2] Session 2 0:0x195d4f Window Manager\DWM-2 Negotiate:Interactive
[3] Session 1 0:0xe960 Window Manager\DWM-1 Negotiate:Interactive
[4] Session 0 0:0x84a9 \ NTLM:(0)
[5] Session 2 0:0x195df0 Window Manager\DWM-2 Negotiate:Interactive
[6] Session 0 0:0x3e7 WORKGROUP\vmrdpXXX-0$ NTLM:(0)
[7] Session 0 0:0x3e4 WORKGROUP\vmrdpXXX-0$ Negotiate:Service
[8] Session 1 0:0x8a61 Font Driver Host\UMFD-1 Negotiate:Interactive
[9] Session 0 0:0x8a7d Font Driver Host\UMFD-0 Negotiate:Interactive
[10] Session 2 0:0x195427 Font Driver Host\UMFD-2 Negotiate:Interactive
[11] Session 0 0:0x3e5 NT AUTHORITY\LOCAL SERVICE Negotiate:Service
[12] Session 1 0:0xe9b7 Window Manager\DWM-1 Negotiate:Interactive
PS C:\Users\my.username>

解决方案 #

1. 启用虚拟桌面会话的Kerberos票据缓存 #

通过AADLoginForWindows扩展登录的会话默认不会生成Kerberos票据，需修改系统配置生成可用于ADAL认证的票据：

• 打开本地组策略编辑器（gpedit.msc）
• 导航至计算机配置 > 管理模板 > 系统 > Kerberos
• 启用允许Kerberos获取Cloud AP票据策略
• 重启虚拟机后重新登录，执行klist确认是否生成缓存票据

2. 临时切换ODBC认证方式 #

如果Kerberos配置无法快速生效，可改用ActiveDirectoryPassword认证：

• 在ODBC数据源配置中，将认证方式改为ActiveDirectoryPassword
• 输入MS Entra ID用户名和密码进行连接测试

3. 确保驱动与应用架构匹配 #

确认MS Access和ODBC驱动的架构一致：

• 32位MS Access需使用32位ODBC驱动，通过%windir%\SysWOW64\odbcad32.exe配置数据源
• 64位MS Access需使用64位ODBC驱动，通过%windir%\System32\odbcad32.exe配置数据源

4. 检查用户SQL Server权限 #

确认登录的MS Entra ID用户拥有Azure SQL Server的访问权限：

• 在Azure Portal中进入目标SQL Server的Azure Active Directory设置
• 将该用户添加为SQL Server的AAD管理员，或在目标数据库中授予db_datareader/db_datawriter等必要权限

5. 更新ODBC驱动至最新版本 #

卸载旧版本驱动，安装最新版ODBC Driver 18 for SQL Server，修复ADAL认证兼容性问题后重新配置数据源测试连接

内容的提问来源于stack exchange，提问作者Arend Slomp