10 Steps to SQL Server 2005 Security

Microsoft's SQL Server 2005 is an enterprise-class database server, suitable for the toughest workloads and boasting some impressive security features. Yet enterprises might be placing themselves at risk if they simply deploy SQL Server and neglect to take advantage of the security features.

In general, Microsoft recommends a defense-in-depth approach to securing infrastructure components such as database servers. This approach focuses on perimeter, network, host, application, data, and physical defenses. Here are 10 steps to securing SQL Server 2005 that you should take before, during, and after installation, based on the defense-in-depth approach.

1. Preparing for SQL Server 2005 Installation: Remove Unnecessary Services

Properly preparing for an installation of SQL Server 2005 is one of the most important ways you can protect your databases and the data contained within them.

The first step is to carefully prepare the server on which you'll install SQL Server 2005. In particular, the server should be dedicated to running SQL Server 2005. It should not also be used to run other services such as Microsoft IIS, provide general Web-server functionality, serve applications, or be configured as a domain controller (DC). It's important that a server dedicated to SQL Server 2005 not run other services because if those other services become compromised, perhaps due to poor configuration, an attacker might be able to gain system-level access to the host OS and then to SQL Server 2005 and the databases it manages.

All unnecessary services and applications should be removed. For example, if you're repurposing an existing server that was a DHCP or DNS server, you shouldn't just disable those services, you should remove them by using the Optional Components Wizard that's part of Add/Remove Programs in Control Panel. Likewise, remove all third-party applications that aren't required, such as WinZip and Microsoft Office applications. You should also disable file and printer sharing. If you don't need them, I recommend that you also disable the administrative shares (e.g., C$, D$, ADMIN$).

Note that depending on what features of SQL Server 2005 you wish to install (e.g., Reporting Services), you might need to install IIS and .NET Framework 2.0. If this is the case, you should limit the use of these additional services and features to SQL Server 2005 only, and you should configure them as securely as possible by using the appropriate best practices for each. Security best practices and guidance for Microsoft technologies can be found at http://www.microsoft.com/technet/security/guidance/default.mspx. You might also want to check http://msdn.microsoft.com/security.

2. Apply All Service Packs and Security Updates to the Host OS

The second step in preparing for installation of SQL Server 2005 also concentrates on the host and is to ensure that all the latest service packs and software updates are applied to the OS on the server on which you'll install SQL Server 2005. This step is crucial to securing your SQL Server 2005 installation. Just as attackers can gain access to your system through services and applications running on it, they can get access to the host through vulnerabilities in the OS itself.

Visit http://update.microsoft.com to ensure that you have the latest service packs and updates, or use the Windows Update or Microsoft Update tool on the Start menu if you don't have an enterprise software update distribution mechanism such as Microsoft Systems Management Server (SMS).

3. Limit Access to the Host

The third step is a combination of focus on the host, perimeter, and physical environment and is to restrict access to the server. An attacker has several ways to gain access to a server, including interactively, over the network, and physically when the OS isn't running.

You limit interactive access to the host by ensuring that the local SAM database contains no unnecessary accounts and that only permitted domain accounts can be used to log on to the system. You can examine and remove accounts in the local SAM database by using the Microsoft Management Console (MMC) Computer Management snap-in (compmgmt.msc).

To prevent domain accounts from logging on interactively to the server and accessing a desktop, you can use Group Policy or the MMC Local Security Settings snap-in (secpol.msc) to set User Rights Assignment. If using Group Policy, expand Computer Configuration, Windows Settings, Security Settings, Local Policies, and User Rights Assignment. If using the Local Security Settings snap-in, expand Local Policies, User Rights Assignment.

Use the Allow log on locally and Deny logon locally settings to configure who is permitted to log on at the console. If you permit access to your server through Terminal Services, even if only for remote administration, you should also use the Allow log on through Terminal Services and Deny log on through Terminal Services settings to configure who can log on interactively.

You'll need to pay specific attention to members of the Domain Admins group—those users who have administrative access to all systems (DCs, workstations, and servers) in a domain. In many cases, Domain Admins members will also be database administrators, but in some environments, they won't. You might want to restrict access to database servers for members of Domain Admins. You can do this most simply by removing Domain Admins from the local Administrators group and adding the account names of trusted administrators to this group.

To prevent remote attackers from accessing the server over the network, you should place the server in a secured network segment and use at least one firewall (including the host firewall that comes with Windows Server 2003 Service Pack 1—SP1) or routing rules to deny access to users who don't need to access the server. For users who are permitted access to SQL Server 2005, you should deny access to any services other than SQL Server 2005 that the users don't need to access. Although you hardened the host in step 1, Windows will always have some services running which listen to the network for inbound traffic.

