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.

This is a rather short question:

When using Microsoft SQL Server (version 2005 and newer), are there any security related reasons to prefer Windows Authentication over SQL Server Authentication?

Just to point it out, I'm interested in security related concerns, not in administrative or any other differences between the two.

Update: If any difference leads to (or is) a security concern, then I'm surely interested in.

share|improve this question
add comment

2 Answers

up vote 5 down vote accepted

An example, separation of duties

On a web server, the password for the service account is only known by one team. It isn't required to lie around in web.config or source control.

  • One team sets up the IIS App Pool (password is known here)
  • One team deploys code
  • One team manages the SQL Server Instance

If you want to pass an audit where PCI or SoX is involved, then you need to have this

share|improve this answer
    
Also for PHI in the US (healthcare). –  JNK Nov 29 '11 at 14:32
    
+1 Cut and dry. Additionally, kerberos delegation is only possible with windows authentication. I've seen enough linked servers set up to use the sa account that I appreciate the security added with kerberos delegation. –  brian Dec 2 '11 at 4:50
add comment

With Windows authentication the username and password are never transmitted to the SQL Server. Only the users Kerberos or NTLM token are passed.

With SQL authentication the actual username and password are passed, but they are not passed in clear text. The authentication process is encrypted via SSL with a self signed certificate to ensure that the username and password aren't passed in clear text. This does mean that a man in the middle attack would be possible as the cert isn't generated by a recognized certificate authority. Now if your SQL Server is configured with a certificate that it from a CA then that cert is used for the authentication SSL encryption instead of the self signed cert.

The connection string within the application should be encrypted no matter which authentication method is being used.

Now the nice thing about Windows auth is that the DBA doesn't ever need to know the password for the account. That's all handled by the application. Granted this isn't that big of a deal, I can use execute as to run commands as the application account all day if I so desire.

With a SQL Account the admin who is installing the software can easily enough be the one that sets the password, or changes the password so that it isn't known by the DBA.

Another nice thing about Windows accounts is that you know for sure that the password will meet the company security policies. SQL Passwords can have the policies disabled and the passwords changed then the policies reapplied which means that the password might not meet the required policies.

share|improve this answer
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.