Decryption fail

• onlo

  SSCertifiable

  Points: 5080

  More actions

  June 10, 2008 at 12:28 am

  #192066

  Dear all,

  I want to encrypt my Employees table's emppassword column, but it always returns NULL, why ?

  SELECT * FROM Employees

  empid mgrid empname salary emppassword

  ----------- ----------- ------------------------- --------------------- -----------

  1 NULL Nancy 10000.00 abc

  2 1 Andrew 5000.00 abc

  3 1 Janet 5000.00 abc

  CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'abc90123###'

  CREATE CERTIFICATE MyCert WITH SUBJECT = 'My DB Access', START_DATE = '06/10/2008',

  EXPIRY_DATE = '10/31/2010'

  CREATE SYMMETRIC KEY MySymKey WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE MyCert

  OPEN SYMMETRIC KEY MySymKey DECRYPTION BY CERTIFICATE MyCert

  -- I wanted to encrypt the emppassword column

  UPDATE dbo.Employees SET emppassword = EncryptByKey(KEY_GUID('MySymKey'),emppassword)

  -- Then I try to decrypt it :

  OPEN SYMMETRIC KEY MySymKey DECRYPTION BY CERTIFICATE MyCert

  SELECT CONVERT(CHAR(10),DecryptByKey(emppassword)) FROM dbo.Employees

  The result was :

  ----------

  NULL

  NULL

  NULL

  Why ? Please help me.

• onlo

  SSCertifiable

  Points: 5080

  More actions

  June 10, 2008 at 1:01 am

  #826474

  After reading this article, I know the cause of my problem and fixed it :

  http://www.sqlservercentral.com/Forums/Topic252605-23-1.aspx

  The reason is my "emppassword" column is not long enough to store the encrypted data.