How to save data in encrypted format

Question

How to save data in encrypted format

Maverick100877

SSCertifiable

Points: 5240
More actions
January 26, 2006 at 4:03 am

#167913

Hi All
I have a table loginmaster in which I save loginids and passwords for a software.Now I want to save the password in encrypted format so that if anybody queries then that person is not able to view the password.
This is a bit urgent.Help required..

Viewing 4 posts - 1 through 3 (of 3 total)

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

In SQL 2000 there isn't really anything built-in for this purpose.

In SQL 2005 I've seen some demos demonstrating how to encrypt and store - read and decrypt by using CLR functions which looked pretty cool actually.

In both cases, though, you either need to build something yourself, or use some 3rd part solution.

(google for 'SQL crypt' or 'xp_crypt' and you'll get some pointers)

/Kenneth

Edward Sokolove SSC Veteran Points: 239 More actions · Answer 2

Actually in SQL 2000 you can use pwdencrypt('password') and change the data type of the field to varbinary and no one can read the data in that field. To compare it you use pwdcompare. i.e where 1 = pwdcompare(fieldname,@password) this would be true or you could do where 0 = pwdcompare(fieldname,@password) to return false.

I use this method for our password methodolgy. If this does not work then try reversing the fieldname with the passed in variable name. I'm not at work right now so I'm doing this from memory.

Email me and I'll get you the exact syntax if it does not work. Also do a google search on pwdcompare because it is an undocumented feature in sql 2000 but it's there.

edward.sokolove.ctr@schriever.af.mil

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

Yes, they are there, though there is two things about them.

First, it's a oneway encryption. You can't decrypt it once it's encrypted with those functions.

This may or may not be an issue.

Second, those procs are undocumented, thus not recommended or supported first hand to use.

Of course we're free to use them, though we must also be aware then that we're building an 'unsupported' solution, should any future problems arise around them. This also may or may not be an issue.

/Kenneth