Here is a solution to add MD5 encryption to passwords in database of all user accounts.
It does require a bit of knowledge of MSSQL
First off you'll need a plugin dll to MSSQL which i have attached to this post, put it somewhere on the server (like c:\windows\system32 would be a good choice)
Fire up MSSQL Manager and go to Master database (in MSSQL 2005 its under system databases) go to programmability->Extended Stored Procedures and rightclick on it, select New Extended Stored Procedure. A window will popup asking for a name and a dll location.
Name: xp_md5
DLL: c:\windows\system32\xp_md5.dll (or wherever you put the dll)
Click ok. You should now see your new extended stored procedure called dbo.xp_md5 under the master database.
Close the master database and open your RYL user database (youxiuser) click on New Query up in the top of the management tools and paste this into the new box that popup.
-- Snip --
CREATE FUNCTION [dbo].[fn_md5]
(
@data AS TEXT
)
RETURNS VARCHAR(30) AS
BEGIN
DECLARE @hash VARCHAR(32)
EXEC master.dbo.xp_md5 @data, -1, @hash OUTPUT
RETURN @hash
END
-- End Snip --
Close user database again and open general MSSQL Security->Logins and open properties on your user account you use to access your databases. Click on User Mapping, click on Master database in the list and tick it and in the bottom window (database roles) it should only have tick in Public nothing else. Click ok.
Now you'll want to change the way passwords are inserted and read in the RYL stored procedures, so open up youxiuser again and go to programmability->stored procedures Right click on dbo.spUserAuthUID->Modify
Find the place that looks like this:
ALTER PROCEDURE [dbo].[spUserAuthUID]
@ID varchar(20),
@PASSWD varchar(30)
AS
..
Change the line: @PASSWD varchar(30) to: @PASS varchar(30)
Find the place that looks like this:
DECLARE @UID int
SELECT @UID = uid FROM usertbl WHERE account = @ID AND passwd = @PASSWD
IF @UID IS NULL
BEGIN
..
Insert these 2 lines after DECLARE @UID int
DECLARE @PASSWD VARCHAR(30)
SET @PASSWD = dbo.fn_md5(@PASS);
Click on execute.
Make this change to the stored procedure you use to insert users (like spJoinUser) [Change the length of pass in spJoinUser to varchar(30) instead of varchar(20)] and insert the same 2 lines again you did in previous stored procedure:
DECLARE @PASSWD VARCHAR(30)
SET @PASSWD = dbo.fn_md5(@PASS);
(do this after DECLARE @UID int)
Hit execute again.
--
Thats all you're done. Passwords of all users are now encrypted on both read and write.
If you wish to change passwords of users manually in your database you must first encrypt the password, you can do this by hitting: New Query when you are in the youxiuser database and write this: select dbo.fn_md5("cleartextpass"); and hit execute. It will then show you the encrypted password in a result box.
NOTE: You cannot convert encrypted passwords to clear text its a one way only conversion, so if someone forget their password it cannot be retrieved and you'll have to set a new password for that user instead.
I would advise you test if things are working after you're done with these steps. To do so insert a new user by right clicking on the spJoinUser stored procedure and click Execute. Fill in the details of the user and password (in clear text) and hit ok. Then go open your usertbl table and check that the user data is correct and that the password is encrypted. Should look something like this:
uid: whatever number is the newest user
account: test
passwd: eebf768ff8f49081d02d2be127f51a
jointime: blahblah
lastlogin: blahblah
..
Phew that was alot of writing
Enjoy.
It does require a bit of knowledge of MSSQL
First off you'll need a plugin dll to MSSQL which i have attached to this post, put it somewhere on the server (like c:\windows\system32 would be a good choice)
Fire up MSSQL Manager and go to Master database (in MSSQL 2005 its under system databases) go to programmability->Extended Stored Procedures and rightclick on it, select New Extended Stored Procedure. A window will popup asking for a name and a dll location.
Name: xp_md5
DLL: c:\windows\system32\xp_md5.dll (or wherever you put the dll)
Click ok. You should now see your new extended stored procedure called dbo.xp_md5 under the master database.
Close the master database and open your RYL user database (youxiuser) click on New Query up in the top of the management tools and paste this into the new box that popup.
-- Snip --
CREATE FUNCTION [dbo].[fn_md5]
(
@data AS TEXT
)
RETURNS VARCHAR(30) AS
BEGIN
DECLARE @hash VARCHAR(32)
EXEC master.dbo.xp_md5 @data, -1, @hash OUTPUT
RETURN @hash
END
-- End Snip --
Close user database again and open general MSSQL Security->Logins and open properties on your user account you use to access your databases. Click on User Mapping, click on Master database in the list and tick it and in the bottom window (database roles) it should only have tick in Public nothing else. Click ok.
Now you'll want to change the way passwords are inserted and read in the RYL stored procedures, so open up youxiuser again and go to programmability->stored procedures Right click on dbo.spUserAuthUID->Modify
Find the place that looks like this:
ALTER PROCEDURE [dbo].[spUserAuthUID]
@ID varchar(20),
@PASSWD varchar(30)
AS
..
Change the line: @PASSWD varchar(30) to: @PASS varchar(30)
Find the place that looks like this:
DECLARE @UID int
SELECT @UID = uid FROM usertbl WHERE account = @ID AND passwd = @PASSWD
IF @UID IS NULL
BEGIN
..
Insert these 2 lines after DECLARE @UID int
DECLARE @PASSWD VARCHAR(30)
SET @PASSWD = dbo.fn_md5(@PASS);
Click on execute.
Make this change to the stored procedure you use to insert users (like spJoinUser) [Change the length of pass in spJoinUser to varchar(30) instead of varchar(20)] and insert the same 2 lines again you did in previous stored procedure:
DECLARE @PASSWD VARCHAR(30)
SET @PASSWD = dbo.fn_md5(@PASS);
(do this after DECLARE @UID int)
Hit execute again.
--
Thats all you're done. Passwords of all users are now encrypted on both read and write.
If you wish to change passwords of users manually in your database you must first encrypt the password, you can do this by hitting: New Query when you are in the youxiuser database and write this: select dbo.fn_md5("cleartextpass"); and hit execute. It will then show you the encrypted password in a result box.
NOTE: You cannot convert encrypted passwords to clear text its a one way only conversion, so if someone forget their password it cannot be retrieved and you'll have to set a new password for that user instead.
I would advise you test if things are working after you're done with these steps. To do so insert a new user by right clicking on the spJoinUser stored procedure and click Execute. Fill in the details of the user and password (in clear text) and hit ok. Then go open your usertbl table and check that the user data is correct and that the password is encrypted. Should look something like this:
uid: whatever number is the newest user
account: test
passwd: eebf768ff8f49081d02d2be127f51a
jointime: blahblah
lastlogin: blahblah
..
Phew that was alot of writing
Enjoy.
