Monday, July 12, 2004

Password Encryption in SQL 2000

Written By :- Veer ji Wangoo (India)
Inputs from:- Dinesh Asanka(Sri Lanka) SqlServer Central
Dated :- 12 July 2004

A very simple and unique way to encrypt Password in your database application
using a function called pwdencrypt

Lets have look at it,we first create a table named [UserTab]
which will have two columns along with a UserTab .Note the data Type
varbinary(255) to include Hashed encrypted value in the Table

CREATE TABLE [dbo].[UserTab] (
[ID] [int] IDENTITY (1, 1)
[UserName] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Password] [varbinary] (255) NULL ,

Next insert the data with a usage pwdencrypt() --This is an Undocumented Function.
INSERT INTO [UserTab](UserName,Password)
VALUES ( 'Vsql',pwdencrypt('Varsha'));

--When you select you will find the PWD in Encryted Form.

select * from [user]

--A Sp can be written with below logic and to retreive an d check the Password authentication
--We can ask the user to input teh USername and PWD into the Sp and check it with the matching Pwd
--If compare value comes as true then we give them access else we reject the access to th application

DECLARE @varPassword varbinary(255)
SELECT @varPassword = [Password] FROM [UserTab] where UserName = 'Vsql'
DECLARE @chkPassword varchar(255)
SELECT @chkPassword = 'Varsha'
if (pwdcompare(@chkPassword, @varPassword, 0) = 1)
print 'Get along'
print 'Permission Denied'

Although this doesnt garuntee the Safegaurd of your passwords and
any hacking attacks but will certainly help in maintaining the integrity of the
applications.Like freaking with Usernames/passwords of any user by the people who has
access to tables in your Application Database

You can also use various Encryption algorithms availble in registry
and Devlopment Environments.