主页 / user-5075689

user16421's questions

Martin Hope
user16421
Asked: 2024-11-05 00:53:11 +0800 CST
  • 6

我正在使用 SQL Server 2017 在测试环境中工作，以验证使用对称密钥加密数据是否能满足我们的需求。我已经使用下面的对称密钥成功加密和解密了数据，但是当我在同一台服务器上测试密钥的还原时，它不会解密最初加密的数据。我确信我只是遗漏了一块拼图，但我看不出它是什么。

我使用以下内容创建了证书和密钥：

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;

然后我使用以下命令备份了证书：

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'
  )
;

然后我删除了对称密钥，然后删除了证书。我使用以下命令成功重新创建了证书和密钥：

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;

我可以使用相同的 select 语句（没有错误），但解密数据返回 NULL。如果我使用新密钥更新表中的加密数据，select 语句将成功提取解密数据。

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

该表设置了 4 列。2 列用于存储原始值（测试目的），2 列用于存储加密值。

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