Even Microsoft discourages the use of SQL Server authentication mode, but our applications require it.
I've read that it's a best practice to not let users use the sa login directly, instead using Windows Authentication and allowing those accounts (or account groups) sysadmin privileges.
- Isn't that essentially the same thing? What are the advantages/disadvantages?
- How does the best practice increase the security of my SQL Server instances?
- Does this only apply to production instances, or to our internal development instances, too?
Best Answer
You have several different questions in here, so I'll knock 'em out individually:
"I've read that it's a best practice to not let users use the sa login directly, instead using Windows Authentication"
You're mixing two things here: the concept of SA, and the concept of SQL authentication and Windows authentication.
SQL authentication is a list of usernames and passwords that are stored in each and every SQL Server. The fact that it's stored in SQL is the first problem. If you need to change a login's password, you have to change it on every server (or maintain different passwords on different servers). With Windows authentication, you can centrally disable logins, change passwords, set policies, etc.
If you choose to use SQL authentication, then SA is just one SQL authentication login. It's the default administrator username, just like Administrator is in Windows authentication. It has local superpowers on that one instance, but not global superpowers across all instances.
"...and allowing those accounts (or account groups) sysadmin privileges."
Whatever authentication method you choose, ideally you want to follow the principle of least privilege: granting people the bare minimum rights they need to get their job done, and no more.
Don't think of them as just logins - they're people who can get you fired. If they drop the database or disable your backup jobs accidentally, they're not going to get fired, because by default, SQL doesn't track who did what. You're the one who will get fired because it's going to happen, and you're not going to be able to say which person did it.
"How does the best practice increase the security of my SQL Server instances?"
You want to do two things:
The first one is accomplished with the principle of least privilege: giving folks only the permissions they need, and nothing more.
The second one is accomplished by giving each person their own login, not allowing shared logins (like letting everyone use the same username/password), and ideally, auditing the logins. You probably won't do that last part right away, because it's kind of painful, but let's put the pieces in place first so you can add auditing later after somebody drops a database and your boss wants to know why.
I know what you're thinking: "But we're coding apps, and the app needs a login." Yes, give the application its own login, and the developers need to know that password, but that login should be so stripped of permissions that nobody in their right mind would want to use it. For example, it might need to be in the db_datareader and db_datawriter roles alone, nothing else. That way it can insert, update, delete, and select data, but not necessarily change schemas, add indexes, change stored procedures, etc.
"Does this only apply to production instances, or to our internal development instances, too?"
I think it applies just as much to development instances because I'm usually worried about people breaking things. People just love to break servers in development. And of course, when it's time to bundle up the list of changes to migrate to production, I need to know whether a particular index really is vital to the app or whether some bonehead just ran the Database Tuning Advisor and told it to apply all changes. Proper permissions helps lessen that pain.