SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Best Encryption method


Best Encryption method

Author
Message
JoyKing
JoyKing
SSC Veteran
SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)

Group: General Forum Members
Points: 250 Visits: 432
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
dmc-608719
dmc-608719
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1181 Visits: 787
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
Steve Jones
SSC Guru
SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)

Group: Administrators
Points: 147619 Visits: 19436
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
JoyKing
JoyKing
SSC Veteran
SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)

Group: General Forum Members
Points: 250 Visits: 432
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)

Group: General Forum Members
Points: 226795 Visits: 46334
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
dmc-608719
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1181 Visits: 787
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
bcronce
SSChasing Mays
SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)

Group: General Forum Members
Points: 633 Visits: 517
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
JoyKing
SSC Veteran
SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)

Group: General Forum Members
Points: 250 Visits: 432
Thanks a lot for your inputs.

Gila Shaw: Yes, We need to deploy all procedures in the client environment (Database Server).
Ross McMicken
Ross McMicken
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1101 Visits: 2250
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
Steve Jones
SSC Guru
SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)

Group: Administrators
Points: 147619 Visits: 19436
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search