SQL AES_256 加密和解密

我有一個奇怪的加密和解密問題。加密在 SQL 2008 R2 中執行良好，我們使用的是 TRIPLE_DES_3KEY。這在 SQL 2016 中已被棄用，因此我們正在測試 AES_256。我的原始字元串一直被切斷，不知道它在做什麼或如何解釋。我確定我錯過了一些東西，但不確定是什麼。詳情如下。謝謝！

我的對稱密鑰：

CREATE SYMMETRIC KEY SK03  
WITH  
   ALGORITHM = AES_256  
   ENCRYPTION BY PASSWORD = 'ThisIsMyAES_256EncryptionTest';  

我的表：

CREATE TABLE [dbo].[_EncryptionTest](  
   [OriginalValue] [varchar](max) NULL,  
   [Encryptedvalue] [varbinary](8000) NULL  
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

我的儲存過程：

CREATE PROCEDURE [dbo].[_usp_EncryptionTest]  
   @InputString varchar(MAX),  
   @InputString2 varchar(MAX)  

AS  
BEGIN  
   SET NOCOUNT ON;  

   OPEN SYMMETRIC KEY SK03 DECRYPTION BY PASSWORD='ThisIsMyAES_256EncryptionTest'  

   INSERT INTO _EncryptionTest
       (OriginalValue, Encryptedvalue)
   VALUES (
       @InputString
       ,ENCRYPTBYKEY(Key_GUID('SK03'), CONVERT(VARBINARY,@InputString2))
       )

   CLOSE SYMMETRIC KEY SK03;

END  

我的測試 SQL：

EXEC _usp_EncryptionTest 'Spring is Finally Here! Spring is Finally Here!','Spring is Finally Here! Spring is Finally Here!'

如下圖所示，數據已更新到我的表中。

我的解密 SQL：

OPEN SYMMETRIC KEY SK03 DECRYPTION BY PASSWORD='ThisIsMyAES_256EncryptionTest'  
SELECT   
   [OriginalValue]   
   ,CONVERT(varchar(MAX), DECRYPTBYKEY([EncryptedValue]))  
FROM  
   _EncryptionTest  
CLOSE SYMMETRIC KEY SK03;  

下圖應顯示帶有 decryoted 值截止的結果。

嘗試按照 https://technet.microsoft.com/en-us/library/ms188362(v=sql.110).aspx將varbinary轉換語句中的長度定義為預設為 30。varbinary(max).

您可以使用以下腳本複制它：

DECLARE @STRING VARCHAR(100);
SELECT  @STRING = REPLICATE('X', 100);

SELECT  LEN(@STRING) AS String_Length,
       LEN(CONVERT(VARBINARY, @STRING)) AS Varbinary_Length ,
       LEN(CONVERT(VARBINARY(MAX), @STRING)) AS VarbinaryMax_Length;

問題出在加密密碼中。對於 AES 256 加密密碼長度應為 32。使用下面的程式碼並檢查。

CREATE SYMMETRIC KEY SK03  
WITH  
   ALGORITHM = AES_256  
   ENCRYPTION BY PASSWORD = 'abcdefghijklmnopqrstuvwxyzasdefb'; 
go
CREATE TABLE [dbo].[_EncryptionTest](  
   [OriginalValue] [varchar](max) NULL,  
   [Encryptedvalue] [varbinary](max) NULL  
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
go
CREATE PROCEDURE [dbo].[_usp_EncryptionTest]  
   @InputString varchar(MAX),  
   @InputString2 varchar(MAX)  

AS  
BEGIN  
   SET NOCOUNT ON;  

   OPEN SYMMETRIC KEY SK03 DECRYPTION BY PASSWORD='abcdefghijklmnopqrstuvwxyzasdefb'  

   INSERT INTO _EncryptionTest
       (OriginalValue, Encryptedvalue)
   VALUES (
       @InputString
       ,ENCRYPTBYKEY(Key_GUID('SK03'), CONVERT(VARBINARY(max),@InputString2))
       )

   CLOSE SYMMETRIC KEY SK03;

END  
go
EXEC _usp_EncryptionTest 'Spring is Finally Here! Spring is Finally Here!','Spring is Finally Here! Spring is Finally Here!'
go
OPEN SYMMETRIC KEY SK03 DECRYPTION BY PASSWORD='abcdefghijklmnopqrstuvwxyzasdefb'  
SELECT   
   [OriginalValue] ,
   CONVERT(varchar(MAX), DECRYPTBYKEY([EncryptedValue]))
FROM  
   _EncryptionTest  
CLOSE SYMMETRIC KEY SK03;

引用自：https://dba.stackexchange.com/questions/167920