SQLTeam.com | Weblogs | Forums

Decrypt key to nvarchar

#1

Hi.


	DECLARE  @EncryptedValue varbinary(MAX)
	 SET @EncryptedValue =EncryptByKey(Key_GUID('CreditCards_Key11'), '123')

	select @EncryptedValue

	declare @x varbinary(max)

	OPEN SYMMETRIC KEY CreditCards_Key11
   DECRYPTION BY CERTIFICATE Sales09;
   SELECT @x = DecryptByKey(0x007F6D50FF656E4498FD19F55504E1530100000045DE7F65E9EB81C1108DBD519430A6DBAEDCCC59DC6B72174125CA475289F653)

   select cast( @x as varchar(max))

If i do select cast( @x as nvarchar(max)) it will decrypt funny letters ( to varchar will work).
How would I go about and decrypt to nvarchar?

I have tried

OPEN SYMMETRIC KEY CreditCards_Key11
   DECRYPTION BY CERTIFICATE Sales09;

 -- SELECT  EncryptByKey(Key_GUID('CreditCards_Key11'), '123αάΒΩ');
 select EncryptByKey(Key_GUID('CreditCards_Key11')
    , '123', 1, HashBytes('SHA1', CONVERT( nvarchar(max)
    , '123')))

---decrypt
	OPEN SYMMETRIC KEY CreditCards_Key11
   DECRYPTION BY CERTIFICATE Sales09;
   	declare @x varbinary(max)
   SELECT @x = DecryptByKey(0x007F6D50FF656E4498FD19F55504E153010000004FDA57244527B52806C7817B2782ECEBC49F540652F16025C8FDC0F0E7F45FFB8A776CADA88D034BD77D7AD5ACB8CE62,1,
    HashBytes('SHA1', CONVERT( nvarchar(max)
    , 0x007F6D50FF656E4498FD19F55504E153010000004FDA57244527B52806C7817B2782ECEBC49F540652F16025C8FDC0F0E7F45FFB8A776CADA88D034BD77D7AD5ACB8CE62)))

   select cast( @x as nvarchar(max))

This will bring null

Thanks.

#2

I don't know the answer, but would you have had to encrypt an NVARCHAR object in the first place?

                                                                 v
SET @EncryptedValue =EncryptByKey(Key_GUID('CreditCards_Key11'), N'123')
                                                                 ^

or

select cast(cast( @x as varchar(max)) as Nvarchar(max))
#3

Yes thank you!
This seems to work:

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;
#4

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.


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 :thinking: