Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Best Encryption method Expand / Collapse
Author
Message
Posted Tuesday, September 9, 2008 8:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 11, 2013 5:24 AM
Points: 40, Visits: 431
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
Post #566218
Posted Tuesday, September 9, 2008 2:45 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:22 AM
Points: 375, Visits: 765
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.
Post #566487
Posted Tuesday, September 9, 2008 9:24 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 5:38 PM
Points: 31,368, Visits: 15,834
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
Post #566575
Posted Wednesday, September 10, 2008 9:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 11, 2013 5:24 AM
Points: 40, Visits: 431
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
Post #567017
Posted Wednesday, September 10, 2008 9:33 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 40,615, Visits: 37,080
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 2008, MVP
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

Post #567047
Posted Wednesday, September 10, 2008 9:59 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:22 AM
Points: 375, Visits: 765
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.
Post #567074
Posted Wednesday, September 10, 2008 11:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 3, 2012 7:36 AM
Points: 127, 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.
Post #567175
Posted Thursday, September 11, 2008 12:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 11, 2013 5:24 AM
Points: 40, Visits: 431
Thanks a lot for your inputs.

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

Post #567492
Posted Monday, September 15, 2008 6:21 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 11:26 AM
Points: 356, Visits: 1,993
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.
Post #569370
Posted Monday, September 15, 2008 6:34 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 5:38 PM
Points: 31,368, Visits: 15,834
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
Post #569381
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse