SQL Server Reference Guide

Security, along with performance, is one of the hottest topics of discussion on the SQL Server forums and on the speaking circuit. The difference between the two topics, however, is that most folks take what they learn about performance and apply it to their systems right away. This isn’t always true for security, however.

Why is that? Why don’t we always work on security the way we work on other priorities? There are several reasons I’ve seen — mostly involving not knowing where to start, and the amount of time it takes to find and fix security issues. In this tutorial, I’ll show you how to evaluate your systems systematically, and use as many automated tools as possible.

I’ll start with four simple rules you should follow for evaluating and securing your system. The further you go with these rules, the more secure your systems will be.

The four rules for securing your systems are:

1. Find the Attack Surfaces
2. Follow Best Practices
3. Use Automated Tools
4. Educate Yourself and Stay Current

Let’s dive in to how to follow these steps. If you complete each one and put it on your regular DBA system-check schedule, you won’t be surprised by a security issue in your environment.

Keep in mind that there is no “silver bullet” that will find and fix your security issues for you. If there was, Microsoft would just implement those for you automatically. The issue is that no one knows what level of security is best for each application. That’s a mix of your software package, the needs of the organization, and the level of security required. So the settings are important for you to set. So I won’t give you a full list of things to take care of or prescribe things you should set. Sorry about that — you really do need to understand these settings before you make them. But not to worry. Towards the end of the article I’ll show you some tools that will help. While you’re reading through this tutorial and the articles I’ll reference, you might want to make a checklist for yourself and your organization.

Find the “Attack Surfaces”

To start, you need to understand the security areas for SQL Server that someone can use to attack. I recommend that you start with the “outside-in” approach, although any systematic approach is fine, as long as it covers all these areas and you are consistent in implementing them.

Hardware

The hardware your system runs on is the first level of security you should consider. You need to ensure that only the administrators and other appropriate personnel can access your systems. Recently there was a report that thieves broke into a data center — they bypassed computers, rack hardware, and everything else — except for the hard drives. What do you think they were after?

Find out where your hardware lives, and who has access to it. That includes the servers, the network connections (including wifi) and the workstations. All of those represent an attack vector. Also ensure that you work with the rest of your IT team on who has access to them, what to do in case of a “lost laptop” (or smartphone) scenario, when those devices include data from your databases. You do know which databases are replication data, correct?

Platform

After you’ve secured access to the physical hardware of your SQL Servers, the next level is to secure the operating system they run on. You can find a list of best practices for that here. Make sure you apply the proper level of service packs, updates and hotfixes for your system. More on that in a moment.

I’m also including the SQL Server software in the Platform area. First, ensure that you carefully evaluate the installation choices when you’re setting up the system. Don’t just take all the defaults, and install everything — that opens up far more attack surfaces than you should. Install only what you need. If you’re using features such as SQL Server 2008’s FILESTREAM, it will request a share location to store the “off database” binary data. Make sure that you include that location in your share evaluation on your server to ensure no one has access that shouldn’t. In other words, understand the file locations for SQL Server and check the Access Control Level (ACL’s) for each one. Only administrators and the system accounts that run SQL Server should have access to them, including places such as data file locations and backup folders.

After you’ve completed that check then check the settings for the network and services using the Configuration Manager. Do not use the Services Control Panel applet, since the Configuration Manager tool does more than just start and stop services – it controls service rights, file shares and more, all automatically. I’ve documented that tool here.

From there, open SQL Server Management Studio (SSMS) and check the Server-level configuration options. Again, the levels and so on depend on your situation and security requirements, so check out the options and their security implications here. You should always bias towards choosing the lowest amount of entry possible, and relax that only as needed.

Databases and Objects

Each database in SQL Server also has Properties and settings, just like the Instance of SQL Server. For a discussion of those settings, check out this article. Again, select the “least privilege” approach, opening up only what you need.

Once you’ve secured the database to the desired level, you can move on to the tables, users, stored procedures and so on. There’s quite a list of things to understand at this level, so to figure out SQL Server Database object and statement level permissions, start with my articles here. In many cases software will use a “proxy account,” so called because only one account accesses the database, and the software handles what each user can see or do. You’ll need to understand the software that accesses SQL Server to see where the security needs to be controlled.

