Encrypt text
-
Hello,
we store login and password in a table from a database and we would like to encrypt the stored login/password. Does anybody know to do?
-
If you don't need password recovery, there is an encrypt() function that does a one way encryption.
-
There is a serious problem with using the encrypt() function. This has got to be one of the simplest algorithms on earth to crack. All it really prevents are non-technical individuals from seeing the raw data that has been encrypted. As an example:
Run:
select encrypt('What''s going on')
Output:
--------------------------------------------------------------
0x570068006100740027007300200067006F0069006E00670020006F006E00
(1 row(s) affected)
This equates to:
0x57 = 87 = W
0x68 = 104 = h
etc........
If you are not worried about recovery of the password you could write a stored procedure that utilizes a more secure hashing algorithm than the Hex conversion with a 00 concat (actually the encrypt() function is not a hash, but I'm not going to split hairs). There are quite a few places you can find examples of hash algorithms on the net for use (MD5 etc...). If you need to recover the password there are also other encryption algorithms (TEA, Vigenere, RC4, etc...) available on the net, the issue with most of these is that they are keyed.
I happen to use an extended stored procedure that is an implementation of the MD5 hash written in managed C++. It works pretty well.
Example (same chars as above):
Exec dbo.xp_md5 'What''s going on'
Output:
MD5
--------------------------------
a77c5b70d08458f9829f759b43652b2d
(1 row(s) affected)
If you didn't know what the input was on this, good luck trying to figure it out.
HTH
-
Take a look at the PWDENCRYPT and PWDCOMPARE functions in BOL. Stores one way hash using AES standard.
Example:
select PWDENCRYPT('abcdE')
0x0100E31F297DDB71A4F75A2B0C7E8256CFB0AF0884029F5208DF197F2B04F9FA264FC1E4636636E431934BC24CCA
select PWDCOMPARE('abcde',PWDENCRYPT('abcde'))
-----------
1
select PWDCOMPARE('guess',PWDENCRYPT('abcde'))
-----------
0
-
Pwdencrypt and pwdcompare functions are not supported by Microsoft and are totally undocumented. Therefore, the encryption and hashing algorithms used are subject to change at any time, and you will be completely on your own when you got problems with a new SP/version.
Check out the http://www.vtc.ru/~andrey/xp_crypt link. You can download a free copy of extended stored procedures to perform encryption of data and store the encrypted data in a field using a stored procedure. (It has some limitations on the used algorithms and the length of the source data though...)
-
Excellent point Katya.
More info on the can be found at the following link, which correctly cautions against using internal MS functions and shows some of the troubles you can run into during upgrades.
-
thanks for all
-
Where can I get xp_md5? We're storing the password using MD5 encryption on the front-end using Cold Fusion's "hash" function. I also need to have this same function at the SQL level to write some stored procedure against.
The Cold Fusion's hash of 'What''s going on' matches your xp_md5 output.
Thanks!
-
Sir, Can u say how can we use the pwdcompare() without using pwdencrypt()?
-
Sir, Can u say how can we use the pwdcompare() without using pwdencrypt()?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply