Share via

SQL Server - How to authenticate encrypted password stored in SQL table using T-SQL programming

  • Article

1)    Objective:

The objective of this article is to provide a way of authenticating password stored in SQL Server table using T-SQL programming. This is a small effort to make new software engineers (especially database ) aware of the encryption, decryption & authentication by very simple example(s) .We are using PWDENCRYPT function to encrypt the password.

2)   Pre-requisites

                1)      Creating a table named ‘ApplicationUser’ where username & password will be stored.

CREATE TABLE dbo.ApplicationUser

(ID int Identity,

UserName NVARCHAR(100),

UserPassword NVARCHAR(100)

)

                2)      Inserting the UserName & their Password in the table

        Insert Into dbo.ApplicationUser
        SELECT 'Vishal',PWDENCRYPT('Cricket@2020')

        GO

        Insert Into dbo.ApplicationUser
        SELECT 'Priyanka',PWDENCRYPT('Olympic@2020G@ld')

        GO

                 3)      Check the data into the table

                 

3)   Case(s) to authenticate UserName & Password

Following are the cases where we can try to authenticate the UserName & Password stored in the table.

  1. Pass the actual UserName & Password string
  2. Pass the actual UserName string & encrypted password string
  3. Pass the actual UserName string & encrypt the actual password string while authenticating
  4. Pass the actual UserName string & use the PWDCOMPARE function with actual password string

4)   Case 1- Pass the actual username & actual password string

This case will definitely fail because password is stored in an encrypted format and we are passing actual password string:

         

5)   Case 2- Pass the actual username & encrypted password string

This case will fail because SQL Server does not allow direct filtering operation over encrypted data:

       

6)   Case 3- Pass the actual username string & encrypt the actual password string while authenticating

This case will fail because SQL Server always create new encrypted string for same actual string. So in this case, encrypted string stored in table & new created encrypted string will not be same.

         

**7)  ** Case 4 - Pass the actual username string & use the PWDCOMPARE function with actual password string

This case will pass because PWDCOMPARE hashes a password and compares the hash to the hash of an existing password. PWDCOMPARE can be used to search for blank SQL Server login passwords or common weak passwords.

PWDCOMPARE returns 1 if the hash of the clear_text_password matches the password_hash parameter, and 0 if it does not match.

        

8)    Conclusion:

By using the PWDCOMPARE, encrypted password string can be authenticated using T-SQL programming.

                 --------------------------------------------End of Article-----------------------------------