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 «««23456

HASHBYTES Expand / Collapse
Author
Message
Posted Friday, February 10, 2012 8:08 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:46 AM
Points: 845, Visits: 2,331
cengland0 (2/10/2012)

Someone mentioned in another article that all you need to do is run a trace and you can get all this information. So, should the hashing be done on the application side to prevent this type of password retrieval with trace on?


In general, you simply have to choose how hard you want which potential adversaries to work using which means.

I would say hashing should be done on the application side at this point, because
A) SQL Server does not provide us modern hash algorithms (i.e. the SHA-2 family; SHA-512, SHA-256, SHA-384, Whirlpool, the SHA-3 finalists, etc.)
B) SQL Server does not provide us with an implementation of PBKDF2; even the SHA-1 only version the .NET framework gives us
C) Conditional: When a single SQL Server instance is serving multiple client machines, loading SQL Server up with hundreds of thousands (or more) of hash iterations in order to allow a reasonable measure of security for users who use passphrases/passwords with a smaller total keyspace may end up putting more load on the CPU of the SQL box than is desirable
C1) Do you really want to try to use GPGPU computation on a SQL Server instance from inside SQL?

Raghavendra Mudugal (2/10/2012)

I thought SALT is fixed static string in the encrypted SP... you dont need to expose the SALT string as open parameter value, that needs to be kept safe. and on the password use the same concept what you are using now... SALT'ing is just added feature to HASH'ing. Let DB things remain on the DB side, if you want use something more on UI level, you can use any encryption concept besides HASH'ing.


Adding a salt in and of itself is merely a device to 1) prevent the trivial identification of the same cleartext (i.e. hash A5BC is present in rows 5, 15, 33, and 114; they have the same cleartext value!) and 2) render more difficult precomputed dictionaries.

Using a fixed static string completely precludes reason 1) as the same cleartext + the same salt = the same hash, and nearly completely undermines reason 2), since once an attacker figures out the fixed single salt, they can proceed with precomputing a massive dictionary, and the result takes up the same space (and takes the same amount of time) as it would have without any salt at all.

Salts need to be significantly long and completely random; feel free to add _more_ content, but the useful core of it is a set of random bytes of significant length.
Post #1250333
Posted Friday, February 10, 2012 8:43 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, November 13, 2013 1:13 PM
Points: 134, Visits: 101
Raghavendra Mudugal (2/10/2012)
cengland0 (2/10/2012)
Raghavendra Mudugal (2/10/2012)
cengland0 (2/10/2012)
Steve Jones - SSC Editor (2/9/2012)

SELECT HASHBYTES( 'SHA1', 'R@nd0m' + Pwd + firstname)
FROM Employees

In this case, without access to the code, it becomes hard to determine what the input values for the hash function are.

Someone mentioned in another article that all you need to do is run a trace and you can get all this information. So, should the hashing be done on the application side to prevent this type of password retrieval with trace on?


just a thought - if that SP is encrypted then finding that in the TRACE is not possible (not sure if secured info are generally passed in the plane SQL statements.

So you're saying to put the query into an encrypted stored procedure to avoid the trace from getting the passwords? How do you pass parameters to that stored procedure? Wouldn't you do something like:

EXEC sp_somestoreprocedure('salt','password')

Couldn't that command be captured by the trace?


I thought SALT is fixed static string in the encrypted SP... you dont need to expose the SALT string as open parameter value, that needs to be kept safe. and on the password use the same concept what you are using now... SALT'ing is just added feature to HASH'ing. Let DB things remain on the DB side, if you want use something more on UI level, you can use any encryption concept besides HASH'ing.


First no need to send the SALT string and the password altogether to the database. Simply get the hashed(and salted) password from DB to the application. This can then be compared by hashing (and salting) the user entered password from UI for further validation in the application end. Hence no need to worry about whether the password/salt will be caught in trace or in transportation layer. This works when the SALT is a static string or user name.

Ofcourse, there are more ways to implement it, but I am saying one of them.
Post #1250371
Posted Friday, February 10, 2012 9:24 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 10:45 AM
Points: 673, Visits: 1,551
SathishK (2/10/2012)
Raghavendra Mudugal (2/10/2012)
cengland0 (2/10/2012)
Raghavendra Mudugal (2/10/2012)
cengland0 (2/10/2012)
Steve Jones - SSC Editor (2/9/2012)

SELECT HASHBYTES( 'SHA1', 'R@nd0m' + Pwd + firstname)
FROM Employees

In this case, without access to the code, it becomes hard to determine what the input values for the hash function are.

Someone mentioned in another article that all you need to do is run a trace and you can get all this information. So, should the hashing be done on the application side to prevent this type of password retrieval with trace on?


just a thought - if that SP is encrypted then finding that in the TRACE is not possible (not sure if secured info are generally passed in the plane SQL statements.

So you're saying to put the query into an encrypted stored procedure to avoid the trace from getting the passwords? How do you pass parameters to that stored procedure? Wouldn't you do something like:

EXEC sp_somestoreprocedure('salt','password')

Couldn't that command be captured by the trace?


I thought SALT is fixed static string in the encrypted SP... you dont need to expose the SALT string as open parameter value, that needs to be kept safe. and on the password use the same concept what you are using now... SALT'ing is just added feature to HASH'ing. Let DB things remain on the DB side, if you want use something more on UI level, you can use any encryption concept besides HASH'ing.

Simply get the hashed(and salted) password from DB to the application. .


how do you suggest to plan this? first to "get" you need to "set", and how are you going to get by bypassing the trace?

(your comments has created a self loop here)


ww; Raghu
--
There are only 10 types of people in the world, those who understand binary, and those who don't.

Note: (as of now) only.. 1 and 4 applies (i am on my way...)
Post #1250406
Posted Friday, February 10, 2012 9:43 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354, Visits: 1,299
Raghavendra Mudugal (2/10/2012)

how do you suggest to plan this? first to "get" you need to "set", and how are you going to get by bypassing the trace?
(your comments has created a self loop here)

Seems the database would have two columns. One for Salt and one for HashedPassword. Then, you have your application do the hashing with the RANDOM salt. You store both in the database.

Reverse engineering a HashedPassword is difficult unless you already have several commonly used password that are in a table and you compare it with the HashedPassword column. This will then give you a list of users that contain the same hash values as your precompiled table. This does not mean the user had the same password but does mean your password will hash to the same value and let you sign in. One hashed value can usually be achieved by many different clear-text passwords.

So, to prevent these "dictionary attacks," use a salt value which changes the final hash value for the stored password. Processing these through a dictionary table is useless because all users have a different salt value. You would have to have a pre-compiled dictionary table for each user in the database which gets much more difficult to process. The chances of you getting a match this way is statistically 0% unless you have several years to brute force it. Generally companies require you to change your passwords every 30, 60, or 90 days so a brute force doesn't work in these cases either.

Sending the clear text from the application to the database is dangerous because the trace command can pick that up and be captured by anyone with full access to the database. Having it hashed in the application makes it so only the salt and hashedpassword values are sent to the database and they are no longer clear text at that point. You can look in the table or do whatever trace command you want and you will not be able to see the user's original password anywhere in the database.

The only method for acquiring these passwords is if you use a network sniffer that captures the packets from the client's computer to the web server. And, this is only easy if you're not using an https server and still on a simple unencrypted http server.
Post #1250423
Posted Friday, February 10, 2012 10:59 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, November 13, 2013 1:13 PM
Points: 134, Visits: 101
Seems the database would have two columns. One for Salt and one for HashedPassword. Then, you have your application do the hashing with the RANDOM salt. You store both in the database.


Usually we can use the user ID column so that SALT will be unique for each user though more than one have same password.

Reverse engineering a HashedPassword is difficult unless you already have several commonly used password that are in a table and you compare it with the HashedPassword column.


Hashed password can never be reverse engineered (as in the case of encryption). The only possible way is bruteforce attack with the help of rainbow table. Ofcourse, there are other ways like capturing key stroke.

Again this can further be controlled by limiting incorrect password attempts combined with strong hash algorithm.
Post #1250463
Posted Friday, February 10, 2012 11:51 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Yesterday @ 11:24 AM
Points: 32,781, Visits: 14,942
Nadrek (2/10/2012)

I would say hashing should be done on the application side at this point, because
A) SQL Server does not provide us modern hash algorithms (i.e. the SHA-2 family; SHA-512, SHA-256, SHA-384, Whirlpool, the SHA-3 finalists, etc.)


One note here: SQL Server 2012 will add SHA2







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1250484
Posted Saturday, February 11, 2012 4:00 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 8:37 AM
Points: 8,287, Visits: 8,738
cengland0 (2/10/2012)
Generally companies require you to change your passwords every 30, 60, or 90 days so a brute force doesn't work in these cases either.

Two points here: The companies which do this so it to their own staff, not to their customers - so customer passwords have to be safe for a much longer time than the internal passwords to which these change frequencies apply. So something good enough to be safe for rather more than several years will be needed, unless the attitude to customer security changes.
Besides, the pasword I use for my most secure stuff is a couple of hundred characters long and I'm not going to create and learn to remember a new one of those every 90 days. I lost my PGP keys way back when because I was persuaded to change my passphrase regularly - and of course couldn't even revoke them, so they are still on public servers somewhere, since only someone who knows them can revoke them.
Sending the clear text from the application to the database is dangerous because the trace command can pick that up and be captured by anyone with full access to the database. Having it hashed in the application makes it so only the salt and hashedpassword values are sent to the database and they are no longer clear text at that point. You can look in the table or do whatever trace command you want and you will not be able to see the user's original password anywhere in the database.

One can perfectly well use encryption (preferably asymmetric, of course) between the database and the app, and do the hashing in the database - or one could if the database were capable of doing the hashing; for long term high security, SQL Server currently isn't.
The idea that passwords have to be passed to the database in unencrypted form is just plain false, and is not a reason for not doing the hashing in the database. The real reason is the SQL Server doesn't have the required hash functions. SQLS 2012 will improve things a little with the addition of SHA2-256 and SHA2-512 to the hashbytes repertoire, but it's still missing the crucial thing: a seriously slow hash; and that means that it can't do the hashing needed for lasting high security.
Of course banks and investment managers and so on providing customers with a web interface don't have a security model that delivers either high security or lasting security - they impose nonsense like a password must be alphanumeric (no symbols) and/or the alpha part of the password is case insensitive and/or the password must be between 6 and 12 characters, they rubbish the vulnerabilities in their security models that are demonstrated by competent security experts (look at how VISA and MasterCard treat the Cambridge University security research team), some of them even hold passwords in clear.


Tom
Post #1250814
Posted Saturday, February 11, 2012 9:19 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:18 PM
Points: 4,245, Visits: 3,324
Wow!

Tom, you have my standing invitation to the whisk[e]y bar in Bellevue, whenever you happen to be in this neck of the woods, but I will have to up that.

I am on systems that are on the Intranet and trusted, so I do not have to worry about these things. So reading your post, I found my blind spot, probably one of many.

Much thanks, again, and I am buying, even if your call is a 50 years old single malt.
Post #1250832
Posted Friday, January 11, 2013 2:18 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:16 AM
Points: 905, Visits: 553
mohammed moinudheen (2/9/2012)
No idea about this really. I guessed it and got it wrong


+1


--
Dineshbabu
Desire to learn new things..
Post #1405839
« Prev Topic | Next Topic »

Add to briefcase «««23456

Permissions Expand / Collapse