Best Encryption method

Question

Best Encryption method

JoyKing

Ten Centuries

Points: 1106
More actions
September 9, 2008 at 8:47 am

#197416

Hi,
In one of my project I am using Hashbytes('SHA1') encryption method to store user password.
As per my understanding this password cannot be decrypted.
Can any one tell me is this is the best method of encryption or not.
If no what are the other alternatives I have?
I am using MS SQL Server 2005.
Thanks,
Keerthy

Viewing 12 posts - 1 through 11 (of 11 total)

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

dmc-608719 SSCarpal Tunnel Points: 4663 More actions · Answer 1

It depends on your needs. There are various encryption methods that can be used both internal and external to SQL. Usually its a trade-off between performance and the quality of encryption.

From what I have read the use of Asymmetric keys is the most secure method, but its the slowest. So you could use that with the function EncryptByAsmKey(). Microsoft documentation indicates to use a symmetric key for better performance. And finally encrypt by password is the weakest.

For general password I have always used external code like .net to hash and store passwords as they are stored/retrievefrom the database. I always placed it there because until recent version it was never very robust to store using SQL Code.

If you don't want to be able to retrieve the password I think the HashBytes is a sufficient method to use inside the database for general security.

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

Not sure about that algorithm, but one way hashes seem to work well. Just be careful that someone can't copy the hash and submit that in your application. Only plaintext should be accepted and you should run the hash yourself.

JoyKing Ten Centuries Points: 1106 More actions · Answer 3

Thanks for your inputs,

Is there is any method to encrypt the stored procedure other than "with encryption" (RC4 Method) method.

As I need to send all my procedures to my client I wanted to secure this data so that he cannot access the procedure logic.

I can use "With Encryption" but it can be easily decrypted.

Advance thanks,

Keerthy

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Not really. The thing is, the SQL engine needs to be able to decrypt the procedure in order to compile and run it, so you can't use some third party form of encryption unless you can modify the SQL engine itself.

Does the SQL server that the procedures are going on to belong to your client?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dmc-608719 SSCarpal Tunnel Points: 4663 More actions · Answer 5

The with Encryption is the only way I know. If you are concerned about providing it, maybe creating a CLR stored procedure is a better option. Compile a dll and obfuscate it or otherwise protect it.

bcronce SSCrazy Points: 2597 More actions · Answer 6

Remember to put some salt on that hash, incase someone get ahold of your DB some how. SHA1 should be plenty for storing someone's password. HASH's work great anytime you only need to compare two inputs without ever knowing what they originally were. You don't need to know a users password, just that they're the same.

JoyKing Ten Centuries Points: 1106 More actions · Answer 7

Thanks a lot for your inputs.

Gila Shaw: Yes, We need to deploy all procedures in the client environment (Database Server).

Ross McMicken SSCarpal Tunnel Points: 4436 More actions · Answer 8

Does it really matter if the client sees the logic? With a one way hash/encryption routine, the logic is going to be pretty standard. PGP Corporation will send you their source code for testing, so they aren't afraid of you knowing how the process works.

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

Hiding your logic from clients is overrated. If that's all you're selling them with the software, you're not providing much service. Don't forget that you schema, the code oyu use, etc. is subject to copyright. clients can't just copy it and use it.

Most clients will never decrypt the procedures, heck most of them don't care. That's why they're buying your software. They don't have the time or inclination to write it themselves.

Jacob Luebbers SSCarpal Tunnel Points: 4834 More actions · Answer 10

It's trivial to get the plaintext versions of encrypted stored procs as long as you have sysadmin privileges - if your client wants to decrypt them they will. To be honest I don't know why vendors bother...

Regards,

Jacob

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 11

Going back to the original question about encryption methods please keep in mind there are vendors out there eager to sell you encryption-appliances like Ingrian, etc.

You are going to find these vendors have got SOX, etc certifications which is something Business might like. 😉

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.