SQL Server 2017证书与对称密钥恢复后无法解密原加密数据

问题概述 #

在SQL Server 2017测试环境验证对称密钥加密方案，原本可正常完成数据加解密，但在同一服务器删除原证书与对称密钥后，从备份恢复证书并重新创建同名对称密钥，执行查询存储过程时原加密数据解密结果为NULL，仅新密钥加密的数据可正常解密。

操作步骤 #

1. 创建证书与对称密钥 #

CREATE CERTIFICATE TestCert   
   ENCRYPTION BY PASSWORD = 'QGTkj3E$NvySXU4x7ens'  
   WITH SUBJECT = 'Testing encryption by Certificate',   
   EXPIRY_DATE = '20251231';  

CREATE SYMMETRIC KEY TestSymKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE TestCert;

2. 备份证书 #

BACKUP CERTIFICATE TestCert
 TO FILE = N'c:\Backup\TestCert.cer'
 WITH PRIVATE KEY
  ( FILE = N'c:\Backup\TestCert.pvk'
  , ENCRYPTION BY PASSWORD = N'AReallyStr0ngK#y4You'
  , DECRYPTION BY PASSWORD = N'QGTkj3E$NvySXU4x7ens'
  )
;

3. 删除原密钥与证书后恢复 #

CREATE CERTIFICATE TestCert
FROM FILE = N'c:\Backup\TestCert.cer'
WITH PRIVATE KEY
(
     FILE = N'c:\Backup\TestCert.pvk',
     DECRYPTION BY PASSWORD = N'AReallyStr0ngK#y4You',
     ENCRYPTION BY PASSWORD = 'QGTkj3E$NvySXU4x7ens'  
);

CREATE SYMMETRIC KEY TestSymKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE TestCert;

存储过程代码 #

ALTER PROCEDURE [dbo].[spGetData] 
        @uid nvarchar(128),
    @CertKey as varchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @sqlOpenCert AS NVARCHAR(MAX);
    SET NOCOUNT ON;
    SET @sqlOpenCert = 'OPEN SYMMETRIC KEY TestSymKey DECRYPTION BY CERTIFICATE TestCert WITH PASSWORD = '''+@CertKey+'''';

    EXEC sp_executesql @sqlOpenCert;

    select [DateEncValue], CONVERT(nvarchar, DecryptByKey([DateEncValue])) as dateDec, TextEncValue, CONVERT(nvarchar, DecryptByKey(TextEncValue)) as textDec
    from tblEncryptTest
    where encryptid = @uid

    CLOSE SYMMETRIC KEY TestSymKey;
END

数据表结构 #

CREATE TABLE [dbo].[tblEncryptTest](
    [EncryptID] [int] IDENTITY(1,1) NOT NULL,
    [TextValue] [varchar](50) NOT NULL,
    [TextEncValue] [varbinary](8000) NULL,
    [DateValue] [date] NOT NULL,
    [DateEncValue] [varbinary](8000) NULL,
 CONSTRAINT [PK_tblEncryptTest] PRIMARY KEY CLUSTERED 
(
    [EncryptID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

原加密数据示例：TextValue = 'This is a text Value'，DateValue = '12/15/1986'

问题原因及解决方法 #

核心原因 #

SQL Server中，对称密钥的**SID（安全标识符）**是唯一标识，恢复证书后重新创建的同名对称密钥会生成新的SID，而原加密数据是与旧密钥的SID绑定的，新密钥SID不匹配导致解密失败。

排查验证 #

执行以下查询对比原环境（若可访问）与恢复环境的对称密钥SID：

SELECT name, sid FROM sys.symmetric_keys WHERE name = 'TestSymKey';

若两处SID不同，即可确认是此问题。

同时可验证证书是否恢复正确（证书thumbprint需一致）：

SELECT thumbprint FROM sys.certificates WHERE name = 'TestCert';

解决方法 #

1. 从原环境备份对称密钥并恢复（若原环境仍可访问）：

   • 在原环境备份对称密钥：

     BACKUP SYMMETRIC KEY TestSymKey 
     TO FILE = N'c:\Backup\TestSymKey.key'
     ENCRYPTION BY PASSWORD = 'YourSecureBackupPassword';
     

   • 在恢复环境中，先恢复证书，再恢复对称密钥（不要重新创建）：

     CREATE SYMMETRIC KEY TestSymKey
     FROM FILE = N'c:\Backup\TestSymKey.key'
     DECRYPTION BY PASSWORD = 'YourSecureBackupPassword'
     ENCRYPTION BY CERTIFICATE TestCert;
     

2. 若原环境已不可访问：
   原加密数据无法通过新密钥解密，只能对现有数据重新使用新密钥加密。

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