SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Why is a Windows authenticated login more secure than a SQL authenticated one?

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

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...