October 19, 2009 at 11:36 am
Hi,
I am using the EncryptByPassPhrase() function to enrypt the user passwords but everybody can see my StoredProcedure to retrieve back the user password . How can i hide it or store this password as HASH in some table . Any input will be appeciated.
Thanks
SC
October 19, 2009 at 11:46 am
1. Add "WITH ENCRYPTION immediately before the AS statement..
CREATE PROCEDURE dbo.ProcedureName
@Variable1 int
WITH ENCRYPTION
AS
Save a copy of your sproc elsewhere.. Lock it down.. That sproc can not not be viewed..
CEWII
October 19, 2009 at 12:00 pm
Thanks for your reply.
But now my question is "IS there a way to break it" if i encrypt the stored procedure as you suggested.
Thanks
SC
October 19, 2009 at 12:22 pm
I believe the answer is yes. I haven't seen it myself though. In SQL 2000 it was EASY. In 2005+ I don't think it is so easy.. You should do a search in google. I don't want to do it from my office, sorry. If you find a solution, make sure that they are not talking about 2000. I believe there may be commercial tools, but I haven't seen any free tools for 2005+, but it has been a while..
At the very least it keeps out the casual looky-loos out.
CEWII
October 19, 2009 at 12:30 pm
Thanks CEWII.
Is there a way to encrypt the Stored Procedure with Password?
Thanks
Shailesh
October 19, 2009 at 12:41 pm
Don't encrypt the password. Store is as a hash.
October 19, 2009 at 12:50 pm
Shailesh Chaudhary (10/19/2009)
Thanks CEWII.Is there a way to encrypt the Stored Procedure with Password?
Thanks
Shailesh
No. Sorry..
CEWII
October 19, 2009 at 12:57 pm
Michael Valentine Jones (10/19/2009)
Don't encrypt the password. Store is as a hash.
This *may* be the right method..
However, if there is any need to get the clear-text version again this is NOT the right method.
With that said if you are just storing passwords for your app that will be used for app authentication then the hash method is by far the easiest, and doesn't require you to have to deal with encrypted passwords.
HASHBYTES is probably the method to look at..
http://msdn.microsoft.com/en-us/library/ms174415.aspx
you store the username and the password hash. When you get the password at login you hash the password the user just entered and compare hashes, if they match, they are the same..
Clear?
CEWII
October 20, 2009 at 3:11 pm
Thanks CEWII for your response.
I have my password field in existing database as varchar(80). I want to join userName + password and use the HASHBYTES function to store it in the database password field. Is it possible I am doing this but it is showing me only some garbage. Please advice.
DECLARE @HashThis varchar(80);
SELECT @HashThis = CONVERT(varchar(80),'superpass');
SELECT HashBytes('SHA1', @HashThis);
update users set password = HashBytes('SHA1', @HashThis);
GO
Thnaks
SC
October 20, 2009 at 3:31 pm
That garbage is the hashed value.. That is what you store..
When you get a username and password you also need to normalize case of the username and password.
DECLARE @UserName varchar(30),
@Password varchar(20),
@HashedValue varchar(80)
SET @UserName = 'MyUserName'
SET @Password = 'Password'
SELECT 'MixedCaseHash' = HASHBYTES('SHA1', @UserName + @Password )
SELECT 'NormalizedCaseHash' = HASHBYTES('SHA1', UPPER(@UserName) + UPPER(@Password) )
GO
When the user passes their username and password you do exactly the same thing and compare the hashes, if the hashes match then the passwords match..
Here is some sample code that would need some attention on your side..
IF ( EXISTS ( SELECT 'X'
FROM dbo.userstable
WHERE PasswordHash = HASHBYTES('SHA1', UPPER(@UserName) + UPPER(@Password) ) ) )
BEGIN
PRINT 'Password is good'
END
ELSE
BEGIN
PRINT 'Password is bad'
END
GO
Also, I like the idea of adding the username to the hash, it adds salt to the value so that even if the password is the same as another user you couldn't tell that by just looking at the hash..
CEWII
October 20, 2009 at 4:22 pm
Can i Store this garbage value returned by HASHBYTES function as String using the function sys.fn_varbintohexstr(@hash)
Thanks
SC
October 20, 2009 at 4:25 pm
can I store this garbage value returned by HASHBYTES() as string using the function sys.fn_varbintohexstr(@hash) ?
Thanks
SC
October 20, 2009 at 4:44 pm
I don't see much value in doing that, why don't you chage the datatype to varbinary(80) and be done with it.. Storing it as text doesn't buy you anything really..
CEWII
October 20, 2009 at 5:24 pm
Because i Am syncing the Client from server and sending this password to the the client machine. This garbage data will be very difficult to sync as i am using the special characters in flat files to sync. If i get the data as string it will be easier to me as system is alerady designed and cannot change in the password datatype now.
Thanks
SC
October 20, 2009 at 6:26 pm
Ok.. At least there is a reason..
DECLARE @UserName varchar(30),
@Password varchar(20),
@HashedValue varchar(80)
SET @UserName = 'MyUserName'
SET @Password = 'Password'
SELECT 'MixedCaseHash' = HASHBYTES('SHA1', @UserName + @Password )
SELECT 'NormalizedCaseHash' = HASHBYTES('SHA1', UPPER(@UserName) + UPPER(@Password) )
SELECT 'HASHBYTE-2-VARCHAR' = CONVERT(varchar(80),HASHBYTES('SHA1', UPPER(@UserName) + UPPER(@Password) ))
SELECT 'HASHBYTE-2-HEX-2-VARCHAR' = CONVERT(varchar(80),sys.fn_varbintohexstr(HASHBYTES('SHA1', UPPER(@UserName) + UPPER(@Password) )))
GO
I think that will be ok, just be sure to do the same thing to the value the user passes when you compare hashes. Which reminds me.. Where are you doing the hash compare? At the web server or at the DB server?
CEWII
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply