Encrypt password with MD5 algorithm for table field after insert or update in Microsoft SQL Server

Hi guy ! in this post I would like to share you how to encrypt password with MD5 algorithm in Microsoft SQL Server by create Trigger function(T-SQL).

Description: 

     In this example, I have a table, Tbluser, in my database and I want to encrypt password automatically for password table field when the table is got any transactions such as insert or update.

Sample table: TblUser

Now time to create trigger function for table, TblUser. To create Trigger function, go to table TblUser --> click to expand the table --> right click on Triggers, choose New Trigger...

Create a new Trigger function

Now time to coding...

Syntax: 

        HashBytes ( 'algorithm', {variable} )

 

Note: algorithm: MD2,MD4,MD5,SHA,SHA1

Code: 

Create trigger [dbo].[encryptPwd] on [dbo].[TblUser]

after insert,update

as

  declare @upwd varchar(50)

  declare @uID varchar(50)

  begin

    select @upwd = i.password, @uID= i.UserID from inserted i;

    update TblUser set password=HASHBYTES('md5',@upwd) where UserID=@uID

  end

go

Press F5 to excute. Now you get a trigger function named encryptPwd.

Explanation: 

This function encrypt data on Password field of TblUser with MD5 algorithm when you insert or update password for user.

Encrypt password field with MD5 algorithm

Query for selecting data from table TblUser

declare @uID varchar(50) = 'user id'

declare @upwd varchar(50) = 'user password'

SELECT UserID,name, [password] FROM TblUser

WHERE (UserID = @uID) AND ([password] =

(SELECT CAST(HashBytes('md5', @upwd) AS VARCHAR(MAX)) AS pass))

Done...Hope this post is useful for you.