Introduction

How often have we seen password is saved in plain text? After doing all kinds of security arrangements still we keep the password in plain text. This short article will discuss about how to keep the passwords in somewhat secure way.

Implementation

There is two steps of implementation. They are storing the password and reading it back.

First create a User table which include fields Username and Password and the password field has the field type varbinary.

CREATE TABLE [dbo].[User] (
[ID] [int] IDENTITY (1, 1) 
NOT NULL ,
[UserName] [varchar] (50) COLLATE 
SQL_Latin1_General_CP1_CI_AS NULL ,
[Password] [varbinary] (255) NULL ,
) ON [PRIMARY]
GO

Now let us see how to Store the user name and the password. At this point we will used the special function pwdencrypt.

INSERT INTO UserEncrycptTable
(UserName,[Password]) 
VALUES ( 'Dinesh',pwdencrypt('Asanka'));

After execution of above statement you will notice that password is not saved in plain text. Now it is time to retrieve the stored password. There is a special function called pwdcompare to fulfill this task.

DECLARE @varPassword 
varbinary(255)
SELECT @varPassword = [Password]
 FROM UserEncrycptTable
 where UserName = 'Dinesh'
DECLARE @chkPassword varchar(255)
SELECT @chkPassword = 'Asanka'
PRINT pwdcompare(@chkPassword, @varPassword, 0);

In the above example two variables were used. varPassword is used to get the password field value while the chkPassword variable is used to store the value which we are going to compare. If the output is 1 then the password is correct while if it is 0 password is wrong. Beauty of this function is it is doing a password comparing not retrieving., which makes little hard for a hacker.

Limitations

Major limitations that I observed was these functions are not documented by Microsoft. It means that they can be changed without notice. This function is among the several function which Microsoft has not done documentation of it. Registry functions are also in the same category.

Another one is in some service packs some times this function gives wrong output. I couldn't figure out in which OS and at what time it is. But I think some you may have an idea and experience on this.

Improvements

This function can be improved by giving an option of encrypt key, which only known by the developers or administrators. This will reduce the attacks of novice hackers because they have to get to that encrypt key which may be like {4EE17959-931E-49E4-A2C6-977ECF3628F3}, which will be difficult to hack.

Conclusion

Even though this is not the best function that you can use for data encryption, you can use this function to some extent. Otherwise developers may have to adopt to their own logic of encryptions.