As I mentioned in the introductory post, during the Introduction to SQL Server Security session for Pragmatic Work’s Training on the T’s, I received a large number of questions that there wasn’t time to answer. Instead of just a re-cap of all of the questions, instead I’ve opted to put together a post per topic. Hopefully, this will help, not just those that attended the session, but also anyone searching for the same questions later on.
Security question
The next question in the list is:
I work in a bank and federal inspectors are always looking at how secure are my databases. How would you prioritize security for the SQL Server?
Prioritizing security can be a sticky wicket. It really depends on what your current environment consists of and where the dangers lie. Also, what was the last security violation that occurred that you now need to deal with. Focus your energies on what will provide the greatest improvement with the least amount of effort. Also, if you can get something mostly secure, make sure you re-evaluate the security measure against other threats to see if your efforts should be re-directed.
The question, though, is what and how would I prioritize? Let’s run through a quick brain storm. I’ll miss some stuff, but this should get you going on the path and direction that you need.
Security Priority List
When working on securing a SQL Server security can be broken down into four basic buckets. These are physical, network, application, and regulatory security. For each of these buckets, there are a number of considerations. The full list would take pages to reply to and might make a good book.
Physical Security
When considering physical security you are focusing on the ability for someone to get to your servers. Where is the SQL Server located? Is it in a data center or in a hall closet? Who has access to the server room? If someone can walk in and grab the disks or backups off a shelf, does it matter what other precautions are being taken. Limit the access to the data center. You don’t necessarily need armed guards at the data center, but a lock on the door would be a good place to start.
Network Security
With network security, you are making sure that access through the network and communication with the server are protected. Is the SQL Server behind the firewall? Is data transmitted to and from the SQL Server encrypted? Again, if anyone in the world can access the server from any network connection, then chances are your data will eventually be accessed regardless of physical or application security.
Application Security
Application security refers to both the SQL Server instance configuration and the applications that access the SQL Server instance. Don’t provide admin access to applications when they don’t need it. Also, make certain that network administrators don’t have more access to the SQL Server than they need. Leverage the principal of least privilege and only give people and service accounts what they need.
Regulatory Security
The last area is regulatory security. I add this as an additional area because the regulatory requirements are often more about appeasing a law than actually securing an environment. The intent of the regulations are well meaning, basically someone somewhere really screwed up to the extent that law had to be written to make sure it didn’t happen again. But often the intent and implementation are lost on each other.
The best recommendation is that when you are securing your SQL Server environment in each of the preceeding sections be certain to document everything that you do. Set up audits that capture when security issues can be raised. Monitor your logins and the data in sensitive tables. When working with regulators, the best strategy is to have a well documented defense.
Summary
I’m not exactly sure I answered this question directly with an answer that wasn’t more or less, it depends. Unfortunately, it really does. As DBAs, we need to focus on the areas above, but without knowing the specific applications many suggestions may or may not fit the environment. The key is to identify what will potentially have the most negative impact and fix it to the best of your abilities and learn when to move on to other tasks that are more effective.
