How can i encrypt passwords stored in my database?

At present, my passwords are stored in my database in plain text.

There is a webpage on my intranet that runs a SQL query to display the usernames and password in a table. The usernames and password are entered into the database through the submission of a form.  This is the only things that hat are does with the passwords, i  mean i do not use them to authenticate users etc.

I am wondering is there a way I can encrypt them so that you cannot just run a quesry and get them .... and then a way to decrypts them to display them in the page on the intranet.

How to do this or a good tutorial about it would be great, thanks
chrisVAsked:
Who is Participating?
 
g0rathCommented:
sorry haven't look on here at all today...

this is how you would do it

CREATE TABLE PwdTest
(
UserID smallint IDENTITY(1,1),
UserName varchar(12),
UserPassword varbinary(256)
)
GO

INSERT INTO PwdTest(UserName,UserPassword) VALUES('Billy Joe', pwdencrypt('This is my password'))
GO
SELECT pwdcompare('This is my Password',UserPassword,0) AS PasswordCheck FROM PwdTest
SELECT pwdcompare('This is NOT my Password',UserPassword,0) AS PasswordCheck FROM PwdTest

Here is the code above that would work...and you can use the PwdTest DDL to help you make sure your columns are of varbinary(256) that is all that gets stored in the database....not the password

hope that helps.
0
 
danblakeCommented:
You will need to buy a third-party utility or get some security specalist company involved such as Baltimore (alternativly you could try people like Ronald Rivest -- inventor of MD5 for some suitable programs/licensing agreement).
0
 
g0rathCommented:
MD5 is free all you have to do is provide a txtfile that gives credit for it.

I store passwords in my DB all the time, as char(32) and they are hashed using MD5. MD5 creates a unique 128bit fingerprint for data provided to it. Usually used to guarentee files, it's also used as a one way hash for passwords.

The string "EE is Cool" will create this string
48f2ec5cdf2cbacbb2772e49591c6ce3

The string "EE is Cool " will create this string
51a206a293456bd2559e0111ac674840

It's mathimatically "very very very difficult" to reverse the hash to the original string.

So for all my passwords I md5sum the entry and store in the database a 32char string. Then to verify the password I take the users password input apply the md5 to that string and then compare the two hashed strings. Non match is bad password, match is correct password.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
g0rathCommented:
http://userpages.umbc.edu/~mabzug1/cs/md5/md5.html

and it's not that difficult, they have ports almost everywhere...I ported the C version here to a C++ dll that I call from Visual Basic...but I understand you could also use the built in version in the WIN32 sdk
0
 
g0rathCommented:
of course if you wanted real encryption that I would do this

use blowfish to encrypt your string, and then apply a BASE64 encoding to it and store it as char() in the database. Blowfish can also be found easily
0
 
arbertCommented:
If you just want encryption for obscurity and you're not worried about access to the database, you could also do a convert on your data and change it to binary--then store it in your varchar field--like I said, this is mainly for display and not security.

select convert(varbinary(10),'arbert')      --returns 0x617262657274


select convert(varchar(10),0x617262657274)  --returns arbert




Brett
0
 
danblakeCommented:
I can't wait till we convert this to an xp_ using the CLR  in Yukon...
0
 
chrisVAuthor Commented:
Thanks .... I will read up on MD5 and the other comments,

... the password are not that important really, i just want to impress the boss a bit (who knows very little about encryption and the like (just like me))

The only time the password will be used is inserting them in the database and then doing a select statement and displaying them on a prowser.

So if i could do something like apply some simple encryption into my INSERT statement to put the password in my database and then reverse apply the enryption in my SELECT statement to retrieve the password that woould be fine.

Is this easy/possible to do?
0
 
g0rathCommented:
yes....undocumented feature

DECLARE @Encrypt varbinary(256)
DECLARE @Decrypt varchar(256)
SET @Encrypt = (SELECT pwdencrypt('foo'))
PRINT @Encrypt
PRINT pwdcompare('foo',@Encrypt,0)
PRINT pwdcompare('foo1',@Encrypt,0)

type this in the query analyzer and see....

pwdencrypt(STRING) returns a varbinary(256) hash

pwdcompare(PASSWORD_TEST,HASHVALUE,0) returns 0 for false and 1 for match and true
0
 
g0rathCommented:
this works with SQL server 2000, and with all undocumented features this is here today, but may be gone tommorow....so third party is better...written by your team is even better....as in md5
0
 
arbertCommented:
It will be gone tomorrow (well, next year).  Those procs aren't in Yukon....

If you just want to insert jumbled data--do the convert statement to binary on the insert...
0
 
arbertCommented:
Take that back--just found it in Yukon--wrong server...Still undocumented procs/functions should be avoided.
0
 
danblakeCommented:
The official license agreement is here: http://www.ietf.org/ietf/IPR/RSA-MD-all
from Rons home-website - http://theory.lcs.mit.edu/~rivest/
0
 
chrisVAuthor Commented:
g0rath, is that i stored procedure oe something, it's looks alien to me !?!
and ican't download anything form the net at work so i'll have to take a look at MD5 at home
____________
arbert,
how can i do:
select convert(varbinary(10),'arbert')

and
select convert(varchar(10),0x617262657274)

in ASP/VbScript

I when i tryed doing it, as above, i get an error:
Type mismatch: 'varbinary'
___________________
0
 
Scott PletcherSenior DBACommented:
Instead you could:

1) rename the table
2) create a view the same name as the existing table but *without* the password column

Give others only access to the view.  Then they can't even *see* the password, which is much safer.
0
 
g0rathCommented:
yes that is an undocumented stored procedures....which is why I provided the T-SQL so that you could see it's usage
0
 
arbertCommented:
"arbert,
how can i do:
select convert(varbinary(10),'arbert')

and
select convert(varchar(10),0x617262657274)

in ASP/VbScript"


How do you do your selects and inserts now?  Same way--against the connection object or with a stored proc....
0
 
chrisVAuthor Commented:
g0rath i have never userd a stored procedure, or T-Sql before, so do i :
1) save:
____
DECLARE @Encrypt varbinary(256)
DECLARE @Decrypt varchar(256)
SET @Encrypt = (SELECT pwdencrypt('foo'))
PRINT @Encrypt
PRINT pwdcompare('foo',@Encrypt,0)
PRINT pwdcompare('foo1',@Encrypt,0)
____
as a stored procudure in SQL server called "encrypt"

2) when inserting the passsward do:
INSERT INTO table (password) VALUES ('" & encrypt(strpass) & "')

3) when retrieving the password do:
SELECT ....<how would I do this>
____________________________________________________________________________________
arbert, I tried it like this:
INSERT INTO table (password) VALUES ('" & convert(varbinary(10),strpass)  & "')

and
SELECT password FROM table ............
then do convert(varchar(10),<sql output>) on the results of the SQL statement

but i get an error on the INSERT statement:
Type mismatch: 'varbinary'

why is this?
________________________________________

Sorry if these are dump questions, but I really appreciate the help...



0
 
arbertCommented:
You wouldn't put single quotes around the convert function:

INSERT INTO table (password) VALUES (" & convert(varbinary(10),strpass)  & ")
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.