SQL Server: encrypt password

This post is about encrypting a value before inserting it to your password  field in SQL Server. This is very much similar as PASSWORD(),SHA1(), etc. in MySQL. In SQL Server we can use the same through PWDENCRYPT() function.

Syntax for insert:

INSERT INTO Table1 (field1,field2) VALUES(value1, PWDENCRYPT(value2))

Example:

INSERT INTO dbo.UserLogin (Uid,Pwd) VALUES('User', PWDENCRYPT('Suvendu'))

Here, ‘Suvendu’ is the value for my Pwd field which I want to encrypt.

Syntax for comparing:

SELECT * FROM Table1 WHERE field1=value1 AND field2=PWDENCRYPT(value2)

Example:

SELECT * FROM dbo.UserLogin WHERE Uid='User' AND Pwd=PWDENCRYPT('Suvendu')

Reverse  is not possible, I mean decryption can not done for this encrypted value.

NOTE: It is a undocumented function. As far I know, PWDENCRYPT() is supported by SQL Server version upto SQL Server 2012 (beta) but, MSDN says it may not be available there in the future versions of SQL Server.

About these ads