How to Encrypt column data and decrypt the same encrypted data

• trivedi.nikesh

  Right there with Babe

  Points: 715

  More actions

  September 21, 2011 at 3:50 pm

  #248569

  Hello All,

  I followed the following steps:

  use test_db

  go

  Create Table SystemPasswords (ID INT IDENTITY(1,1), SysName varchar(20), Psw varchar(24))

  go

  select*from SystemPasswords

  insert into SystemPasswords (SysName,Psw) values ('Server1','abc')

  insert into SystemPasswords (SysName,Psw) values ('Server2','def')

  insert into SystemPasswords (SysName,Psw) values ('Server3','ghj')

  insert into SystemPasswords (SysName,Psw) values ('Server4','mno')

  insert into SystemPasswords (SysName,Psw) values ('Server5','zyx')

  ---Encrypting data of column in Table

  --STEP 01

  --If there is no master key, create one now.

  IF NOT EXISTS

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

  CREATE MASTER KEY ENCRYPTION BY

  PASSWORD = '23987hxJKL969#ghf0%94467GRkjg5k3fd117r$$#1946kcj$n44nhdlj'

  GO

  --STEP 02

  --Creating Certificate

  CREATE CERTIFICATE cer_PswEnc001

  WITH SUBJECT = 'PasswordEncryption';

  GO

  --STEP 03

  CREATE SYMMETRIC KEY SSN_Key_001

  WITH ALGORITHM = DES

  ENCRYPTION BY CERTIFICATE cer_PswEnc001;

  GO

  --STEP 04

  -- Open the symmetric key with which to encrypt the data.

  OPEN SYMMETRIC KEY SSN_Key_001

  DECRYPTION BY CERTIFICATE cer_PswEnc001;

  --STEP 05 ADD COLUMN TO TABLE

  ALTER TABLE SystemPasswords ADD EncryptedPsw varbinary(256)

  --STEP 06

  -- Encrypt the value in column NationalIDNumber with symmetric

  -- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber

  UPDATE SystemPasswords

  SET EncryptedPsw = EncryptByKey(Key_GUID('SSN_Key_001'), Psw);

  GO

  --STEP 07

  -- First, open the symmetric key with which to decrypt the data.

  OPEN SYMMETRIC KEY SSN_Key_001

  DECRYPTION BY CERTIFICATE cer_PswEnc001;

  GO

  --STEP 08 VERIFY THE DATA OF TABLE

  SELECT*FROM SystemPasswords

  --STEP 09

  -- Now list the original ID, the encrypted ID, and the

  -- decrypted ciphertext. If the decryption worked, the original

  -- and the decrypted ID will match.

  SELECT SysName, Psw , EncryptedPsw

  AS 'Encrypted Password',

  CONVERT(nvarchar, DecryptByKey(EncryptedPsw))

  AS 'Decrypted Password'

  FROM SystemPasswords;

  GO

  But I am not getting the correct decrypted data when I run the last query.

  Please correct me if I am wrong at any step and let me the neccessary changes or update needed here.

  Thanks,

  Nikesh

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  September 21, 2011 at 4:45 pm

  #1384882

  Don't encrypt passwords. You have no need to ever obtain the plain-text password. Use a one-way hash (like hashbytes) with a salt and then hash and compare passwords entered by the user.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• trivedi.nikesh

  Right there with Babe

  Points: 715

  More actions

  September 22, 2011 at 11:17 am

  #1385336

  Hello Gail,

  I am thankful to you for your reply.

  But please can you correct my code where which need to be changed as per your view, because I am not understanding your view completely.

  Again I am thankful to you for this correction.

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736255

  More actions

  September 22, 2011 at 11:29 am

  #1385343

  Gail means use a one way hash. So when someone enters a password, you store it like this:

  insert SystemPasswords select 'Steve', pwdencrypt( 'MyPassword')

  Then when someone logs into the system, you do this

  if exists( select name from systempasswords where name = 'Steve' and pwd = pwdencrypt( 'MyPassword')

  )

  -- allow login

  However if you are storing passwords for people to use against something like other systems, where they need the decrypted password to enter it in an application, don't build this. Go get Password Safe, KeePass, or 1Password and use those. Theyr'e built and vetted to protect passwords securely.

  Pdwdencrypt - http://technet.microsoft.com/en-us/library/dd822791.aspx

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  September 22, 2011 at 11:49 am

  #1385357

  trivedi.nikesh (9/22/2011)

  ---

  But please can you correct my code where which need to be changed as per your view, because I am not understanding your view completely.

  It's not a case of correcting a couple lines of your code, it's a case of completely changing your approach. Do some reading on hashes and salted hashes and make sure you understand how that works, then look at the HashBytes function in sQL.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• NotoriousDBA

  SSChasing Mays

  Points: 658

  More actions

  September 22, 2011 at 2:57 pm

  #1385462

  ...

  But I am not getting the correct decrypted data when I run the last query.

  Please correct me if I am wrong at any step and let me the neccessary changes or update needed here.

  You're converting to the wrong datatype in the last step. If you change the target datatype of the conversion from nvarchar to varchar it works as you expect.

  That said, I agree with everyone who advised against storing passwords using two-way encryption.