Last in this step, ensure that the server is physically secured. If attackers can gain physical access to the server, they can boot an alternative OS and reset the local Administrator password to log on or they can gain direct access to files on the disks, including database files. Note that if you're running SQL Server 2005 on a laptop, you might never be able to completely address the risk of physical access to the host. In such cases, step 10, below, can help.

4. Create Accounts for SQL Server to Run Under

The last step in preparing for a secure installation of SQL Server 2005 is to create accounts under which SQL Server 2005 services will run. The number of accounts you'll need depends on two factors: the number of SQL Server 2005 features you intend to install and the number of SQL Server 2005 installations in your environment.

Although you can use one account for all SQL Server 2005 services, I recommend that you create an account for each. You'll need at least three accounts: one for the SQL Server database engine, one for the SQL Server Agent, and one for the SQL Server Browser. You'll also need service accounts for Analysis Services and Reporting Services, if you intend to install these components.

I also recommend that you use unique service accounts for each installation of SQL Server 2005 in your environment—that is, create a unique account for each service on each installation. (An exception would be using one database engine service account for a cluster of servers or for servers that replicate data between themselves.)

If you're installing SQL Server 2005 in an environment in which the database engine will never need access to other servers and services across a network, you can use local accounts instead of domain accounts. Regardless of whether you use a domain account or a local account, the account shouldn't have privileges beyond those of an ordinary user—that is, it should be a member of Domain Users or Users only. Make sure that the passwords on the accounts you create are strong, and set up a schedule to change them on a regular basis.

5. Install SQL Server 2005

Once the server has been prepared, you can begin installing SQL Server 2005. Step 5 in securing SQL Server 2005, the careful selection of installation options, addresses the application aspect of the defense-indepth strategy.

The first selection you need to make is which components to install. You should not install components of SQL Server 2005 that you don't need. (Remember, the goal is to minimize the attack surface.) I recommend that when prompted for Components to Install in the Setup wizard, you click Advanced to select not only top-level features but also the individual features of each top-level feature. For example, the Advanced option lets you decide whether to install replication or full-text search features along with the default Database Services. If you're installing a single instance of SQL Server 2005 that won't replicate data to other database servers and if you don't intend to use fast text searching, you can simply omit these features from the installation.

Enter carefully the credentials of each account you created in step 4 for each of the services when prompted. When asked to select an authentication mode, choose Windows Authentication Mode wherever possible. In some situations, you'll have to use Mixed Mode, which permits both Windows Authentication and SQL Server Authentication. If you select Mixed Mode, you must enter a password for the SQL Server systems administrator (sa) account. As with the service accounts you created in step 4, you should ensure that the password is strong and set up a schedule to change it on a regular basis.

6. After SQL Server Installation: Apply SQL Server 2005 Service Packs and Updates

The first post-installation step, which also addresses the application aspect of defense in depth, is the application of all SQL Server 2005 service packs and software updates. At the time of this writing, Microsoft has released a Community Technology Preview (CTP) of SQL Server 2005 SP2.

7. Run the SQL Server Surface Area Configuration Tool

The next step, another application-level one, is to run the SQL Server Surface Area Configuration tool. Although you can launch the tool immediately after you install SQL Server 2005, I recommend that you run it after applying service packs and updates. I also recommend that you run it after you make changes to the configuration of SQL Server 2005 or add or remove components.

You can find the SQL Server Surface Area Configuration tool in the Configuration Tools submenu under Microsoft SQL Server 2005 on the Start menu. When you launch the tool, it's pointed at localhost, but you can also use the tool to configure remote installations of SQL Server 2005. In the tool, you can choose either Surface Area Configuration for Services and Connections or Surface Area Configuration for Features. The former allows you to view the installed SQL Server 2005 services and their configuration either by instance or by component (see Figure 1). The latter allows you to view specific features that have security implications and enable or disable each (see Figure 2). The SQL Server Surface Area Configuration tool is the preferred means to make security-related adjustments to your SQL Server 2005 configuration.

8. Encrypt Traffic Between the Database Server and Database Clients

To address the network aspect of the defense-in-depth model, you must secure communications between the database server and applications. By default, communications between a database client and the database server are sent in clear text, and anyone with a network monitor can eavesdrop and gain access to potentially sensitive information. SQL Server 2005 supports the use of Secure Sockets Layer (SSL) to both encrypt traffic between the client and the server and guarantee the identity of the server, which helps mitigate the risk of man-in-the-middle attacks.

