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.