Securing Your SQL Server Environment

Database security is a topic that affects all DBAs regardless of the size of your business or application. If you're reading this magazine, you need to know how to set up a secure database environment. So let's get started.

I’m not going to spend time on how a developer writes secure code or how to add and secure SQL Server Reporting Services (SSRS) or other ancillary features of your overall data storage solution. Instead, let's look at security items you should consider on the core relational database engine. I’ll approach security for SQL Server the way you should approach security in general—as a series of layers. First, I’ll show you how to secure your SQL Server environment when you set up your own database (as opposed to a hosted database) for an application. In future articles, we’ll look at authorization, authentication, and policy-based security settings that are applicable to any database.

There are certainly differences between the security features available in SQL Server 2008 and SQL Server 2005. This article will offer security recommendations that are valid regardless of version.

Creating a Secure Environment

The first element of SQL Server security you need to consider is how and where to deploy it. For example, a copy of SQL Server installed on the local developer's laptop probably isn't considered to be secure, regardless of whether the developer locks down access using best practices within the database. If someone can access the server's file system and make copies of the database files, then it’s just a matter of time before they crack the security you've applied within those files. As a result, you need to consider how to protect those files when setting up your server.

There’s certainly an aspect of physical security you should consider—a laptop carried into an airport and lost in security is a prime example of not having physical security. However, most elements of physical security for a server-based database are handled when you configure your server in a locked server room. The real surface area for a system attack is network-based, which is why it’s recommended that you place the database on a separate server when setting up a simple website. If your website is compromised, in theory, any file located on that server might be accessible. So if your database is running on the same server as the website, the database files could be compromised.

Ideally, your database server should be a dedicated server. Unlike a developer who will cram as many different roles and features into a single environment as possible, in a production environment, or even in a test environment, the goal is for each server to focus on a single role. For example, the domain controller (DC) shouldn’t double as your database server and Microsoft Exchange shouldn’t be hosted with SQL Server. For small-to-midsized businesses (SMBs), many of these applications and services might be hosted on a single physical machine, while in large organizations some of these individual server roles might be installed across multiple physical machines. The focus of this article isn’t on whether given your database needs you can successfully host it within a virtual environment, but rather that by dedicating a "machine" to the role of database server you can better secure your SQL Server environment.

Granting Service Account Permissions

The first step in creating a secure environment isn't securing just the settings used to connect to the database but access to SQL Server by the host OS. Securing access to your database involves two components: the accounts that are used by the server and the underlying file system that hosts those files. When you install SQL Server, you’ll be asked which accounts should be used to run the database engine, not including those associated with additional components such as SSAS and SSRS. These accounts determine the permissions associated with the running server.

During the installation process, you can define which services will run and which account or accounts will be used to define the permissions (authorization) provided to that service. You can choose from three system-defined accounts: Local System, Network Service, and Local Service. The Local System account has highly privileged access to the local system and doesn’t have access to resources not on the local system, thus it would be a poor choice if you need to reference data or services on the network. The Network Service account has access to network resources, but only carries the permissions of a typical user on the local system. The Local Service account is similar to the Network Service account in that it has permissions like those granted to a typical user on the local system. Unlike the Network Service account, however, this account doesn’t have permission to work with network resources and will pass a null account to external network resources.

So which of these accounts should you use? The correct answer is none. Unless you’re setting up a development environment, you should bypass all three of the potential system accounts and instead create a user account on the domain or local system. Note that this is true for the relational database engine and other subsystems such as SQL Server Analysis Services (SSAS) and SSRS. By creating one or more accounts, you can customize the exact permissions required and adjust those permissions based on need. Although you might initially create a domain account with permission to access the local machine, you could change permissions later if you needed that account to access files on another machine. If that access was needed only for a short period of time, you could grant and revoke those permissions, and since you would only have that account running for the purposes of SQL Server, you could be certain you weren’t going to impact another service. Which is why, of course, you’ll also want to create a separate account for each of the services that are part of your database installation.

Securing Your Database Files

After creating custom accounts and granting them permission to access the file system and folder necessary to host your database files, you need to protect those files. Part of that protection is derived by following the best practice to associate those files with a server that’s dedicated to database services. Two things to note when you’re doing so: First, there are several performance-related issues related to competition for server resources by different services/applications that also make dedicating your server to SQL Server a performance best practice. Second, the files might be located on a SAN that isn't physically part of the server. In either case, these files are at the core of what you’re trying to protect from unauthorized access. Someone copying these files can essentially copy your database and potentially open it on a different database server.

Network Security Considerations

There are several things you need to consider regarding the logical location of the server and database files on your network. For SMBs, your network configuration might just place the production database server on a network segment with your internal network. However, in larger organizations you'll probably have a unique network segment for your production database servers.

As Figure 1 shows, you can place your Internet-facing applications on one (or more) network segments, your internal company on a separate network segment, and your database server(s) on a different network segment.

Because your database isn’t truly part of your internal network configuration, you’ll want to decide if you’re going to use a common domain across your internal network and the database network. If not, then you’re probably moving toward a security model that relies on SQL Server authentication. Keep in mind that Figure 1 represents a logical diagram, so although you might choose to implement separate firewalls at every location, it’s also possible that the two logical firewalls surrounding the data servers are in fact a single physical device with different rules, depending on the communication source.

As you’ll also note in Figure 1, there are two connection points to the Internet. The first are the web servers, which allow for anonymous access. However, your internal network, which is the second connection point, contains your corporate users, all of whom expect and use network access, not to mention that they could take a laptop home and connect from an external location across a public network. In essence, the connection from corporate becomes a second attack vector for someone attempting to access your database. This scenario, combined with the fact that most security attacks originate with internal users, makes it easy to justify separating your corporate data from your unlimited network access by your internal users.

Once you've decided on your network structure you'll need to open a set of ports to access your data based on your data access strategy. For example, while your website typically works on ports 80 and 433 for http:// and https://, SQL Server typically uses port 1433 and 1434 for inbound connections. Thus the same tool that was used to exploit some unknown IIS vulnerability for your external firewall will hopefully be useless in getting through your next firewall, which doesn't support traffic to IIS. This type of port and protocol shift between your external web servers and database provides a layer of protection against someone accessing your database files directly.

Encrypting Data on the Disk

One last layer of defense for your database files is protecting them on the disk. In SQL Server 2005, it’s possible to define an encrypted column within your tables. Therefore, if you need to store credit card numbers or other information that might expose people to identity theft, you could add another layer of security to that information. This feature is good for protecting data but has a few disadvantages. For example, it affects performance and requires application changes to ensure the data was actually encrypted and decrypted.

SQL Server 2008 Enterprise Edition includes Transparent Data Encryption (TDE), which lets you specify that the entire database should be encrypted on the disk. TDE lets you take an existing database and tell SQL Server that the data on disk needs to be encrypted and no changes are required from the application. The database engine then handles the translation between the encrypted data on the disk and the decrypted data returned to the application. It’s possible to simulate this feature using the Encrypting Files System (EFS) or BitLocker Drive Encryption. Keep in mind that although BitLocker is probably the preferred solution for most SQL Server 2005 scenarios, it encrypts only the data “at rest.” Data isn’t encrypted when the server is up and running. The process of setting up database encryption is beyond the scope of this article. For more information about TDE, see "Database Encryption in SQL Server 2008 Enterprise Edition."

A Secure Database Environment

Although these practices provide an environment that promotes a secure database, they aren’t the whole answer. For that we need to look at the policies that will be enforced within the database server, the user accounts, and authorization of those accounts on the server. Security is a critical facet of database management, one that can be defined in layers. Those layers can take a logical form in the case of multiple network firewalls, database encryption, and account permissions. However, they also appear when we look at the permissions granted to users who access the database or the rules for how the database is defined when considering standard policies. These additional layers are the focus of the follow-up articles on this topic.