locked
DecryptByKey function always return NULL value for VARCHAR type fields RRS feed

  • Question

  • Hi everyone,
     I am Encrypting and Decrypting DataBase column field using SQL Server 2005 Database Encryption.Here are the steps that i followed:
    1)Create Master Key
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '$EncryptionPassword12'
    2)CREATE CERTIFICATES
    CREATE CERTIFICATE PasswordFieldCertificate WITH SUBJECT = 'Password Fields';
    3)CREATE SYMETRIC KEY
    CREATE SYMMETRIC KEY PasswordFieldSymmetricKey
    WITH ALGORITHM = TRIPLE_DES
    ENCRYPTION BY CERTIFICATE PasswordFieldCertificate;
    4)ENCRYPT DATABASE COLUMN 

     

    OPEN SYMMETRIC KEY PasswordFieldSymmetricKey
    DECRYPTION
    BY CERTIFICATE PasswordFieldCertificate;
    DECLARE @CardNumber varchar(20)
    SET @CardNumber = 1234567891234567
    UPDATE dbo.TesTable
    SET CardNumber= EncryptByKey(Key_GUID('PasswordFieldSymmetricKey'),@CardNumber);  

    Here CardNumber is VarChar(20) type field in table TesTable

    Now My problems are:
    1)When I execute select command on table TesTable, the column CardNumber always shows empty but when I open this table by rtClick on table "TesTable" then open--the card number data shows in encrypted form.
    2)When I decrypt Card Number field by command
    SELECT
    CONVERT(VARCHAR,DecryptByKey(CardNumber)) from dbo.TesTable
    It returns NULL value
    3)This is always be the case for VarChar data types in table, if datatype is nvarchar then there is no problem.

    Can you guys suggest me where I am wrong in dealing with varChar values.

    Thanks

    • Edited by Monesh Tuesday, June 16, 2009 2:31 PM change table name
    Tuesday, June 16, 2009 2:30 PM

Answers

  • Hi Mayur,
     
    Two things that I see missing from what you've written (or omitted) from the description. Most important one first.
     
    1. Ensure you have opened the symmetric key used to encrypt the cells before doing a select statement (reference OPEN SYMMETRIC KEY/CLOSE SYMMETRIC KEY in Books Online)
    2. Ensure you define the column to be encrypted as varbinary(n) and that the size is big enough to hold the encrypted values.
     
    Another thing that's unrelated...
    SELECT CONVERT(VARCHAR,DecryptByKey(CardNumber)) from dbo.TesTable
    would be better as:
    SELECT CONVERT(VARCHAR(20),DecryptByKey(CardNumber)) from dbo.TesTable
     
    Cheers
    Bob Beauchemin
    SQLskills
    Tuesday, June 16, 2009 6:16 PM

All replies

  • Hi Mayur,
     
    Two things that I see missing from what you've written (or omitted) from the description. Most important one first.
     
    1. Ensure you have opened the symmetric key used to encrypt the cells before doing a select statement (reference OPEN SYMMETRIC KEY/CLOSE SYMMETRIC KEY in Books Online)
    2. Ensure you define the column to be encrypted as varbinary(n) and that the size is big enough to hold the encrypted values.
     
    Another thing that's unrelated...
    SELECT CONVERT(VARCHAR,DecryptByKey(CardNumber)) from dbo.TesTable
    would be better as:
    SELECT CONVERT(VARCHAR(20),DecryptByKey(CardNumber)) from dbo.TesTable
     
    Cheers
    Bob Beauchemin
    SQLskills
    Tuesday, June 16, 2009 6:16 PM
  • Hi Bob,
    Thanks for your reply,
    I have solved this by making column type of VARCHAR(MAX)


    Thanks
    Wednesday, June 17, 2009 11:30 AM