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.