decrypting data returns invalid data or wrong character set

• TangoVictor

  SSChampion

  Points: 10976

  More actions

  July 31, 2018 at 11:52 am

  #374019

  Hi I'm playing around with encrypting and decrypting a column of data. 
  I've followed the MS examples and have encrypted a column.

  When retrieving the column using:
  OPEN SYMMETRIC KEY MyKey 
   DECRYPTION BY CERTIFICATE MyCert;
  GO
  SELECT
  PlainTextData
  ,TextData_encrypted AS EncryptedTextData
  ,CONVERT(NVARCHAR, DECRYPTBYKEY(TextData_encrypted)) AS DecryptedTextData
  FROM
  dbo.MyTable

  The DecryptedTextData is a mix of numbers and what appears to be Chinese characters or some other character set or a mix of character sets. 

  

  This is the example i used:
  USE AdventureWorks2012;
GO

  CREATE CERTIFICATE HumanResources037
 WITH SUBJECT = 'Employee Social Security Numbers';
GO

  CREATE SYMMETRIC KEY SSN_Key_01
  WITH ALGORITHM = AES_256
  ENCRYPTION BY CERTIFICATE HumanResources037;
GO

  USE [AdventureWorks2012];
GO

  -- Create a column in which to store the encrypted data.
ALTER TABLE HumanResources.Employee
  ADD EncryptedNationalIDNumber varbinary(128); 
GO

  -- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY SSN_Key_01
 DECRYPTION BY CERTIFICATE HumanResources037;

  -- Encrypt the value in column NationalIDNumber with symmetric 
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);
GO

  -- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data.
OPEN SYMMETRIC KEY SSN_Key_01
 DECRYPTION BY CERTIFICATE HumanResources037;
GO

  -- Now list the original ID, the encrypted ID, and the 
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.
SELECT NationalIDNumber, EncryptedNationalIDNumber 
  AS 'Encrypted ID Number',
  CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber)) 
  AS 'Decrypted ID Number'
  FROM HumanResources.Employee;
GO

  Was hoping someone could shed some light on this and maybe point me in the proper direction. Thx.

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  July 31, 2018 at 12:32 pm

  #1999745

  What was the data type of the data you encrypted?

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• TangoVictor

  SSChampion

  Points: 10976

  More actions

  July 31, 2018 at 12:38 pm

  #1999749

  Hi Gail,
  It's  VARCHAR(15)

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  July 31, 2018 at 1:18 pm

  #1999759

  Then

  
OPEN SYMMETRIC KEY MyKey
DECRYPTION BY CERTIFICATE MyCert;
GO
SELECT
PlainTextData
,TextData_encrypted AS EncryptedTextData
,CONVERT(VARCHAR(15), DECRYPTBYKEY(TextData_encrypted)) AS DecryptedTextData
FROM
dbo.MyTable


  The decrypted data is binary, if you cast it to a data type other than the one it was originally, you're going to get garbage.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• TangoVictor

  SSChampion

  Points: 10976

  More actions

  July 31, 2018 at 1:29 pm

  #1999765

  yep that did it, I should have seen that, was focusing too much on the encryption part. 

  

  Really appreciate the help, have a great day!!