4

(For the purpose of this question, assume that I only need to support SQL Server 2008 and higher. Or to future-proof this question, let's say recent, current, and near-future versions.)

The documentation for the CREATE LOGIN statement says (emphasis mine):

HASHED
Applies to SQL Server logins only. Specifies that the password entered after the PASSWORD argument is already hashed. If this option is not selected, the string entered as password is hashed before it is stored in the database. This option should only be used for migrating databases from one server to another. Do not use the HASHED option to create new logins. The HASHED option cannot be used with hashes created by SQL Server 7 or earlier.

There's no explanation as to why this option shouldn't be used to create new logins, and if there is a reason, it's not obvious (to me, at least).

The documentation references KB918992, which while a little unclear, describes steps that indicate legacy (2000-2008 R2) password hashes are upgraded automagically. So I can take a password hash generated on 2000-2008 R2 and CREATE LOGIN ... WITH PASSWORD ... HASHED on 2012(+?) and have the hash converted to 2012 format when the principal logs in for the first time. I tested this does indeed work as described.

The password hash itself appears to have a 6-byte signature/metadata header, consisting of what I think is a 2-byte tag/version and a 4-byte salt. When a hash is upgraded, the tag/version is incremented while the salt remains the same.

The question is then: if Microsoft has already baked in this kind of future compatibility (which I assume will need to be maintained going forward), is there any reason why this method shouldn't be used for creating new logins?

Improve this question
2
  • The only reasons I can think of are (a) CYA for people who will save those scripts and try to run them on old versions, or (b) CYA for changes they may make in the future that break them.
    – Aaron Bertrand
    Jan 16, 2014 at 18:04
  • My guess is that it's a security thing. Don't share passwords between different logins since it makes them all less secure.
    – Kenneth Fisher
    Jan 16, 2014 at 18:23

2 Answers 2

Reset to default
4

The hashing method is presumably an implementation detail which may or may not change in future releases (as it has at least once already). They're telling you not to do it in order to absolve themselves of breaking your scripts/automation if you try to run them on newer or older releases. It's partially supported purely to allow for migrating logins.

At some point in the process, you would have to use the clear-text password to generate the hashed password, so I'm not sure you'd really gain much by supplying pre-hashed passwords.

Improve this answer
4
  • Sometimes we need to run deployment scripts with the client watching what we do, and they shouldn't have access to the password. I suppose that falls into the narrow set of cases Microsoft says it's okay. That said, though, you're right -- the password does have to be clear text at some point, even if on the client side.
    – Jon Seigel
    Jan 16, 2014 at 18:37
  • Yeah, that seems like a fair enough use. I can't think of a nice way around it without writing a deployment tool, or doing silly encryption/decryption tricks within the script.
    – db2
    Jan 16, 2014 at 18:41
  • That's the other use case. We don't want to put the password in the deployment tool (currently VB6 -- yeah... -- we're converting to a .NET app soon). But as I said, the client apps still have it embedded in clear text, so it doesn't make that much of a difference I suppose.
    – Jon Seigel
    Jan 16, 2014 at 18:47
  • Why can't you use Trusted authentication instead? You are correct, your password can be trivially found in the client app, or extracted in flight with a packet sniffer unless the client has SQL Server connection encryption turned on and operating (SELECT encrypt_option, * FROM sys.dm_exec_connections (and the client doesn't disable it), not even counting the clients extracting the password hash and running oclHashcat on however many desktop machines or even servers they feel like during off hours.
    – Anti-weakpasswords
    Mar 22, 2014 at 3:19
4

For the record, the SQL Server 2012 hash format is: 0x200<4 byte salt><hash result>

where the hash result is essentially:

HASHBYTES('SHA2_512', CONVERT(VARBINARY,N'[password]') + CAST(0x[salt] AS BINARY(4)))

SQL Server 2005 to 2008 R2 are identical except they use SHA-1 instead of SHA-512.

Some reasons NOT to use the previously hashed password is that you're sharing salts (leaving an attacker who gets lots of your passwords with less work to do), and you're sharing passwords (more bang per cracked password for the attacker, since that'll go right back into their cracking dictionary), and if an attacker cracks the weaker version, they automatically have the password for the stronger version as well (once they re-run their software with the previously cracked passwords.

Ideally, use different passwords entirely; however, if nothing else, at least make sure it's a different salt in each place so the attacker actually has to bother to perform the subsequent cracking run with previously cracked passwords, rather than simple see that the hash is identical, so if you crack it once, you're done.

Improve this answer
1
  • Thanks for this. To answer your question (I assume it was directed at me -- use @username to ping someone), there is no management buy-in to change the authentication method at this point.
    – Jon Seigel
    Mar 22, 2014 at 16:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.