March 19, 2015 at 7:06 am
I have been able to encrypt a column however I can not decrypt it; please help:
Ok pretty new to encryption. Did it once YEARS ago. The following is what I have which is from a MS example. I got my column encrypted but can not decrypt. please help:
It is a test table, the fields are: TestID int, Encrypted varbinary(8000), ClearText varchar(256)
As I said I got the Encrypted field populated but decryting does not result in anything, just NULL.
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = 'ChangedMyKeyForPost'
GO
CREATE CERTIFICATE SSN
WITH SUBJECT = 'Customer Credit Card Numbers';
GO
CREATE SYMMETRIC KEY NewtonEncryt --CreditCards_Key11
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE SSN;
GO
ALTER TABLE Sales.CreditCard
ADD CardNumber_Encrypted varbinary(128);
GO
OPEN SYMMETRIC KEY NewtonEncryt --CreditCards_Key11
DECRYPTION BY CERTIFICATE SSN --Sales09;
UPDATE EncryptionTest
SET Encrypted = EncryptByKey(Key_GUID('NewtonEncryt')
, ClearText, 1, HashBytes('SHA1', CONVERT( varbinary
, Encrypted)));
GO
OPEN SYMMETRIC KEY NewtonEncrypt --CreditCards_Key11
DECRYPTION BY CERTIFICATE SSN; --Sales09;
GO
SELECT ClearText, Encrypted
AS 'EncryptedText', CONVERT(nvarchar,
DecryptByKey(Encrypted, 1 ,
HashBytes('SHA1', CONVERT(varbinary, Encrypted))))
AS 'Decrypted card number' FROM EncryptionTest;
GO
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
March 19, 2015 at 7:13 am
Why are you first hashing and then encrypting the value? And why, when you try to decrypt are you first hashing the encrypted value and then trying to decrypt that?
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
March 19, 2015 at 7:17 am
Honest answer, because I have not messed with encryption before and I don't know what I am doing with this, trying to figure it out. Here is the example on the MS site... and I am just modifying some of the naming convention:
https://msdn.microsoft.com/en-us/library/ms179331.aspx
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
March 19, 2015 at 7:23 am
Note that the MS site has the Hashbytes function on the CreditCardID, they're not hashing the column that they're encrypting (which is what your code does)
Maybe ditch the example and read the BoL sections on EncrytByKey and DecryptByKey.
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
March 19, 2015 at 7:24 am
Jeffery Williams (3/19/2015)
I have been able to encrypt a column however I can not decrypt it; please help:Ok pretty new to encryption. Did it once YEARS ago. The following is what I have which is from a MS example. I got my column encrypted but can not decrypt. please help:
It is a test table, the fields are: TestID int, Encrypted varbinary(8000), ClearText varchar(256)
As I said I got the Encrypted field populated but decryting does not result in anything, just NULL.
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = 'ChangedMyKeyForPost'
GO
CREATE CERTIFICATE SSN
WITH SUBJECT = 'Customer Credit Card Numbers';
GO
CREATE SYMMETRIC KEY NewtonEncryt --CreditCards_Key11
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE SSN;
GO
ALTER TABLE Sales.CreditCard
ADD CardNumber_Encrypted varbinary(128);
GO
OPEN SYMMETRIC KEY NewtonEncryt --CreditCards_Key11
DECRYPTION BY CERTIFICATE SSN --Sales09;
UPDATE EncryptionTest
SET Encrypted = EncryptByKey(Key_GUID('NewtonEncryt')
, ClearText, 1, HashBytes('SHA1', CONVERT( varbinary
, Encrypted)));
GO
OPEN SYMMETRIC KEY NewtonEncrypt --CreditCards_Key11
DECRYPTION BY CERTIFICATE SSN; --Sales09;
GO
SELECT ClearText, Encrypted
AS 'EncryptedText', CONVERT(nvarchar,
DecryptByKey(Encrypted, 1 ,
HashBytes('SHA1', CONVERT(varbinary, Encrypted))))
AS 'Decrypted card number' FROM EncryptionTest;
GO
Just use DecryptByKeyAutoAsymKey
https://msdn.microsoft.com/en-us/library/ms365420%28v=sql.110%29.aspx
March 19, 2015 at 7:59 am
Thank you for the replies. I am using the exact example from this link"
https://msdn.microsoft.com/en-us/library/ms365420%28v=sql.110%29.aspx
And the decrypted field looks like Chinese writing.
I made database called 'Encrypt'
A single table called Employee. It has the following fields: TestID (int, Identity), NationalIDNumber (varchar(10))
The script below adds the encrypted field.
Can someone please please tell me what i am doing wrong here. Makes no sense. This time I am using the EXACT code posted on MS.
Here is the exact code I ran:
--Create the keys and certificate.
USE Encrypt;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mzkvdMlk979438teag$$ds987yghn)(*&4fdg^';
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mzkvdMlk979438teag$$ds987yghn)(*&4fdg^';
CREATE ASYMMETRIC KEY SSN_AKey
WITH ALGORITHM = RSA_2048 ;
GO
CREATE SYMMETRIC KEY SSN_Key_02 WITH ALGORITHM = DES
ENCRYPTION BY ASYMMETRIC KEY SSN_AKey;
GO
--
--Add a column of encrypted data.
ALTER TABLE Employee
ADD EncryptedNationalIDNumber2 varbinary(128);
OPEN SYMMETRIC KEY SSN_Key_02
DECRYPTION BY ASYMMETRIC KEY SSN_AKey;
UPDATE Employee
SET EncryptedNationalIDNumber2
= EncryptByKey(Key_GUID('SSN_Key_02'), NationalIDNumber);
GO
--Close the key used to encrypt the data.
CLOSE SYMMETRIC KEY SSN_Key_02;
--
--There are two ways to decrypt the stored data.
--
--OPTION ONE, using DecryptByKey()
--1. Open the symmetric key.
--2. Decrypt the data.
--3. Close the symmetric key.
OPEN SYMMETRIC KEY SSN_Key_02
DECRYPTION BY ASYMMETRIC KEY SSN_AKey;
SELECT NationalIDNumber, EncryptedNationalIDNumber2
AS 'Encrypted ID Number',
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber2))
AS 'Decrypted ID Number'
FROM Employee;
CLOSE SYMMETRIC KEY SSN_Key_02;
--
--OPTION TWO, using DecryptByKeyAutoAsymKey()
SELECT NationalIDNumber, EncryptedNationalIDNumber2
AS 'Encrypted ID Number',
CONVERT(nvarchar, DecryptByKeyAutoAsymKey ( AsymKey_ID('SSN_AKey') , NULL ,EncryptedNationalIDNumber2))
AS 'Decrypted ID Number'
FROM Employee;
GO
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
March 19, 2015 at 8:11 am
Jeffery Williams (3/19/2015)
This time I am using the EXACT code posted on MS.
Not quite.
Your example: The column you're encrypting you said was varchar(10)
MS's example (using Adventureworks), the column that's being encrypted is nvarchar(15)
This is important, because when you decrypt, you tell SQL what data type the value should be turned back into. If it's not the data type that the original value was, then you won't get the original value back.
Consider if I converted 24 to binary then asked SQL to cast that binary value to datetime. I shouldn't expect to get 24 back
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
March 19, 2015 at 8:37 am
OMG. Are you kidding me right now?? Now THAT is embarrassing. I can not believe I missed that.
Fixed and working, thank you so much.
(shaking head in disbelief)
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply