Encrypt a column in SQL 2000 via code or SQL script

Question

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.

Auto

VB6 or VB.NET? Either way I'm pretty sure that Google will have plenty of results for it. Why must the encryption be reversible anyway? It is usual to just use a one way hash. – Therese

VB6. String values should be first encrypted. But, when required in future, one should be able decrypt that same encrypted value to the original string value. – Auto

Answer 1

You could use undocumented PWDENCRYPT and PWDCOMPARE functions available in SQL Server 2000 -

CREATE TABLE #USER
(
    LOGIN_ID varchar(20),
    UserPassword  nvarchar(256)
)

-- Encrypt & Insert Password
-- Note: You will have to write UPDATE on existing records
INSERT #USER VALUES ( 'my_loginid', PWDENCRYPT('MyPassword1'))


DECLARE @InputPassword VARCHAR(100)
DECLARE @IsValid INT = 0

-- Test for Correct Password

SET @InputPassword = 'MyPassword1'

SET @IsValid = (SELECT PWDCOMPARE(@InputPassword, UserPassword, 0)
                FROM #USER
                WHERE LOGIN_ID = 'my_loginid')


SELECT @IsValid AS 'Test1';

-- Test for Wrong Password

SET @InputPassword = 'WrongPassword'

SET @IsValid = (SELECT PWDCOMPARE(@InputPassword, UserPassword, 0)
                FROM #USER
                WHERE LOGIN_ID = 'my_loginid')

SELECT @IsValid AS 'Test2'

DROP TABLE #USER

Reference links -

• PWDENCRYPT
• PWDCOMPARE
• SQL Server's Undocumented Password Encryption Functions

Julianajuliane

Answer 2

Passwords are usually stored with a 1 way hash (for example SHA1), meaning they are encrypted and never need to be decrypted. When the user enters the password, your code would hash it and check if the hashed value matched the hashed value in the database.

However, it sounds like you have a requirement to also be able to decrypt the password. For that there are several asymmetric algorithms (RSA, PGP, etc) where you would have a private and public key pair. The private key is kept secret, while the public key could be shared for others to be able to encrypt their own information before sending it to you. It sounds like that is overkill since only your VB6 code needs to encrypt the data and not any 3rd parties. Therefore, you could simply use a symmetric algorithm (like Blowfish or TripleDES) where you use the same passphrase (instead of a key pair) to encrypt and decrypt the data. That passphrase could be stored in a configuration file on the server. Make sure to keep it protected from unauthorized users.

Have you seen this article? It uses TripleDES with a passphrase which sounds exactly like what you need. http://msdn.microsoft.com/en-us/library/ms172831(v=vs.80).aspx

Jana

Answer 3

Nowadays it is considered a bad practice just to encrypt passwords on their own. Often an arbitrary string (called "salt") is added to every password and then encryption applied. In principle it does not matter in what sequence you are adding "salt" and encrypt. All these combinations are equal in encoding strength:

HASH (Pass & Salt) OR HASH (HASH (Pass)+Salt)) OR HASH (HASH (Pass) + HASH (Salt))

Salt is kept in separate table as plain-text. Another thing you can do is to encrypt same value several times in a row. Small delay for one user will not be noticeable, but it will increase effort needed to brute force the password.

It is also a good practice to name tables so table names cannot be guessed. It makes blind attacks more difficult when they cannot get table with passwords straight away.

As to a way to encrypt the string.

SQL Server 2000 There no built-in symmetric functions. There are 2 asymmetric built-in functions: BINARY_CHECKSUM and CHECKSUM.

VB VB gives you already implemented algorithms as well as tools to do your own implementation. Article referred to by @SuperFunkyMonkey has links to Security.Cryptography Namespace. Another symmetric algorithm (one you can decode) is Rijndael.

Tallboy

Answer 4

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.

Colombia