To secure communications with SSL, you must obtain a certificate from an enterprise public key infrastructure (PKI) built with Microsoft's Certificate Services or from a third-party vendor such as VeriSign. The SSL certificate should contain the host name or Fully Qualified Domain Name (FQDN) of the database server or cluster as appropriate and should be in the local computer certificate store if SQL Server 2005 is running in the LocalSystem context; otherwise, the certificate should be in the certificate store for the account under which the SQL Server database service runs.

Once the certificate is installed, launch SQL Server Configuration Manager, expand the SQL Server 2005 Network Configuration node, right-click the Protocols subnode, and select Properties. On the Certificate tab, select the correct certificate from the Certificate drop-down list (see Figure 3). On the Flags tab, under General, select Force Encryption and change the value to Yes by selecting it from the drop-down list. Once you've configured SQL Server 2005 to use the certificate, you need to stop and restart SQL Server 2005 for the changes to take effect.

A second means for encrypting traffic between a database client and the database server is IPsec. The benefit of IPsec is that it doesn't require any configuration of SQL Server 2005, and it can be used to secure all traffic (not just SQL Server 2005 traffic) and to restrict access to the database server to only other domain-joined systems.

9. Configure Login Privileges and Server Roles

The next step in securing SQL Server 2005 is to configure login rights and server roles. For example, by default, members of the local Administrators group are granted login privileges. They're also granted membership in the sysadmin role, which gives them nearly unrestricted access to both the database server and the databases it manages. Members of the SYSTEM group, which represents the OS, also have sysadmin membership.

In most environments this access isn't an issue, but in some, it might not be desirable to allow system administrators access to the database server and databases in this way. You might instead want to grant named user accounts sysadmin role membership and deny Administrators and SYSTEM the sysadmin role. This is especially true if you're running multiple instances of SQL Server 2005 on one system, such as in a hosting environment.

By extension, when planning which databases will be hosted on a SQL Server 2005 instance, you should ensure that only the minimum permissions required be granted to users to access the databases for SELECT, INSERT, UPDATE, and DELETE operations, and to execute stored procedures. SQL Server 2005 supports a more fine-grained approach to security than SQL Server 2000, so you can apply more granular rights, including to users who perform maintenance functions.

10 Encrypt the Databases

The last step in securing your SQL Server 2005 system is protecting the data within your database files when SQL Server 2005 and the host OS aren't running. This step, which addresses the data aspect of the defense-in-depth model, encrypts the databases.

There are a number of approaches to encryption in SQL Server 2005, including support for encryption within the databases themselves. The simplest approach, however, where the main concern is one of stolen disks, backup tapes, or laptops, is to use Windows' Encrypting File System (EFS). SQL Server 2005 databases can be encrypted by using EFS and still be useable by the database server.

To encrypt databases with EFS, you must log on interactively to the database server by using the credentials that SQL Server runs under and encrypt the database and transaction log files by using Windows Explorer or the cipher.exe command-line utility. This means that SQL Server must run under a named user account, not under the LocalSystem account or one of its derivatives (LocalService or NetworkService). If you do choose to use EFS to encrypt database and transaction log files, I recommend that you ensure that a named Data Recovery Agent is configured so that the databases can be recovered if, for whatever reason, the service account is locked out or deleted.

Note that using EFS to encrypt a SQL Server database will slow performance somewhat, but not as much as you might expect. On a suitably equipped server, you shouldn't encounter problems unless the database is heavily used. The performance hit is usually negligible on desktop and laptop systems, and the security that comes with EFS would easily outweigh any slight slowdown, especially on laptops containing customers' personal information.

SQL Server 2005 Security in Depth

SQL Server 2005 contains great security features and tools, but enterprises can still be at risk if you don't install and configuring the database server correctly. The 10 steps I outlined here are practical, easy to follow, and will significantly reduce exposure to risk by addressing key areas of concern when installing and running SQL Server 2005.

You can read more about the defense-in-depth approach on which I've based this article at http://technet.microsoft.com/en-us/library/ms161948%28v=sql.90%29.aspx. This overview also contains a link to Improving Web Application Security, "Chapter 18: Securing Your Database Server," which discusses securing SQL Server 2000. Much of the advice in the chapter is valid for all versions of SQL Server and is good background reading for SQL Server 2005 users, but the chapter doesn't include advice specific to SQL Server 2005.