How to Encrypt column data and decrypt the same encrypted data

Question

Post reply

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

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

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 · Answer 2

trivedi.nikesh

Right there with Babe

Points: 715

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: 728159 More actions · Answer 3

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

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

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 · Answer 5

...

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 · Answer 6

trivedi.nikesh

Right there with Babe

Points: 715

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: 728159 More actions · Answer 7

Steve Jones - SSC Editor

SSC Guru

Points: 728159

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?

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

TomThomson SSC Guru Points: 104773 More actions · Answer 8

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: 728159 More actions · Answer 9

Steve Jones - SSC Editor

SSC Guru

Points: 728159

September 23, 2011 at 11:04 am

#1385926

Password Safe is an open source product.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

TomThomson SSC Guru Points: 104773 More actions · Answer 10

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: 728159 More actions · Answer 11

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.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

TomThomson SSC Guru Points: 104773 More actions · Answer 12

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: 728159 More actions · Answer 13

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

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

TomThomson SSC Guru Points: 104773 More actions · Answer 14

TomThomson

SSC Guru

Points: 104773

October 2, 2011 at 2:23 pm

#1389243

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

Tom