how to decrypt data from sql server

Question

how to decrypt data from sql server

paras_98

SSCrazy

Points: 2994
More actions
September 4, 2001 at 11:47 am

#104567

I have stored my data my using insert into table1(password) values (encrypt('mypassword')). Now how can I display this password field onto the front-end (ASP/VB). I shall see only encrypted data.
I want to decrypt data, because I have been storing data so that no one... even the system admin cannot see the data. But when the user logs into his account, I would like him to see his data in the format that he has entered, and not in encrypted junk format.
So please do let me know whats the turnaround.
Thankx
Paras Shah
Evision Technologies
Mumbai, India

Paras Shah
Evision Technologies
Mumbai, India

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Brian Knight One Orange Chip Points: 27249 More actions · Answer 1

I'm afraid there's not really an easy way to do this. Check out Les Smith's java encryption article on this. The encrypt function has no decrypt counterpart. It's only meant for validation decryption. Here's my article on the function.

http://www.sqlservercentral.com/columnists/bknight/encryptfunction.asp

Care to write a COM function with me to do this? I'm in the middle of doing one now.

Brian Knight

bknight@sqlservercentral.com

http://www.sqlservercentral.com/columnists/bknight

Brian Knight
Free SQL Server Training Webinars

patilds SSChasing Mays Points: 615 More actions · Answer 2

The Encrypt function encrypts the data but it is intended to use sys tables only. So even if u r writing

Select * from login where username='ABC' and password=Encrypt('Password')

or

Select * from login where username='ABC' and password='Password'

will generate the same result!!!.

Also there is no decrypt function in SQL Server. As Brian Knight says u will have to write COM function for this. I have already written the same. Will help you if required.

Deepak Patil

paras_98 SSCrazy Points: 2994 More actions · Answer 3

then .. what is the best way we can encrypt the data while storing it and also retrive it while getting it to the frontend.

Has anyone made a stored procedure to encrypt in a proprietory format.

Paras Shah

Evision Technologies

Mumbai, India

Paras Shah
Evision Technologies
Mumbai, India

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 4

The best solution, IMHO, is to use one of the commercially available and readily known encrpytion algorithms. They biggest part of the encryption is the key. Keep in mind that almost any encryption, actually, all encrytpion can be cracked, given time and desire. You want to make it hard, so choosing a good key and securing the key is the best.

You need to secure the key in the front end, by most likely hiding it in the registry or on the disk and then having the app use this key to encrypt/decrypt the data. Send the encrypted string to/from SQL using normal ADO methods.

Steve Jones

steve@dkranch.net

paras_98 SSCrazy Points: 2994 More actions · Answer 5

How is this possible? I have got hang of software that are able to decrypt encrypted stored procedures. But I want to decrypt the value in the field(column). Please let me know if you bounce across something.

Thankx

quote:
The Encrypt in sql is a HASH encryption. This means that it can never be decrypted. the most you can do is encrypt another value and compare it to the stored value. If you need to decrypt, you can pick any of a million activex controls and use sp_OACreate to call it from a sproc.

Paras Shah

Evision Technologies

Mumbai, India

Paras Shah
Evision Technologies
Mumbai, India

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 6

Even if it's a hash, since it can be duplicated, there has to be a way to decrypt it. Nobody has spent the time to do it, including myself, though I'm not sure I have the patience or brains to figure it out.

Steve Jones

steve@dkranch.net

Robert Vallee Right there with Babe Points: 786 More actions · Answer 7

I agree with Steve. Any encrption can be broke given time, hash encrption included. Look at L0phtCrack. It is able to do hash decryption against NT passwords and if memory serves me that is what SQL2000 is using.

Robert Vallee Right there with Babe Points: 786 More actions · Answer 8

One last thing. L0phtCrack as far as I know can't decrypt SQL2000. I did want to miss lead you in thinking that there is something out there that you can use.

don1941 SSCarpal Tunnel Points: 4183 More actions · Answer 9

quote:
I have stored my data my using insert into table1(password) values (encrypt('mypassword')). Now how can I display this password field onto the front-end (ASP/VB). I shall see only encrypted data.
I want to decrypt data, because I have been storing data so that no one... even the system admin cannot see the data. But when the user logs into his account, I would like him to see his data in the format that he has entered, and not in encrypted junk format.

Several scripts posted recently on encrypting and decrypting data. I suggest you trot on over to the scripts area and pick some low-hanging fruit and use in your data base.