Decryption of Data Returning NULL values

• WillBen

  SSC-Addicted

  Points: 403

  More actions

  September 19, 2012 at 4:20 am

  #281781

  Hi,

  I am struggling to encrypt and decrypt a column in a test table, this is my first time playing with encryption. When I run the code below the UnencryptedPwd field is NULL. Is there something wrong with my code? When you run this code do you get a NULL returned? Any advice on how to fix this would be very much appreciated.

  CREATE TABLE dbo.Test____123SrvAcc321(

  ServAccName VARCHAR(50) NULL,

  ServPassWd VARCHAR(50) NULL,

  EncryptedServPassWd VARBINARY(256) NULL

  )

  INSERT INTO dbo.Test____123SrvAcc321

  (ServAccName, ServPassWd, EncryptedServPassWd)

  VALUES

  ('MYSERVICEACCOUNT',

  'ThisIsMyPassword'

  , NULL

  )

  IF NOT EXISTS

  (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)

  CREATE MASTER KEY ENCRYPTION BY

  PASSWORD = 'MyV3ryL0ngC0NFus1nGP@$$W0RD'

  GO

  CREATE CERTIFICATE DataSubsUserPassWrd

  WITH SUBJECT = 'Password For Service Account used for Data Driven Subscriptions';

  GO

  --DROP SYMMETRIC KEY SSN_Key_01

  CREATE SYMMETRIC KEY SSN_Key_01

  WITH ALGORITHM = TRIPLE_DES

  ENCRYPTION BY CERTIFICATE DataSubsUserPassWrd;

  GO

  OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION

  BY CERTIFICATE DataSubsUserPassWrd;

  UPDATE dbo.Test____123SrvAcc321

  SET EncryptedServPassWd = EncryptByKey(Key_GUID('SSN_Key_01'), ServPassWd)

  GO

  OPEN SYMMETRIC KEY SSN_Key_01

  DECRYPTION BY CERTIFICATE DataSubsUserPassWrd;

  GO

  SELECT *, UnencryptedPwd = CONVERT(VARCHAR(50), DecryptByKey(ServPassWd))

  FROM dbo.Test____123SrvAcc321;

  GO

  /*

  DROP SYMMETRIC KEY SSN_KEY_01

  DROP CERTIFICATE DataSubsUserPassWrd

  DROP MASTER KEY

  */

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  September 19, 2012 at 9:33 am

  #1539639

  --posted to wrong post, sorry.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/