Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Save Your Password

By Dinesh Asanka,

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.

Total article views: 13143 | Views in the last 30 days: 11
 
Related Articles
FORUM

Password Encryption in SQl SERVER 2005

Password Encryption in SQl SERVER 2005

FORUM

Automatic Encryption / Decryption

Encrypt / Decrypt without exposing password

FORUM

SQL Encryption:hiding the Password

here I am asking for help on how to hide the password tha tI use for encrypting data

BLOG

Brief Description about SQL Server Encryption

Encryption is a good technique using which a particular data is obfuscated with a key or password. T...

FORUM

Problem with encryption

encryption

Tags
advanced querying    
security    
strategies    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones