使用对称密钥/证书对加密数据进行链接服务器查询

Question

我想如果我必须真正将其归结为一件事，真正的问题是："有没有办法使用某种 4 部分标识符语法等效于调用 keyand 证书并保持打开状态，以便 4 部分标识符SELECT语句可以使用链接服务器使用它？

---

所有服务器都是SQL Server 2012。

当我在 SSMS 中登录到服务器 （ SERVER_A ） 时（此服务器具有加密数据），我只需OPEN SYMMETRIC KEY并DECRYPTION WITH证书，然后用 DECRYPTWITHKEY 包装列并继续前进。

SERVER_A SSMS 中的查询：

OPEN SYMMETRIC KEY [KEY]
DECRYPTION WITH [CERTIFICATE]
GO
SELECT
    CONVERT(NVARCHAR, DECRYPTWITHKEY(col)) AS ColA
FROM
    SCHEMA.TABLE
GO

我有另一台服务器（SERVER_B）与SERVER_A有链接服务器连接。 从SERVER_B开始，我希望能够在语法上做类似的事情，但我没有任何运气。

我成功地在打开RPC的情况下使用了EXEC [SERVER_A].DATABASE.dbo.sp_executesql N'<above code>'，但这种方法需要转义撇号，并且我失去了代码的 IntelliSense 和整体可维护性/可读性（这显然比上面的例子更复杂）。

有没有办法做类似于以下内容的事情：

SSMS 中查询"表单"的理想SERVER_B：

OPEN SYMMETRIC KEY [SERVER_A].DATABASE.[KEY]
DECRYPTION WITH [SERVER_A].DATABASE.[CERTIFICATE]
GO
SELECT
    CONVERT(NVARCHAR, DECRYPTWITHKEY(col)) AS ColA
FROM
    [SERVER_A].DATABASE.SCHEMA.TABLE
GO

或者至少，保持密钥打开，以便我可以在语句中使用 4 部分标识符语法SELECT（即不将整个事情包装在我已经完成并起作用的OPENQUERY中，但我不想引用整个查询）？ 无论做法/性能好坏，实际的 SELECT 语句从 Intranet 上的 3 个不同服务器调用数据，因此 4 部分标识符语法非常方便，可读性方面。

Answer 1

我知道

这是一个旧帖子，但刚刚遇到了相同的情况，并通过使用以下查询，使用 OPENQUERY 来

解决这个问题

SELECT * FROM OPENQUERY( [SERVER_A], '
            EXEC ('' USE [DATABASE];
                    OPEN SYMMETRIC KEY [KEY]
                    DECRYPTION BY CERTIFICATE [CERTIFICATE]
                    SELECT
                        CONVERT(NVARCHAR, DECRYPTWITHKEY(col)) AS ColA
                    FROM
                        SCHEMA.TABLE
                '') 
                ')

Answer 2

对于需要参数的查询，以下内容对我有用 -

declare @param nvarchar(20)
set @param = 'param value'
declare @qSQL nvarchar(1000)
SET @qSQL = '
    SELECT
        *
    FROM
    OPENQUERY([Linked_Server],''        
        EXEC ('''' USE [Database_Name];
                open symmetric key key_name
                decryption by certificate cert_name;
                SELECT
                    *
                FROM
                    [Table_Name]
                where CONVERT(varchar(Max), DECRYPTBYKEY(Column_Name))) = '''''''''+@param+'''''''''
            '''') 
    '');';
EXEC sp_executesql @qSQL;