I have an odd encryption and decryption problem. Encryption worked fine in SQL 2008 R2, we were using TRIPLE_DES_3KEY. This has been deprecated in SQL 2016, so we were are testing out AES_256. My original string keeps getting cut off, not sure what it's doing or how else to explain. I am sure I'm missing something, but not sure what. Detals below. Thanks!
My Symmetric Key:
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]
My Stored Procedure:
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
My Test SQL:
EXEC _usp_EncryptionTest 'Spring is Finally Here! Spring is Finally Here!','Spring is Finally Here! Spring is Finally Here!'
As you can see from the image below, the data gets updated to my table.
My Decryption SQL:
OPEN SYMMETRIC KEY SK03 DECRYPTION BY PASSWORD='ThisIsMyAES_256EncryptionTest' SELECT [OriginalValue] ,CONVERT(varchar(MAX), DECRYPTBYKEY([EncryptedValue])) FROM _EncryptionTest CLOSE SYMMETRIC KEY SK03;