The standard best practice answer when it comes to connecting to SQL Server is to use Windows authentication. However, SQL Server-based authentication exists, and there are some times when it's a best practice (or only practice) to use it. Wait a second... what do you mean by having two best practices? Well, as with most things in security and architecture, what is the best practice in a given situation depends on the situation. So let's look at two common reasons why you would use SQL Server authentication over Windows authentication.

Third Party Products

This one is a no-brainer. You've purchased a third party application and it only uses SQL Server-based authentication. If that third party application is important to your organization, you're now stuck between a rock and a hard place, right? After all, the recommendation is to use Windows authentication! Well, not really. The fact of the matter is you can't do it. The application doesn't support it. So you have to make do with what the application supports. Either that, or you've got to eject the application from your environment. That's not happening? Then SQL Server authentication it is. And that reminds me of a baseball analogy.

When I played Little League I was blessed with some really good coaches. They didn't just teach the skills, they taught how the game worked. Simple things which seem obvious to me as an adult I realize wasn't so obvious as a 10, 11, or 12 year-old kid. Like one time my team was down by more than 4 runs. The bases were loaded and I was going up to hit. Before I left the batting circle, my coach motioned to me so I could look at him. He said, "Make contact, get a hit. Don't overswing and end up striking out." I think I remember saying, "But if I get a homerun, we score 4 runs!" To which he said, "Even if you hit a homerun, we're still losing the game. It's more important to get a base hit. So keep your eye on the ball and get a good swing. Don't overswing!" And it clicked what he was saying. So I went up, put the ball in play and got a base hit, drove in a couple of runs, and didn't get an out. Unfortunately, we lost that game (we lost every game that season).

The same idea is true here. If it can't be done, it can't be done. The application doesn't support Windows authentication and you can't make it do so. You can request a future version support it, but until the vendor writes that into the application (if the vendor does so), you use SQL Server authentication. If it represents an exception to your environment, save the installation/configuration documentation. Do a short exception write-up about where in the documentation it says you have to set it up to use SQL Server authentication. If need be, get acknowledgment from the appropriate powers that be and save that evidence with those documents. And then move on.

Applications in Untrusted Domains or in the DMZ

Using Windows authentication assumes you have a connection coming from a trusted computer. The simplest example is when everything is in the same domain. There are cases where a computer may be coming from an untrusted domain or the computer isn't in a domain at all. In these cases, you can't use Windows authentication. The most common example is a computer in the DMZ, the area firewalled off from the Internet, but firewalled off from your internal network. Computers in the DMZ are usually accessible from the Internet.

For instance, a web server that is your company's presence on the Internet is likely in a DMZ. By rule, these computers are supposed to be off any internal domain. The reason is simple: if I compromise this DMZ computer from the Internet and it's on an internal domain, I can launch a direct attack against at least one of your internal domain controllers. And that's not good. By being on the domain it's got to be able to talk to domain controllers. And the way it'll talk to a domain controller is the same pathway I would use to attack that domain controller. Therefore, these computers are usually put in workgroups. Since they are in a workgroup, they can't use Windows authentication to connect to SQL Server. They must use SQL Server authentication.

What About Shared Services and Resource Pooling?

It's not unusual to have a case where multiple systems hit a particular resource and that resource makes a connection to the database server. A good example is a web service which multiple applications use. The web service may authenticate the individual incoming connections, but for the purposes of efficiency you want to make a connection back to the database server using a single account. This means the connection string can be the same every time and this means you can take advantage of resource pooling. A perfect candidate for SQL Server authentication, right? Well, not necessarily. If the web service runs on servers where you can establish that trusted connection to SQL Server, a better option is to use a domain user account, only make sure it doesn't belong to a particular user. A lot of folks call this a service account. A savvy domain administrator can even lock down what servers that account can run against, meaning if Joe Smith somehow got the username/password and tried to run it from his workstation, it would fail. In this case, Windows authentication is the best option. You still get resource pooling. You're not relying on a SQL Server-based login. And you can apply additional security options to the account to help make it harder to be abused.