March 16, 2015 at 9:46 am
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
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply