使用Microsoft Entra实现Azure SQL跨库查询遇秘钥错误求助

可以通过Microsoft Entra（原Azure Active Directory）实现Azure SQL Database的跨数据库查询，你当前遇到的"Fail to obtain or decrypt secret for credential 'TestDbCred'"错误，是因为创建数据库范围凭据时未正确关联Entra身份的验证信息，以下是具体解决思路和步骤：

错误原因分析 #

你创建凭据时仅指定了Entra邮箱作为IDENTITY，但未提供对应的验证密钥/证书。当使用Entra身份作为跨库查询的凭据身份时，不能仅靠邮箱标识，必须结合托管标识或存储在Azure Key Vault中的服务主体密钥来完成身份验证，否则SQL无法获取到有效的验证凭据。

解决方案 #

方案一：使用Entra托管标识（推荐，无需手动管理密钥） #

这是最安全且易维护的方式，利用Azure SQL的托管标识自动完成身份验证：

1. 确保源数据库和目标数据库所在的Azure SQL Server已启用系统分配托管标识（或用户分配托管标识）
2. 在目标数据库中，为托管标识授予查询权限：

-- 创建托管标识对应的数据库用户
CREATE USER [你的SQL Server系统托管标识名称] FROM EXTERNAL PROVIDER;
-- 授予读取权限
ALTER ROLE db_datareader ADD MEMBER [你的SQL Server系统托管标识名称];

3. 在源数据库中创建关联托管标识的数据库范围凭据：

CREATE DATABASE SCOPED CREDENTIAL TestDbCred
WITH IDENTITY = 'Managed Identity';

4. 重新创建外部数据源和外部表（与你之前的语句一致，确保引用上述凭据）：

CREATE EXTERNAL DATA SOURCE TestDbSource 
WITH 
(
    TYPE = RDBMS,
    LOCATION = 'myserver.database.windows.net',
    DATABASE_NAME = 'MyDatabase',
    CREDENTIAL = TestDbCred
);

CREATE EXTERNAL TABLE MyTestTable
( 
    Id int not null, 
    Descr nvarchar(max) null
)
WITH
( 
    DATA_SOURCE = TestDbSource,
    SCHEMA = dbo,
    OBJECT_NAME = MyTestTable
);

5. 执行查询SELECT * FROM MyTestTable;，此时会通过托管标识自动完成Entra身份验证。

方案二：使用Entra服务主体+Azure Key Vault #

若需使用服务主体而非托管标识，需将服务主体密钥存储在Key Vault中：

1. 创建Entra服务主体，并在目标数据库中为其授予db_datareader权限：

CREATE USER [服务主体应用ID] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [服务主体应用ID];

2. 在Azure Key Vault中存储服务主体的客户端密钥，并为源SQL Server的托管标识配置Key Vault的密钥读取权限
3. 在源数据库中创建引用Key Vault密钥的数据库范围凭据：

CREATE DATABASE SCOPED CREDENTIAL TestDbCred
WITH 
    IDENTITY = '服务主体应用ID',
    SECRET = 'https://your-key-vault-name.vault.azure.net/secrets/your-secret-name';

4. 重新创建外部数据源和外部表后即可执行查询。

关键注意事项 #

• 禁用SQL账户后，所有跨库查询必须依赖Entra身份，不能使用SQL身份验证的凭据配置
• 确保源SQL Server的标识（托管标识或服务主体）拥有目标数据库的访问权限
• 若使用Key Vault，需验证SQL Server的访问策略是否允许读取对应密钥

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