Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

(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?

share|improve this question
    
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 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 at 18:23
add comment

2 Answers

up vote 4 down vote accepted

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.

share|improve this answer
    
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 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 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 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 at 3:19
add comment

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.

share|improve this answer
    
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 at 16:25
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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