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.

Solution

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 -