Encrypt a column in SQL 2000 via code or SQL script
I'm using SQL 2000. A string column 'Password' is there in a table 'Users'. It has around 3k rows. My requirement is to encrypt all the values of the 'Password' column. Also, I should be able to decrypt those encrypted password fields whenever it is needed. I know, from SQL 2005 onward, there are in-built functionalists for these requirements. But I'm concerned for SQL 2000. Please suggest if there is any way to achieve my requirement via VB code or SQL script. NOT with any third party tools. I have searched many places but with no success. Thanks.
Let me start by pointing out that you mentioned this was for a password. Proper protection of passwords is a complex subject, but at the bare minimum I would suggest salting and hashing them. SQL Server does include a hash function ([pwdencrypt] it was in SQL Server 2000 but wasn't documented until later versions. The newest versions include Hashbytes which has more options), but this hash function is not the most secure and you should look at other options. Using a hash instead of encryption does violate one of your stated requirements to be able to decrypt those encrypted fields, but with passwords it is normally considered better not to be able to decrypt them. (You can compare a hashed value to a password entered by the user by hashing the password as well, you just can't decrypt it easily to recover the plain text version.) If you really want to encrypt them, look at System.Security.Cryptography namespace and the Simple3Des class in particular for VB. There is documentation [here] and a walkthrough on encrypting strings within a program [here]. :