Code

The next place to look is to evaluate the code that runs on your system. Writing secure code involves, once again, that concept of “least privilege” I keep bringing up. If it’s a vendor package, ask them about the security features, requirements, and tweaks you are allowed to explore. If it’s code your team writes the code, make sure you check out the book called “Writing secure Code” — it’s essential reading for any developer.

One of the most common coding errors for SQL Server is writing the code such that an attacker could “slip in” more code (or less) than you intended to run. This is called “SQL Injection,” and you can read what it is, and how to protect against it here.

Data at Rest and in Transit

The last section of SQL Server that you need to protect is the data itself. This includes the physical, platform, code and other security, but also includes things like encryption, securing and encrypting your backups, and even network encryption. The links show you more about each of these areas.

Follow Best Practices

As you can see, there are a lot of things here to consider — but you don’t have to re-invent the wheel. Many organizations, Microsoft included, have created a series of “best practices” that you can follow for common Patterns and Practices around the configuration of your system. While you should review these and possibly even implement some of them, you should understand what they mean. Don’t just enable or disable a setting because you see it listed — understand why it’s a best practice.

To understand them, you need to know where they are. The primary source I use for best practices are the Microsoft SQL Server sites on that topic and the SQL Server Customer Advisory Team, or SQLCAT. Here are some links that will help:

• SQL Server Best Practices
• Security Overview for Database Administrators
• SQLCAT site for Security
• Microsoft General Security website

After you understand the best practices, all that’s left is to implement them. This is the part that takes the most planning and discipline. To do that, I start with an automated process, leveraging as many tools as I can.

Use Automated Tools

I’ve given you quite a few articles to read and understand. You may not have time to do that, which brings us around to the beginning of this article. But since you need a secure system, and you don’t always have the time to check the settings, why not have software do the job for you? There are packages, free and pay-for, that will help you do that.

Another important thing to keep in mind is that while these tools can help you with Platform and Database settings, they can’t take care of your physical security, password policies, or poorly-written code. That’s something you’re still going to have to take a look at yourself.

Microsoft Baseline Security Analyzer

A great place to start for an “outside-in” approach is the Microsoft Baseline Security Analyzer. This tool will examine one or more systems to make sure they comply with security best-practices with an operating system focus. It can also be scripted, which is a great benefit. You can find it here: http://technet.microsoft.com/en-us/security/cc184923.aspx

Best Practices Analyzer

By far one of the best tools to catch the most errors in your system configuration, including some security issues, is the SQL Server Best Practices Analyzer. I’ve written a full article on it here, and you can find it on the Microsoft website.

Policy Based Management

In SQL Server 2008, Policy Based Management (PBM) was created to take the place of the Best Practices Analyzer. In fact, Microsoft still has both tools, but there are advantages to using PBM, since you can create your own rules easily and it’s extensible. I’ve written a full set of articles here on PBM.

PowerShell

PowerShell is a scripting environment that works with the .NET environment — which means it can “talk” natively to Windows, SQL Server, and other software that runs on the Windows environment. I’ve written an entire series of articles on PowerShell starting here, and you can use it to not only check and change the settings you want, but it will even allow you to script things like the Policies inside Policy Based Management.

Third-party

A few other companies provide tools that will help you perform a security audit. A quick web search on “SQL Server Security Audit” will bring those up. Make sure when you evaluate those tools that they allow you to customize them for your environment, that they cover your editions and versions of SQL Server, and that they have a good track record.

Educate Yourself and Stay Current

No, you don’t want to just apply things blindly — make sure you understand what is in each update and apply it only when you need to. You can find a list of security bulletins to subscribe to here.

It’s important to stay on top of your security audits. Make them into a checklist, and roll those steps and these tools into a periodic review. You should check your security at least once a month, or even more often if you have a very dynamic environment.

InformIT Articles and Sample Chapters

There’s a good reference on creating a security policy in the article Developing a Security Policy.

Books and eBooks

It is not a simple matter to develop secure software. The book The Art of Software Security Testing: Identifying Software Security Flaws shows you how.

Online Resources

The security documentation within SQL Server Books Online is well ordered, and is the most authoritative place for information. You can read it starting here.