In this continuing look at post install SQL configuration I am going to have a quick look at some common sense security configurations that I would recommend whenever you put in a new server.
Security is a big concern in organizations these days. Cyber threats come in all shapes and sizes, and companies are more readily aware of the responsibility they have to protect their data and the possible breaches that can occur and the consequences these breaches can have on their public image (Sony, Linked In, I’m looking at you J ). So it makes sense for us, as DBAs, to make sure that we are not responsible for gaping holes in an organizations defense, by making sure security is as tight as it can be on our servers from day 1.
If this was a perfect world, I would be writing this from my private Beach house in Jamaica, watching Jennifer Aniston bring me a glass of Southern Comfort, and every SQL Server would be using Windows Authentication. But sadly it isn’t, and I am in North Carolina and almost every server I work on is mixed authentication.
It would be great if we could dictate to business owners how their applications should work and Legacy applications were easily changeable, but, for the most part we can’t. So, unless you are very lucky and can get in at the design level of a new application server and get developers to use best practice then you may be forced into using Mixed Mode on a lot of your servers. This doesn’t mean you should give up all hope of making your server secure.
Disable the sysadmin user
SA isn’t needed, if security is configured correctly, there should always be an account that can be used that has the necessary rights to make your required configuration changes to your server, so I would recommend disabling this account at day 1. You are prompted at install to set an SA password, and I usually use KeePass or some other tool to generate a nonsensical 20+ character password and then immediately forget it once I have moved on in the install. Current SQL installers allow you to set other system administrators during the install phase, so if you have set yourself as one of these users, the first thing you should do is disable the sa account when you connect to your new instance.
In SQL 2000 and 2005 a new SQL install automatically included the security group BUILTIN\Administrators by default. This was removed in SQL 2008, so if you are up to date and running latest version s of SQL, “move along, nothing to see here.”
This group was automatically added as a sysadmin to the server, so anyone who had local machine administrator rights, also had SQL administrator rights. In some cases this might be fine, but as a security best practice it isn’t great. Administrator rights should be added explicitly not inherited implicitly. By removing this group you are making sure you deal with the permissions directly and therefore know who can do what in your new server.
A word of warning! Make sure that you have granted yourself admin rights before removing this account. Getting back into the server without admin rights is an annoyance (and yes, I have been there J)
Domain SQL group
When I started my current post, I inherited 100+ servers ranging from tiny 1 app systems to multi-Terabyte ERP clusters, and there was not a single standard security group configured amongst them. I spent almost a month trying to get access to the systems to see what needed to be done, and in some cases was still waiting beyond that first month to get my access. To say this was frustrating would be an understatement. Every single DBA that had come before me had given permissions using their personal accounts, making it a nightmare for the next admin along to pick up where they left off. It also left a plethora of old accounts sitting around, sometimes for people who had left over 4 years ago.
One of the first things I did was to check whether there was a domain group (DOMAIN\SQL-Admins,DOMAIN\ DBA etc.) that I was a member of, if there wasn’t one, I quickly got it created. As I finally got access to each server, I made sure that this group was both a local computer admin and a sysadmin on SQL, so that the next guy to come along didn’t have to go through the same hassle I did, just to do their job. Simply adding them to the Domain Group would give them all the permissions they needed.
So, during the install, or after, make sure you add the DBA domain group to your Server Administrators; it will make life much easier down the road.
These few simple security changes will help you manage your environment more efficiently and hopefully keep those pesky Audit and SOX guys off your back for a while.
In the next installment I will be going on to cover configuring your SQL agent after install.