SQL Server 2008 Mixed Mixed Mode Security?

Question

We are setting up a SQL Server 2008 Standard edition on a Web Server for the first time. We normally have our SQL Server on our local Intranet and only use windows authentication.

What is the best practise authentication mode for a web server? Can I use Mixed mode without any security worries?

Thanks!

Answer 1

Yes, as long as you don't use "sa" for the app and you set the "sa" password here very, very strong. (Or rename "sa" and create a dummy "sa" account).

Most shops use mixed mode because web servers, firewalls, cross-domain issues, 3rd party apps etc all conspire to prevent windows authentication only

Answer 2

Although you will be just fine using Mixed Mode Authentication, if all of your servers are within the same domain then you should strongly consider using Windows Authentication only.

The recommended best practice is to use Windows Authentication, unless specifically necessary to do otherwise.

See SQL Server Books Online and in particular the security note "When possible, use Windows Authentication":

http://msdn.microsoft.com/en-us/library/ms144284.aspx