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.

Filed under: Microsoft SQL Server, Security, SQLServerPedia Syndication