OPEN SYMMETRIC KEY CreditCards_Key11
DECRYPTION BY CERTIFICATE Sales09;
DECLARE @EncryptedValue varbinary(MAX)
SET @EncryptedValue =EncryptByKey(Key_GUID('CreditCards_Key11'), N'123')
--not needed if you have an encryption by server -- CLOSE ALL SYMMETRIC KEYS;
select @EncryptedValue
OPEN SYMMETRIC KEY CreditCards_Key11
DECRYPTION BY CERTIFICATE Sales09;
select cast( DecryptBykey(0x007F6D50FF656E4498FD19F55504E1530100000026F64BAC40F4E94D027AD8DD21102E4720FCFD71B4CBDC124E508738F62D899E) as nvarchar(max))
Also I was making a mistake on the hash attempt. Hash just provides an extra "layer" but you need to know the hash value on both encrypt and decrypt in order to decrypt.You don't pass the varbinary to the hash, you pass the nvarchar second-tohash value.
So this works also:
--encrypt for value with hash
OPEN SYMMETRIC KEY CreditCards_Key11
DECRYPTION BY CERTIFICATE Sales09;
select EncryptByKey(Key_GUID('CreditCards_Key11')
, N'123', 1, HashBytes('SHA1', CONVERT( nvarchar(max)
, N'testextrahash')))
--decrypt for value with hash
--OPEN SYMMETRIC KEY CreditCards_Key11
-- DECRYPTION BY CERTIFICATE Sales09;
declare @x varbinary(max)
SELECT @x = DecryptByKey(0x007F6D50FF656E4498FD19F55504E153010000009027004A293E8A4B8914C9688748D95109D865FC3BD2AA0682CE5860AADA230451E48056DF1592995B60DD19F4E605FBE34A0F3845A791248F2CB1FF91190AD9,1,
HashBytes('SHA1', CONVERT( nvarchar(max)
, N'testextrahash')))
select cast( @x as nvarchar(max))
CLOSE ALL SYMMETRIC KEYS;
I need to add the the max encrypted value is the varbinary(8000) limitation.
That sucks because I may need to encrypt bigger sizes.
There is a demo function here I haven't tried that spits the data into chunks. https://blogs.msdn.microsoft.com/yukondoit/2005/11/23/sql-server-2005-encryption-encryption-and-data-length-limitations/
We need to be legit with the EU GDPR low so I'm not sure that I can have solutions outside of the current SQL AES - TRIPLE DES mechanisms. Maybe yes maybe not. I need to see our law agency for that