SQL存储过程使用OPENDATASOURCE所需凭证的最优安全存储方法

首选方案是SQL Server数据库作用域凭据（Database Scoped Credential），是该场景下的最优解，完全不需要依赖链接服务器，也不会出现凭证明文泄露的问题。

方案优势 #

• 凭证经过加密后存储在数据库层面，不会明文出现在存储过程定义、脚本、执行日志中，安全性远高于硬编码
• 权限隔离：只有拥有CONTROL SERVER或ALTER ANY CREDENTIAL权限的高权限账号可以管理凭据，普通用户无法直接查看凭证内容
• 易维护：凭证变更时仅需要更新凭据对象，不需要修改存储过程或业务代码

实现步骤 #

1. 创建数据库作用域凭据 #

仅高权限管理员执行一次如下命令，写入外部数据源的访问凭证：

CREATE DATABASE SCOPED CREDENTIAL [ExtDataSource_XXXCred]
WITH 
    IDENTITY = N'外部数据源用户名',
    SECRET = N'外部数据源密码';

2. 编写存储过程调用 #

存储过程通过高权限上下文读取凭据内容，动态拼接OPENDATASOURCE的连接字符串，普通调用者仅需要存储过程的EXECUTE权限，不需要任何凭据相关的高权限：

CREATE PROCEDURE dbo.Proc_GetExtData
WITH EXECUTE AS OWNER -- 用所有者权限执行，确保可以读取凭据内容
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE 
        @uid NVARCHAR(128),
        @pwd NVARCHAR(128),
        @query_sql NVARCHAR(MAX);

    -- 读取预存的凭证
    SELECT 
        @uid = identity_value,
        @pwd = secret
    FROM sys.database_scoped_credentials
    WHERE name = 'ExtDataSource_XXXCred';

    -- 拼接OPENDATASOURCE查询（以下以SQL Server数据源为例，其他数据源替换提供程序和连接串格式即可）
    SET @query_sql = N'
    SELECT * 
    FROM OPENDATASOURCE(
        ''SQLNCLI11'',
        ''Server=外部数据源地址;Database=目标库名;UID=' + @uid + ';PWD=' + @pwd + '''
    ).目标库名.dbo.目标表名';

    EXEC sp_executesql @query_sql;
END
GO

-- 给需要调用的业务用户/角色授予执行权限即可
GRANT EXECUTE ON dbo.Proc_GetExtData TO [业务用户角色];
GO

低版本SQL Server备选方案 #

如果使用的是SQL Server 2016之前的版本（不支持数据库作用域凭据），可以用列级加密的权限控制表存储凭证：

• 创建一张独立的凭证表，对密码列启用对称/非对称加密
• 仅给存储过程的EXECUTE AS上下文开放表的读取和加密密钥的解密权限
• 普通用户无该表的任何访问权限

禁忌操作 #

绝对不要将凭证直接硬编码在存储过程定义、配置文件或者业务代码中，会导致明文泄露风险。

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