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.

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

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

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.