Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113

    Unanswered: Decryption not working :(

    I'm implementing some third party data where the provider has requested that we retain one of the fields of there data as encrypted within our system and only accessible to certain people. I'm using a symmetric key for simplicity and performance and it's my first stab at encrypting so I've been using How to: Encrypt a Column of Data from Microsoft as a guide to figure out how it's all tied together.

    Code:
    -- Create the keys.
    CREATE MASTER KEY ENCRYPTION BY 
        PASSWORD = 'P@ssw0rd'
    CREATE CERTIFICATE TestCert
       WITH SUBJECT = 'Testing Certificate';
    CREATE SYMMETRIC KEY TestKey
        WITH ALGORITHM = AES_256
        ENCRYPTION BY CERTIFICATE TestCert;
    
    -- Create a table to test encryption.
    Create Table TestEncryption (OriginalValue nvarchar(10),EncryptedValue varbinary(128))
    -- Open the key and insert some values into the table encrypting the value in the second column.
    Open Symmetric Key TestKey
    	Decryption By Certificate TestCert;	
    Insert Into TestEncryption (OriginalValue,EncryptedValue)
    	Values ('aaaaaa',EncryptByKey(Key_GUID('TestKey'), 'aaaaaa'))
    Insert Into TestEncryption (OriginalValue,EncryptedValue)
    	Values ('bbbbbb',EncryptByKey(Key_GUID('TestKey'), 'bbbbbb'))
    Insert Into TestEncryption (OriginalValue,EncryptedValue)
    	Values ('cccccc',EncryptByKey(Key_GUID('TestKey'), 'cccccc'))
    
    -- See the table with column 1 showing plain text and column 2 showing an encrypted value.
    select * from TestEncryption
    
    -- Open the key to decrypt the values and select from the table.
    Open Symmetric Key TestKey
    	Decryption By Certificate TestCert
    Select OriginalValue,DecryptByKey(OriginalValue),Convert(nvarchar,DecryptByKey(OriginalValue)) From TestEncryption
    In the above example, for me - using SQL Server 2008 SP1 - The two decrypting columns in the final select return null for the three rows. I have server level sysadmin privs so as far as I'm aware it can't be a permissions thing and I should therefore be able to open and use the key for decryption.

    Any thoughts on why this is failing?

  2. #2
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    Figured it out...

    2 problems:
    1.) I was (stupidly) trying to decrypt the OrignalValue column not the EncryptedValue - of course that's not going to work.
    2.) When I fixed that the decryption was returning rubbish, which is because it needs to be converted to varchar not nvarchar.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •