Post
Ask Question

SQL TIps - How To Compare Encrypted Password In SQL Server?

In our last SQL TIps, we understood how to secure our password by encryption using PWDENCRYPT function.

Although the encryption is good, we have gone through halfway till now by understanding the encryption using PWDENCRYPT but we are not aware of how to compare this password for validation.

In this post, we are going to check the easy way to compare encrypted passwords to fetch particular records.

The syntax is very simple as shown below where PWDCOMPARE returns boolean value true if it match else false.

PWDCOMPARE('String to Compare','Encrypted Password) 
GO

Let's understand this will an example. Suppose we have a user's table which contains username and password where we have encrypted the password for security reasons.

Let's follow below example to understand it more 

-- A user table with username and password 
DECLARE @tblUser AS TABLE (Username VARCHAR(100),
                           [Password] NVARCHAR(250))

Now, insert some rows into this table,

INSERT INTO @tblUser (Username,Password) VALUES ('Indiandotnet',PWDENCRYPT('MyPassword'))
INSERT INTO @tblUser (Username,Password) VALUES ('CsharpCorner',PWDENCRYPT('CsharpPassword'))
INSERT INTO @tblUser (Username,Password) VALUES('IndoreCommunity',PWDENCRYPT('P@ss123'))

Now to confirm the password is encrypted or not run a select command which will show all the username and password.

SELECT Username, [Password] FROM @tblUser

Now, what if we want to compare specific user and password. For example, we want to compare username "Indiandotnet' and password 'test' then it should not return any row.

SELECT Username,[Password] from @tblUser WHERE Username ='Indiandotnet' AND PWDCOMPARE(N'Test',[Password])=1

Now, password is correct and again run the same command you will get one single row that matches,

SELECT Username,[Password] from @tblUser WHERE Username ='Indiandotnet' AND PWDCOMPARE(N'MyPassword',[Password])=1

Let's see below snap of the above steps,

SQL TIps- How to compare Encrypted Password in SQL Server?

You can download the script attached to the post.

Hope this small tip might help you to protect your data by encryption.


Similar Articles