Password Encrypt/Decrypt - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Password Encrypt/Decrypt

Author

Topic

janets20
Starting Member

12 Posts

Posted - 2010-09-10 : 12:54:43

Hi,

I need to save a password in the database by encrypting it.Also need to decrypt it.what SQL Server 2005 functions support these.

Regards
Janet

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-10 : 13:46:19

There are some built-in encrypt/decrypt functions (EncryptByAsymKey, DecryptByAsymKey, EncryptByPassPhrase, etc.) documented in Books Online.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-09-10 : 15:26:44

Encryption is something that I feel is handled better on the front end then just passing the encrypted string to sql.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

CSears
Starting Member

39 Posts

Posted - 2010-09-10 : 17:12:16

Are you just looking to not have the passwords visable? If that's the case what you may want to do is use something like SHA1('Password') when saving a comparing the value

Create new
Insert into UserTable (UserName, Password) VALUES ('UserName', SHA1('SubmittedPassword'))

Update Password
Update UserTable Set Password = SHA1('SubmittedPassword')

Check for valid password
SELECT * FROM UserTable WHERE UserName = 'UserName' AND Password = SHA1('SubmittedPassword')

If the select statement returns no records then either the username doesn't exist, or the password is not correct. SHA1 and most of the easier encryption functions in SQL are more or less just to make sure someone just doesn't open your tables and scan through your passwords easily. It doesn't add a great deal of security.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-10 : 17:26:27

quote:
SHA1 and most of the easier encryption functions in SQL

I think you mean HASHBYTES:

Insert into UserTable (UserName, Password) VALUES ('UserName', HASHBYTES('SHA1','SubmittedPassword'))
Update UserTable Set Password = HASHBYTES('SHA1','SubmittedPassword') WHERE UserName = 'UserName' 
SELECT * FROM UserTable WHERE UserName = 'UserName' AND Password = HASHBYTES('SHA1','SubmittedPassword')

Kristen
Test

22859 Posts

Posted - 2010-09-13 : 08:40:19

Ought to use a SALT as well as a HASH, no?

@O/P : Note that this has NO Decryption, so if you want to EMail a user their "lost password" you will need to create a new, temporary, password and email them that, plus store the encrypted form of the temporary password on their record.

We store the temporary password, and a time limit, on the user's record in addition to their original password. The user can log in using this new, temporary, password up to the expiry time - or they can use their original password (our tests show that quite often people remember their original password anyway!)

If the user makes use of the Temporary Password then they are taken to a "please create a new, memorable, password" page.

Storing a user's password using an Encrypt + Decrypt process means that the stored password can be decrypted; the risk is that the user is using the same password as for their Bank and other critical uses, so best not to provide ANY means of allowing someone to fraudulently discover it - using a one-way process such as SALT + HASH avoids this pitfall.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources