password encryption

Hi all Im used to work on mysql and in that Db you can call the password function to encrypt password, so that people browsing directly the db can't see others password.

What is the best way to do this in sqlserver ?

You may use these two undocumented SQL Server functions.

PWDEncrypt and PWDCompare

How to use them?
http://msmvps.com/blogs/gladchenko/archive/2005/04/06/41083.aspx

Pwdencrypt() Weakness
http://www.sqlteam.com/article/pwdencrypt-weakness

Good luck.

|||

so as I see it. It seems easy to hack, So I will ask an other question to you. How do you procede to encrypt password? do you encrypt in code instead of in the db?|||

Frist thing you need to make Password column as VarBinary.

It will save in encrypted format. If you dont wanna use builtin function then make some function which will add some values and then subtract some values.

Hope this will help you.

DBMaster

My Blog

|||

You could use varbinary, you could also use binary, since the results will always be the same lengh.

Public Function MD5(s as string) as byte()

Dim encoder as New UTF8Encoding()
Dim md5Hasher as NewSystem.Security.Cryptography.MD5CryptoServiceProvider

return md5Hasher.ComputeHash(encoder.GetBytes(s))

end function

dim cmd as new SqlCommand("INSERT INTO Users(UserName,Password) VALUES (@.UserName,@.Password)",conn)

with cmd.parameters

.add("@.UserName",sqldbtype.varchar).value=txtUsername.text

.add("@.Password",sqldbtype.varBinary).value=md5(txtPassword.text)

end cmd

...

dim cmd as new SqlCommand("SELECT COUNT(*) FROM Users WHEREUserName=@.UserName ANDPassword=@.Password",conn)

with cmd.parameters

.add("@.UserName",sqldbtype.varchar).value=txtUsername.text

.add("@.Password",sqldbtype.varBinary).value=md5(txtPassword.text)

end cmd

if cmd.executescalar<>1 then

throw new applicationexception("Bad password")

endif