• trivedi.nikesh

  Right there with Babe

  Points: 715

  More actions

  September 23, 2011 at 8:02 am

  #1385762

  Thank you Everyone.

  I am heartily very thankful to you, and I really appreciate you for your time and support on this issue.

  One more thing I want to ask you guys, that can we implement a Wallet in SQL Server to keep safe of our passwords.

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736255

  More actions

  September 23, 2011 at 9:05 am

  #1385823

  You can, but I wouldn't. Is there some reason you wouldn't want to use something like KeePass/Password Safe?

• TomThomson

  SSC Guru

  Points: 104773

  More actions

  September 23, 2011 at 10:44 am

  #1385897

  Steve Jones - SSC Editor (9/23/2011)

  ---

  You can, but I wouldn't. Is there some reason you wouldn't want to use something like KeePass/Password Safe?

  I won't use anything like that for serious passwords unless it is (a) open source and (b) simple enough for me to understand and verify the source.

  Of course I keep my non-serious passwords under a master password in Firefox, but only

  non-serious ones.

  I used to keep serious passwords encrypted by a public key for which the private key was buried under a very long passphrase, using a fairly ancient version of PGP (not, incidentally, the same key pair as I used for email) but I gave that up when newer versons of PGP appeared which seemed somewhat untrustable.

  So I can understand someone wanting to roll their own.

  But - as everyone else has said - if the passwords are being held to be checked agains input, it would be absolutely crazy to encrypt them instead of using a one way hash. Preferably a slow one way hash at that.

  Tom
  

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736255

  More actions

  September 23, 2011 at 11:04 am

  #1385926

  Password Safe is an open source product.

• TomThomson

  SSC Guru

  Points: 104773

  More actions

  September 23, 2011 at 4:59 pm

  #1386061

  Steve Jones - SSC Editor (9/23/2011)

  ---

  Password Safe is an open source product.

  Have you tried to read the code?

  I have, and concluded that I could write a new one in less time than it would take to comprehend that one. Maybe not as facility rich, maybe even not as secure, but definitely good enough foir what I want.

  Tom
  

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736255

  More actions

  September 23, 2011 at 5:29 pm

  #1386066

  I haven't, but I also know a few security researchers find it secure. I don't have the time, nor the knowledge, to understand how to write a secure, strong encryption program. Bruce Schneier and other researchers I respect built the algorithm and released it for use.

• TomThomson

  SSC Guru

  Points: 104773

  More actions

  September 23, 2011 at 5:44 pm

  #1386070

  Steve Jones - SSC Editor (9/23/2011)

  ---

  I haven't, but I also know a few security researchers find it secure. I don't have the time, nor the knowledge, to understand how to write a secure, strong encryption program. Bruce Schneier and other researchers I respect built the algorithm and released it for use.

  If Bruce had a hand in it I'm sure it will be fine. Maybe I should have looked at where it came from, instead of going straight to the code and concluding it was too much effort to evaluate.

  Tom
  

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736255

  More actions

  September 24, 2011 at 6:23 pm

  #1386145

  not a master reference, but here's some history: http://en.wikipedia.org/wiki/Password_Safe

  I want to say that I read about it in one of his books. It was an initiative to try and get stronger passwords used by everyone by making an easy to use program.

  Here's a better one: http://www.schneier.com/passsafe.html

• TomThomson

  SSC Guru

  Points: 104773

  More actions

  October 2, 2011 at 2:23 pm

  #1389243

  Thanks, Steve, I'll download it and have a play.

  Tom