Why is a Windows authenticated login more secure than a SQL authenticated one?
12April 26, 2017 by Kenneth Fisher
I had this question come up at work the other day and while I knew it was true I wasn’t as sure about why as I’d like. The person I was working with wanted a real answer, not just Because I say so, so off to the internet I went.
What I knew before I started:
- SQL doesn’t actually encrypt its passwords. It uses a password hash which isn’t as secure as true encryption.
- Using windows authentication allows for an easier separation of duties. A security team can handle the Active Directory users and passwords while all the SQL Server admin has to do is grant the existing ID necessary permissions.
A quick search on the internet took me here: Choosing an Authentication Mode. And if you go down to the section Connecting Through Windows Authentication it points out a few important things and then even farther down the section Disadvantages of SQL Server Authentication has a bit more. Then I found a couple of good forum questions here and here. In summary (and only discussing actual security features):
- Windows authentication is handled by the operating system and passes a token to SQL for authentication. No password is sent across the network.
- Windows authentication can use Kerberos security protocol if set up correctly while SQL authentication can’t.
- Windows authentication can handle more complex password policies and in SQL Authentication the DBA can actually turn off the password policies.
As always feel free to add to this subject in the comments. I’ve learned an amazing amount over the years by people commenting on my posts.
SQL Studies 
The conclusions you presented are good (based on the research you made). I just wanted to clarify that one of your pre-research statements is not exactly correct:
“SQL doesn’t actually encrypt its passwords. It uses a password hash which isn’t as secure as true encryption.”
The part with “… a password hash which isn’t as secure as true encryption” is just about as true as saying that apples are not as good as oranges.
While I agree they are completely different, in this case the purpose of both would be to obscure the password. Which the hash does, just not as well as encryption would.
I’m curious to know why you say that hashing does not do a good job for obscuring the password. It can easily be argued that when used according to best practices encryption is less secure because by definition with encryption there is a key that allows one to actually recover the plaintext data … and where there is a key, there is a chance for the key to get lost and such.
Sure … there are bad hashing algorithms out there (MD5) that make it easy to brute force passwords with rainbow tables and such but using such algorithms is not following the best practices for hashing data.
As a matter of fact if you look at the OWASP security guide for password storage in online systems you’ll see that they only recommend hashing the password – the word ‘encrypt’ does not even appear on the page:
https://www.owasp.org/index.php/Password_Storage_Cheat_Sheet
I am going to have to say you appear to know more about the subject than I do 🙂 My understanding is that a HASH is easier to crack but that’s just based on what reading I’ve done and I could certainly have misunderstood.
Yeah, pretty sure windows domains also store a hash of the password instead of the real thing. You can encrypt them but MS recommends against it.
Missing a key point here: Brute force attack.
SQL will accept –as per design- and try to authenticate -depending on the server- thousands or more new connections request by second. The SQL account will not be lock even for invalid credentials. But…
The AD will lock the account after a number of invalid attempts when using Windows Auth.
Excellent point! Thanks!
SQL Server will lock the account after the number of retries defined in the Local Security Policy, Account Lockout Policy, which by default is 0, I give you that, but it’s possible and configurable.
Certainly some element of this is wrapped up in the old versions of sql where the SQL login password was passed in plain text across the network, easily readable by any half decent sniffer. If you haven’t seen that and are running any old SQL servers, I recommend you give it a go.
Another element to it is security by complexity…. it seems that almost any half qualified developer can write code to use a SQL Login, but far too many struggle to use a windows one!
General account management and usage practices are generally better at an AD level, people protect their desktop password more than just a ‘site’ password, and they usually tie back to a user better.
For server communications we also have a variety of limited access service accounts which can be access controlled at many different levels, and even the group managed service accounts, all managed centrally so the dba doesn’t have to worry much about it, unlike the SQL login where we need to manage the passwords (and probably keep a ‘store’ of them – which again punches a big old hole in security), keep them synced between servers (or not) and match up SiDs all over the place if we want to move a database.
Using SQL logins you risk ending up in a position of having an auto saved SQL script to create logins lying around somewhere with a plain text password in it…. or even someone storing such a thing under source control!
[…] Not to mention that SQL Authentication is just less secure to begin with. […]
[…] Why is a Windows authenticated login more secure than a … […]
SQL Passwords are in several scenarios present in plain text in the memory, for example see here
https://www.codykonior.com/2018/11/03/smo-leaks-sqlcredential-passwords-in-clear-text/
or here
https://www.tarlogic.com/blog/linked-servers-adsi-passwords/
And in general, when you store a password at multiple places, multiple places are attack points to be secured…you can just get rid off all this ods by just using integrated security