July 31, 2018 at 11:52 am
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.
July 31, 2018 at 12:32 pm
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
July 31, 2018 at 12:38 pm
Hi Gail,
It's VARCHAR(15)
July 31, 2018 at 1:18 pm
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
July 31, 2018 at 1:29 pm
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!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply