Encyption of data

[TRACEYMARYLAND]

Hi i have a table that has
username, password

Is there a way to encrypt the data in the password field.

I set up my users by directly going into the table..(i not got round to doing an interface) but i can do so.

So from ASP how do i encypt the data and directly entering data into table.

 

[mrdenny]

I use a proc called xp_md5 to encrypt the data via one way encryption. Then to check the password, you encrypt the data they submit as the password witht he xp_md5 procedure, and compare the output strings.

You can get it from here. It is free to download.

http://www.codeproject.com/database/xp_md5.asp

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[TRACEYMARYLAND]

Do you have to add the entire encryption to the whole of the database.

I just need it against one table

USE master;
EXEC sp_addextendedproc 'xp_md5', 'xp_md5.dll'

Thanks

 

[mrdenny]

That code is going to install the DLL on your SQL Instance, so that SQL Server can use the procedure. You will control what is encrypted within your code.

Also don't forget the md5 is one way encryption. You can't decrypt it. That's why I use it for passwords.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[TRACEYMARYLAND]

Trying to get my head around it.

So i register the Dll ..understand that part.

Create the function
------------------
CREATE FUNCTION [dbo].[fn_md5] (@string VARCHAR(8000))

RETURNS CHAR(32) AS
BEGIN
DECLARE @hash CHAR(32)
EXEC DataWarehouse.dbo.usertable @string, @hash OUTPUT
---is this line correct where as DataWarehouse.dbo.UserTable
is my table
RETURN @hash
END

So how do i do the part where i insert into table
insert into usertable values (username, password)

Thanks

 

[mrdenny]

Your function should look like this.

CREATE FUNCTION [dbo].[fn_md5] (@string VARCHAR(8000))
RETURNS CHAR(32) AS
BEGIN
  DECLARE @hash CHAR(32)
  EXEC master.dbo.xp_md5 @string, @hash OUTPUT
  RETURN @hash
END

You then call the function within your code like this.

Declare @UserName varchar(100)
Declare @Password varchar(1000)
set @UserName = 'TestMe'
set @Password = 'password'
set @Password = dbo.fn_md5(@Password)
insert into table
(UserName, Password)
values
(@UserName, @Password)

Then to check the Username and password, you do something like this.

Declare @UserName varchar(100)
Declare @Password varchar(1000)
set @UserName = 'TestMe'
set @Password = 'password'
set @Password = dbo.fn_md5(@Password)
if exists (select * from Table where UserName = @UserName and Password = @Password)
BEGIN
  select 'UserName and Password match.'
END
ELSE
BEGIN
  select 'UserName and Password do not match.'
END

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[TRACEYMARYLAND]

CREATE FUNCTION [dbo].[fn_md5] (@string VARCHAR(8000))
RETURNS CHAR(32) AS
BEGIN
DECLARE @hash CHAR(32)
EXEC master.dbo.xp_md5 @string, @hash OUTPUT
RETURN @hash
END

My database is not master i know there is master in SQL but our Database is called DataWarehouse so do i not have to replace

EXEC master.dbo.xp_md5 @string, @hash OUTPUT
with
EXEC DataWarehouse.dbo.xp_md5 @string, @hash OUTPUT

 

[mrdenny]

No, when you attach a DLL to the SQL Server as an extended stored procedure it is always in master. Extended stored procedures can only be placed in master. They can be used in any database, but they are always housed in master.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[TRACEYMARYLAND]

Oh now i understand it............ok let me go ahead and play with this...for a few hours...

I be back in a while ....to give you a big star if it works

Thanks

 

[mrdenny]

ok, sounds good. If you have any more questions, let me know.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[TRACEYMARYLAND]

Tried to register it it did say it was loaded but then
returned DLL Register server entry point was not found..

so i have to research that.

Then if i run
Declare @UserName varchar(100)
Declare @Password varchar(1000)
set @UserName = 'TestMe'
set @Password = 'password'
set @Password = dbo.fn_md5(@Password)
insert into DataWarehouse.dbo.TestUser
(UserName, Password)
values
(@UserName, @Password)

gives invalid fn_md5.

maybe not registering correctly is the problem

 

[mrdenny]

What database did you create the fn_md5 function in? If in the local database this should work, if in master you will need to specify that when calling it. Please cut and paste the entire error message from QA that you get when you run this code.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[TRACEYMARYLAND]

Ok...i had the function in the wrong place....wrong database....

testing it in SQL Analyzer working...now.

I try and do an ASP page and see i get that to work aswell

Im getting there....

I let you know...if a while

 

[TRACEYMARYLAND]

oK just got it working in ASP too so all is great...

Thanks for your patient and help really appreciate it

 

[TRACEYMARYLAND]

One last little thing
if they forgot the password and now because it is encrypted is there a way to get it.....to tell the user what it was

 

[mrdenny]

Nope. You have to have them reset it. There is no way to decrypt an MD5 hash.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[TRACEYMARYLAND]

Thanks ...that will be alright more security that way