ODBC ActiveDirectoryInteractive场景下，如何让Access重置OAuth令牌并触发MFA重登

问题场景 #

通过VBA创建DSNless链接表连接Azure SQL Database，使用ActiveDirectoryInteractive认证适配Entra ID，但受4小时MFA重登策略限制，令牌过期后重新链接会陷入弹窗循环，常规刷新/修复操作无效，且无法切换到SQL身份验证、不愿自定义OAuth流程。

原连接字符串：

connectionString = "ODBC;Description=SqlServer;" & _
                "DRIVER={ODBC Driver 17 for SQL Server}; " & _
                "SERVER=tcp:" & svrName & ",1433; " & _
                "DATABASE=" & dbName & "; " & _
                "Authentication=ActiveDirectoryInteractive; " & _
                "UID=" & userEmail & ";"

触发的错误：

AADSTS70043: The refresh token has expired or is invalid due to sign-in frequency checks by conditional access. The token was issued on 2025-02-08T22:49:01.5679772Z and the maximum allowed lifetime for this request is 14400. Trace ID: 2a0f4a74-30dc-49b4-b837-d62573b51200 Correlatio Microsoft OLE DB Provider for ODBC Drivers -2147467259

可行解决方案 #

核心思路是彻底清除Access缓存的连接资源与令牌，重新创建链接表以触发单次MFA认证，无需重启Access。

1. 释放所有链接表的占用资源 #

先关闭所有依赖链接表的对象，确保Access能释放缓存的连接：

' 关闭所有打开的表单
Dim frm As Form
For Each frm In Forms
    DoCmd.Close acForm, frm.Name, acSaveNo
Next frm

' 关闭所有打开的报表
Dim rpt As Report
For Each rpt In Reports
    DoCmd.Close acReport, rpt.Name, acSaveNo
Next rpt

' 关闭自定义打开的DAO记录集
Dim db As DAO.Database
Set db = CurrentDb()
Dim rs As DAO.Recordset
For Each rs In db.OpenRecordsets
    rs.Close
Next rs

' 强制释放数据库锁与缓存资源
DBEngine.Idle dbFreeLocks

2. 删除现有Azure SQL链接表 #

直接删除旧链接表，避免Access复用缓存的令牌与连接信息：

Dim td As DAO.TableDef
' 遍历并删除所有Azure SQL链接表（通过连接字符串特征判断）
For Each td In CurrentDb().TableDefs
    If td.Connect <> "" And InStr(td.Connect, "tcp:") > 0 And InStr(td.Connect, ".database.windows.net") > 0 Then
        CurrentDb().TableDefs.Delete td.Name
    End If
Next td
' 刷新表集合
CurrentDb().TableDefs.Refresh

3. 重新创建链接表 #

使用原连接字符串（建议升级到ODBC Driver 18）重新创建链接表，此时会触发单次MFA弹窗：

' 配置服务器、数据库、用户信息
Dim svrName As String, dbName As String, userEmail As String
svrName = "你的Azure SQL服务器名.database.windows.net"
dbName = "目标数据库名"
userEmail = "用户邮箱"

' 生成连接字符串（使用ODBC Driver 18）
Dim connectionString As String
connectionString = "ODBC;Description=SqlServer;" & _
                "DRIVER={ODBC Driver 18 for SQL Server}; " & _
                "SERVER=tcp:" & svrName & ",1433; " & _
                "DATABASE=" & dbName & "; " & _
                "Authentication=ActiveDirectoryInteractive; " & _
                "UID=" & userEmail & ";"

' 批量创建链接表（替换为你的目标表名列表）
Dim tableNames As Variant
tableNames = Array("Table1", "Table2", "Table3")
Dim tblName As Variant
For Each tblName In tableNames
    Dim tdNew As DAO.TableDef
    Set tdNew = CurrentDb().CreateTableDef(tblName)
    tdNew.Connect = connectionString
    tdNew.SourceTableName = tblName
    CurrentDb().TableDefs.Append tdNew
Next tblName

关键注意事项 #

• 必须使用ODBC Driver 18 for SQL Server，该版本对Entra ID令牌的生命周期管理更适配条件访问策略
• 连接字符串中不要添加Persist Security Info=True，否则Access会缓存凭据，无法触发重新认证
• 所有链接表使用完全相同的连接字符串，Access会复用同一个新连接，避免多次弹窗

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