Can't get decryption to work 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

Viewing 0 posts

You must be logged in to reply to this topic. Login to reply