Expertise: Advanced
Language: Enterprise

November 30, 2000

Undocumented Encrypt and Decrypt Functions in SQL Server 7.0

There are two undocumented encryption functions. They are not supported by Microsoft, and I have had problems with some SQL Server service packs when using these functions. That being said, the two functions are pwdencrypt(‘string’) and pwdcompare(‘unencrypted string’, ‘encrypted string’). The pwdencrpt function enables a user to insert, and update data in a table, such that it is encrypted from that point on. To insert an encrypted password the following syntax could be used:

INSERT INTO tableName (userID, password) VALUES (1, pwdencrypt
('mypassword')) ;

Note that there is a decrypt function, so in order to use the encrypted data once it is placed in the database, employ the pwdcompare function. The function returns a 1 (TRUE) if the values match and a 0 (FALSE) if the values do not match.

DECLARE @test VARCHAR (100)
SELECT @test = tableName FROM test where id = 1
PRINT pwdcompare('wrongpassword',@test );
PRINT pwdcompare('mypassword',@test );