Solved

How can i encrypt passwords stored in my database?

Posted on 2004-03-25
19
1,826 Views
Last Modified: 2007-12-19
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
0
Comment
Question by:chrisV
  • 7
  • 5
  • 3
  • +2
19 Comments
 
LVL 13

Expert Comment

by:danblake
ID: 10678036
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
 
LVL 5

Expert Comment

by:g0rath
ID: 10678225
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
 
LVL 5

Expert Comment

by:g0rath
ID: 10678265
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
 
LVL 5

Expert Comment

by:g0rath
ID: 10678289
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
 
LVL 34

Expert Comment

by:arbert
ID: 10678305
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
 
LVL 13

Expert Comment

by:danblake
ID: 10678307
I can't wait till we convert this to an xp_ using the CLR  in Yukon...
0
 

Author Comment

by:chrisV
ID: 10678407
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
 
LVL 5

Expert Comment

by:g0rath
ID: 10678625
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
 
LVL 5

Expert Comment

by:g0rath
ID: 10678653
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 34

Expert Comment

by:arbert
ID: 10678749
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
 
LVL 34

Expert Comment

by:arbert
ID: 10678772
Take that back--just found it in Yukon--wrong server...Still undocumented procs/functions should be avoided.
0
 
LVL 13

Expert Comment

by:danblake
ID: 10678807
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
 

Author Comment

by:chrisV
ID: 10678883
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 10679110
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
 
LVL 5

Expert Comment

by:g0rath
ID: 10679115
yes that is an undocumented stored procedures....which is why I provided the T-SQL so that you could see it's usage
0
 
LVL 34

Expert Comment

by:arbert
ID: 10682648
"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
 

Author Comment

by:chrisV
ID: 10685875
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
 
LVL 34

Assisted Solution

by:arbert
arbert earned 20 total points
ID: 10688178
You wouldn't put single quotes around the convert function:

INSERT INTO table (password) VALUES (" & convert(varbinary(10),strpass)  & ")
0
 
LVL 5

Accepted Solution

by:
g0rath earned 30 total points
ID: 10690888
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now