Maintaining a Server-Level Security Strategy

You’ll now examine the strategies that are important for ensuring that your database server-level security is properly configured. Specifically, you will examine the service accounts used to run SQL Server along with the patches and surface area configuration that help ensure your databases are properly protected at the highest level.

Typically a database administrator will rarely change the server-level configuration of a SQL Server instance once it is set up. However, you still should periodically audit your setup to ensure that no changes have been made without your knowledge and that the security of your server is not compromised. It is important to develop a set schedule for all these activities to make sure your systems remain as secure as possible.

Although you can’t prevent all types of attacks or security breaches, you can almost always detect them at a later date and deal with them appropriately. To ensure that you can detect a compromised system or service, you must implement some type of auditing process. The next few sections discuss strategies for setting up security policy and auditing compliance.

Designing a Strategy for Auditing Windows Account Permissions

Windows domain or local computer accounts interact with SQL Server 2005 in a few different ways. Logins to the database server can be linked to Windows accounts; we’ll cover strategies for dealing with these accounts later in this chapter. Some type of Windows account is also used for the various services that make up a SQL Server instance.

The service accounts chosen for use with SQL Server should conform to a variety of best practices to ensure that they do not offer unauthorized users a chance to gain control of the database server. We’ll cover how to choose a service account and what characteristics you should audit on a regular basis.

Understanding the Different SQL Server Service Accounts

The SQL Server service logs into the Windows host or domain in order to run in a manner similar to an administrator who presses Ctrl+Alt+Del and enters a username and password. A service does this automatically by retrieving the username from the registry and the password from encrypted storage.

As an administrator, you should follow best practices for choosing accounts for the various services and assigning permissions. The following list includes the factors you should consider when choosing service accounts.

Note 

You should use SQL Server 2005 Configuration Manager when choosing or changing service accounts to ensure that the proper permissions are assigned to the account on the local computer.

• Create separate accounts for each service.   Each service in a SQL Server instance should be assigned its own Windows account if a domain account is required. This limits the dependency of one service on another and also prevents a compromised account from being used to gain even more permissions.

• Assign the minimum amount of permissions required.   The permissions and rights assigned to any account should be kept to the minimum required to perform that account’s function. You can add more permissions as necessary, but adding permissions in advance of the actual need for them unnecessarily compromises security.

• Create complex passwords.   Since these accounts will likely never be accessed by an actual person or typed in, you should use a longer than usual password. Consider creating highly complex, long, one-time passwords for these accounts.

• Limit network access.   In most installations, the SQL Server services will not need to access any resources on the network. If this is the case, then the service accounts chosen should not have any access to network resources. If access is required, be sure to assign permissions only to the specific resources that will be accessed.

• Change the password regularly.   Although this account should be accessed only by the service, you can never be sure whether the password has been compromised or changed. One protection against this is to regularly change all the service account passwords during a maintenance window.

When you install SQL Server 2005, the installation program creates groups for each service and places the chosen account in those groups. This ensures that the proper permissions are assigned to each group and in turn each account. This conforms to the recommended policy of assigning users to groups and permissions to groups. You should use these groups for permissions and also periodically audit the groups to ensure that there are no unauthorized users in these groups.

Tip 

You can check on access to resources and permissions granted by temporarily assigning a test account to the service account group and then logging in with the test account and checking access. Be sure to remove the test account from the group when you are finished.

Choosing Service Accounts

One of the items listed in the previous section is the assignment of the minimum permissions required for that service. Prior to SQL Server 2005 and Windows 2003, administrators often assigned the Local System account to services that did not require network access. This is not recommended since this account has unlimited privileges on the local operating system.

Windows 2003 has two new built-in accounts that have fewer privileges than Local System and are better suited for services such as the SQL Server database service. These two accounts are Local Service and Network System. If you need additional access, then you should choose a domain user account. Table 10.1 lists the permissions and the situations where accounts should be used.

Table 10.1: Service Account Choices and Recommendations
Open table as spreadsheet

Account	Rights	Recommended Use
Local System	Unlimited rights on local operating system	Not recommended
Local Service	Limited rights on local operating system	Recommended if no network access needed
Network Service	Same as Local Service with network rights equivalent to the computer account of the local operating system	Recommended for limited network access
Domain user	Rights assigned by administrator	Recommended for each service that needs rights to mail, replication, other SQL Server instances, or other services

No matter which type of account you choose for each service, you should document the details of the account permissions and function and regularly review these details to ensure that nothing has changed. This is essential for ensuring a secure server.

Designing a Strategy to Audit SQL Server Service Access

SQL Server is a complex software product that performs a great many tasks efficiently. To maintain security in this complex environment, every task runs as a process, and each process executes inside the context of some security account. The previous section introduced the service accounts that run each subsystem of SQL Server and the various characteristics that are important for a secure environment. This section will examine the next level of server-level security by introducing the different tasks that SQL Server carries out on behalf of users.

In the following sections, we’ll discuss the permissions required for each of the most common SQL Server services as well as server logins and the items that should be audited on a regular basis.

As you devise a strategy for your service accounts, be sure you do not apply the same policy to all server instances. If one server instance requires more privileges because of its application, do not grant additional rights to other servers. Conversely, do not cause issues with a server by not granting it the necessary permissions in order to simplify administration.

In all cases, you should document the rights and accounts required for each instance and service and use that record when you perform your regular audits.

SQL Server Service

The SQL Server service is the MSSQLServer service for the default instance and SQLServer2005MSSQLUser$ InstanceName for named instances. Many DBAs do not consider this service account a threat, but it should still be properly secured and audited like any other service account.

This account can execute the SQL Server executable and provide database services; however, it also allows users to access the registry through the xp_regwrite procedure as well as execute Windows shell commands through the xp_cmdshell procedure in some situations. With these methods of access for SQL Server users, this account should not be a member of the Administrators group on the local server. A regular user with Log On As a Service rights and the folder permissions granted to the SQLServer2005MSSQLUser$ ComputerName $MSSQLSERVER group can run this service.

These guidelines have some exceptions, such as in cluster installations or situations where two SQL Servers need to communicate. In any of these cases, you should grant the minimal level of permissions to the service account.

Auditing of this account should ensure that it conforms to the set of rights you determine are required and that these rights are the same as what was documented for each server instance when it was set up.

SQL Server Agent Service

The SQL Server Agent is the primary subsystem that DBAs use to help manage the server and automate administrative tasks. The SQL Server Agent schedules jobs, performs maintenance, and even completes some user-level tasks. The account that runs SQL Server Agent usually is configured to send alerts to administrators, and it sends these mails through SQL Agent Mail. This often requires a domain user account to access the mail server. The SQL Server Agent service should be able to run as a regular user account in most instances. The exceptions are if any of the following tasks must be performed by this service:

• Restarting the SQL Server service automatically

• Detecting periods of low activity to run maintenance tasks

• Allowing ordinary users to run operating system tasks or scripts

If these exceptions are required, the SQL Server Agent service account must be a member of the local Administrators group.

Since this account is a member of the SQL Server sysadmin group, this account should be carefully secured and a strong password chosen. Many DBAs run this service and the database server under the same account to simplify administration, but this is not recommended. Using a separate account requires minimal effort, allows you to determine which service is performing a task, and prevents the compromise of one account from affecting another service.

Auditing of this account should take place on a regular basis to ensure the rights granted are limited to those deemed necessary and are documented.

Other Subsystems

You can install a number of other subsystems on SQL Server 2005. Table 10.2 lists all 10 of the services, along with the recommended accounts and whether the service is instance-aware. Instance-aware services are installed separately for each SQL Server instance on a Windows host. Services that are not instance-aware are installed only once on a Windows host, regardless of the number of database server instances installed.

Table 10.2: SQL Server Subsystems
Open table as spreadsheet

Subsystem	Instance-Aware?	Default Service Account
Reporting Services	Yes	Domain user
Analysis Services	Yes	Domain user
Notification Services	No	No default; must be manually configured
Integration Services	No	Network Service (Local System on Windows 2000)
Full-Text Search	Yes	Same account as SQL Server service
SQL Server Browser	No	Domain user (Local System on Windows 2000)
SQL Server Active Directory Helper	No	Network Service
SQL Writer	No	N/A

For each of these subsystems, you should choose an appropriate account using the same considerations discussed earlier: the minimum level of permissions and what type of access is required. In all cases, you should document your choices and periodically audit them.

Maintaining a Strategy to Assign the Appropriate Minimum Level of Privileges

The hallmark of a good security policy is granting the minimum level of privileges necessary for each user or process to complete the tasks it is assigned. Adding all users to the sysadmin role would allow them to work with SQL Server and run all the queries they need, but this is unnecessary. The vast majority of users and processes perform only a few tasks, and they can easily be assigned only those rights required for those tasks. We’ll discuss users later in this chapter; we’ll now cover the server-level processes and the rights they require.

Service Accounts

We mentioned earlier in this chapter that the service accounts you use to run the SQL Server subsystems should be granted only the rights and privileges they require to run. In most cases, this means that service accounts should be low-level system accounts, such as Local Service, or regular domain user accounts with the rights to Log On As a Service to the particular machine on which they are running a service. Most services do not need administrative rights on the Windows host.

If you need to assign additional rights, such as for services in a cluster or that perform system functions, then you should document the need and ensure that a very strong, long password is chosen for these accounts.

You can also enforce special requirements for any domain user accounts used for service accounts by placing these accounts in their own organizational unit (OU) with settings that limit the ability of these accounts to be used for any other purpose.

Auditing Service Accounts

In addition to assigning the minimum amount of rights to accounts chosen to run services, you should periodically audit the accounts to be sure that they have not been granted additional rights or that someone has not used the accounts inappropriately.

An audit requires that you have documented which accounts are linked with which services and also any special permissions they have been assigned. Since a regular service account requires limited permissions, you should document that all accounts should be limited to that set of rights unless otherwise noted.

Service Account Lockouts

Service accounts should be used only to log into the Windows operating system by the service itself. This means these accounts should never be locked out. If you find that a service account is being locked it, either it has a password problem or an attempt is being made to hack the account password. Investigate any lockouts immediately to determine the root cause.

Warning 

A locked-out service account is not necessarily a sign of your server being hacked. This also can happen when multiple services share service accounts. If the password is changed on one server and not the other, the account will become locked when the second service attempts multiple logins with the old password.

Exercise 10.1 shows how you can determine whether a service account is locked out.

Exercise 10.1: Determining Account Lock Status

You will be examining a service account in this exercise to determine whether it is locked out.

1. Access the SQL Server Surface Area Configuration tool from the Start menu by clicking Start All Programs Administrative Tools Computer Management.

2. Select the Local Users and Groups item in the left pane and then the Users folder.

   

3. Right-click the user account in the right pane, and select Properties. In this case, the service account name is sqlguy.

   

4. Notice that the last check box (grayed out in this case) is titled Account Locked Out. If this box is checked, then the account is locked. You can uncheck it and click OK to enable the account.

Real World Scenario-Tightly Coupled Problems

I once worked in a small company that was growing rapidly and deploying a number of new SQL Servers to meet demand. After documenting the setup of a new server, I left the task of installing new servers to a junior DBA.

After a few months, I changed the password on the service account used on the first SQL Server as part of a regular change and restarted the service. Soon afterward I received a number of calls that users on other SQL Servers were having difficulty. The servers were behaving strangely, and we spent more than an hour trying to determine why the applications were not behaving as expected. Eventually I realized that all our SQL Servers, nearly a dozen, were using the same service account!

When I changed the first server, it caused problems with other servers that tried to authenticate themselves to the domain with the old password. This resulted in quite a bit of downtime and an embarrassed DBA group.

I realized that my documentation did not clearly specify that each service account should be different, and I had relied upon common sense to make that plain. This was a mistake on my part, but one that was easily corrected. It has led me to both clearly document my intent and ensure all services run under separate accounts.

-Steve Jones

Designing a Strategy to Apply Service Packs and Security Updates

SQL Server is like any other large software program in that it will contain bugs. Just like most other programs, the developer will periodically release patches to correct the problems as they are found and as solutions are developed. It is the job of the database administrator or system administrator to watch for patches and apply them as necessary.

Microsoft devotes a great deal of resources to ensuring that SQL Server is a secure and highly reliable software product. However, there are still issues, and Microsoft releases a constant stream of patches. SQL Server 2005 has two classes of patches that a database administrator must deal with: service packs and security updates (also known as hot fixes).

Service Packs

Service packs are large bundles of patches that are distributed in one large file and meant to contain all previous patches to that point in time. If you install a new database server and apply Service Pack 2, you do not need to install Service Pack 1 first. The latest service pack contains all the fixes from previous service packs.

Note 

As of the time of this writing, Service Pack 2 is the most recent service pack for SQL Server 2005.

A service pack installation changes a great many files in the SQL Server installation and often results in an installation that cannot be easily removed. Most service packs are removed by uninstalling the SQL Server instance and reinstalling the database server with all patches prior to the latest service pack. This means you should proceed with caution when applying a service pack to an instance of SQL Server. You should first apply the service pack to a test server and then verify that all applications and tasks run properly. If you encounter issues, you should contact Microsoft and obtain a workaround or additional patch.

Warning 

Some of the software changed by a service pack will be non-instance-aware services, such as Integration Services. If you have multiple instances of SQL Server that use these subsystems, be sure they will not be affected by applying the patch.

Service packs are rarely released, usually about one per year, and often with a great deal of press surrounding the release. As a database administrator, you should make it a point to periodically monitor news sites relating to SQL Server for the announcement and begin planning to test the service pack as soon as possible. Since Microsoft Product Support Services often requires the latest service pack to be applied before anyone can assist you, it is good practice to apply the service pack as soon as you have verified that it will not disrupt your server’s operation.

Tip 

The latest service pack for SQL Server is always available at http://support.microsoft.com/kb/913089.

Security Updates

In contrast to service packs, security updates for Microsoft products are released on the second Tuesday of every month. All updates that are ready for release across the entire Microsoft product line are distributed, and system administrators should have a plan for applying these patches since they are occasionally critical.

Although SQL Server rarely has a patch released on a monthly basis, you’ll often see that patches for the Windows operating system are available. With SQL Server dependent on both Windows and the .NET Framework, database administrators may still need to test these patches to be sure they do not affect SQL Server.

You might not see new patches every month, but when you do see new ones, you should have a plan in place to apply these patches. Typically an evaluation should take place within a day or two of the patch’s releases to determine which systems are affected. You should then apply the patch within a week to test systems; then examine those systems to be sure the patch does not disrupt normal operation or change any expected behavior. A scheduled update window should be available for the application of these patches to production systems.

Tip 

Occasionally Microsoft releases emergency patches for a critical vulnerability. A prudent database administrator will subscribe to a security bulletin such as TechNet (http://www.microsoft.com/technet), Secunia (http://www.secunia.org), or kbAlertz (http://www.kbalertz.com).

Checking for Missing Updates

You can use many different methods of determining which patches and updates you may be missing, and this section will explain how you can determine which updates need to be applied.

Microsoft has developed a few tools to help system administrators keep track of the patches that are available for its software that have not been applied to the host server. You can determine which patches have not been applied to your server in two main ways: Windows Update and Microsoft Baseline Security Analyzer (MBSA).

Note 

You can find the latest version of the MBSA tool at http://www.microsoft.com/technet/security/tools/mbsahome.mspx. The current version at the time of this writing is 2.0.1.

Either of these tools can be run from the server, and each will analyze the current configuration and compare this with the list of patches that have been released for the software on your system. You can select those updates that you want to install from Windows Update, and they will be installed for you. The MBSA tool provides you with a list of updates that are missing, which you must install manually or schedule for deployment with a tool such as Systems Management Server (SMS), as discussed in the next section.

Warning 

You should never install a patch directly onto a production server without testing it on another system.

Deploying Updates

Microsoft has developed a number of mechanisms that help deploy patches to systems:

• Microsoft Update   Most Windows servers have a menu option to connect to the Microsoft Update website and retrieve patches. This is a good way to check for updates to a particular server.

• Windows Server Update Services Server   Windows Server Update Services Server (WSUS) allows an organization to specify a particular server to retrieve all updates and then pass them along to other computers in the domain. This reduces the bandwidth requirements since only the WSUS servers download the patches from Microsoft.

• Automatic updates   Automatic updates can be configured on a server to allow it to automatically download and apply patches from Microsoft Update or a WSUS server. This is not recommended for SQL Servers.

• Systems Management Server   SMS can be deployed similarly to WSUS in an organization. This server uses agents installed on your organization’s servers to push patches to particular machines. It also allows you to schedule deployments.

• Manual   The old standby method is to download the patches and then access them on a share or some removable media from each server. If you have more than a couple servers, this is not an efficient way to update servers.

No matter which method you choose, you should have some sort of staging environment that is similar to the production servers and allows you to test the patches to be sure they will not break the operation of your SQL Servers.

Once you have deployed patches to your server, you should update your documentation to reflect the changes. This ensures that you can reproduce the same configuration on another server in the event of a disaster.

Real World Scenario-A Structured Release

Many years ago I worked in a large corporation with hundreds of Windows servers and thousands of desktops. At that time Microsoft would release patches as they were done, often several times a month. With the requirement to test all patches before they were applied to production servers, we would sometimes find ourselves in the middle of a test cycle when a new, more critical patch was released. Sometimes we would have to stop testing and rebuild our environments to test the more critical patch, resulting in backlogs of testing.

Microsoft realized the burden this placed on system administrators and moved to a regular, monthly patch release cycle, and immediately our lives improved.

Typically we had a three-week patch cycle, which was woven around our other regular tasks. Although this meant system administrators were working on patches three out of every four weeks, the load was fairly light, and it enabled us to plan our resource allocation.

The cycle worked like this: During the week patches were released, they were evaluated for criticality, and those systems that could be affected were noted. The individuals responsible for those systems were notified that patches were available and testing needed to be started. From the Tuesday of release until the next Wednesday, test systems had the patches applied.

On Wednesday of the second week, we would begin scheduling the application of patches to a small group of selected servers. These were usually less critical servers whose functions were close to those of other servers. This allowed us to deploy patches to live environments in a limited scale and deal with any problems with those servers only and not the entire environment. In a group of 500 servers, we might apply the patches to only 40 or 50 servers. Patches were scheduled for Friday night, and system owners had until Thursday to notify us of problems or reasons to not apply the patches.

The next week all additional servers would be scheduled to receive the patches on the next Friday night. The servers that had the patches applied the previous week would also be monitored for any issues.

As with any process, there were exceptions that constantly occurred, but having regular update windows scheduled greatly smoothed the process of patching our systems.

-Steve Jones

Configuring the Surface Area

The classic strategy for defending anything is to limit the places or areas in which it can be attacked. Centuries ago people realized that a fort or building in the middle of a field is vulnerable to attack on all four sides, but one that was built next to a mountain or in a canyon might be susceptible to attack on only two or three sides. Reducing the area of attack has provided an effective defense for centuries of warriors.

In securing computer software, we speak of a similar reduction of the surface area of attack. If your computer is not connected to a network, you have limited the attack to the physical server hardware or a login from the local console. Since most SQL Servers are connected to a network of some sort, that is not an option, but you can reduce the surface area that can be attacked in many other ways.

This section will examine a few strategies that you can use to expose the minimal number of services that can be attacked.

One of the first tasks you should do after configuring a SQL Server instance is to run the SQL Server Surface Area Configuration tool. Exercise 10.2 shows how you can execute this tool against your instances.

This tool examines your installation and points out the items that are typically not configured correctly by many administrators. It is the result of much research on different installations and what configurations were actually needed to run effectively. The tool reports on your configuration and suggests changes that will make your instance more secure.

Exercise 10.2: Running the SQL Server Surface Area Configuration Tool

In this exercise, you will execute the SQL Server Surface Area Configuration tool.

1. Access the SQL Server Surface Area Configuration tool from the Start menu by clicking Start SQL Server 2005 All Programs Configuration Tools SQL Server Surface Area Configuration.

2. When the tool starts, a welcome screen appears, as shown here. Note that at the bottom you can configure which computer this will be run against by clicking the Change Computer link.

   

3. In this case, you are running this tool against the local computer. Click the Surface Area Configuration for Services and Connections link; the view shown here will appear. This graphic shows two instances (SALES and SS2K5) on this computer along with an installation of Integration Services and the SQL Server Browser. Your installation may look different.

4. Click each component, for example SQL Server Agent for the SALES instance, on the right side to see the service name, the start-up type, and the current status. You can also reconfigure the start-up type or change the status of the service from here.

5. Close the Services and Connections window by clicking Cancel, and click the Surface Area Configuration for Features selection on the start-up screen (as shown earlier). This will open the window shown here. This window lists the various features that can be enabled or disabled and affect security. In this case, you can see that each feature is listed under the instance it applies to and the right side shows what can be configured. In this particular example, CLR Integration is selected and the Enable CLR Integration check box is selected.

   

The Surface Area Configuration tool allows you to easily modify the various components, services, and features on SQL Server instances that could be attacked. You can disable or stop services, enable or disable features, and easily compare a number of settings between servers.

You should document the settings in this tool and use it in auditing all instances periodically to ensure that only required services and features are enabled.

Maintaining an Encryption Strategy That Meets Business Requirements

SQL Server 2005 introduces a great many new encryption functions to allow you to secure your data from being read by unauthorized users. This can even include the system administrators, who can grant rights to read data they themselves cannot read. The next sections will cover the encryption hierarchy in SQL Server 2005 and how you can best implement it in your organization.

Encryption Basics

The basic idea behind the encrypted data is that several keys are used to transform the data from a readable form into something that is unrecognizable. For example, an EncryptByPassPhrase() function will encrypt data using a password as the key. Figure 10.1 shows the results of using this function.

Figure 10.1: Basic encryption of data

As you can see, the plain-text value “My salary is $100,000” has been transformed into something that is not easily recognizable, the cipher text. Without knowing the password, it would be extremely difficult for someone to change the encrypted value back to a readable sentence.

Keeping track of passwords has proven difficult for many people working on computer systems. In addition, a plain-text password is a relatively weak security mechanism in and of itself. Over the years cryptographers have created a variety of mechanisms that provide greater security. The algorithms involved are divided into two types: symmetric and asymmetric.

Symmetric Encryption

The password shown in Figure 10.1 was an example of a symmetric encryption, where the same key is used to both encrypt and decrypt the data. This means both users must have the same key. This is the least secure method of encryption, though the process of having the symmetric keys stored inside SQL Server 2005 and using a strong algorithm is more secure than using a password, which must be sent across the network.

In SQL Server 2005, you can implement a symmetric key with a variety of algorithms. These offer varying amounts of strength and speed. All symmetric encryption operations are relatively fast because of the nature of these algorithms, but there may be requirements to use a particular type of key because of government or regulatory mandates. The following algorithms are available for symmetric key encryption:

• DES

• Triple DES

• RC2

• RC4

• RC4 (128-bit)

• DESX

• AES (128-bit, 192-bit, or 256-bit versions)

When you create a symmetric key, you can choose to specify which algorithm is used. By default, the Triple DES algorithm is used. The following shows how to create a basic symmetric key:

 CREATE SYMMETRIC KEY CreditCardCipher   WITH ALGORITHM DESX   ENCRYPTION BY PASSWORD = 'This is my cipher password'

In this example, the key is secured by a password, which must be entered to use the key. Although this may not seem more secure than just using a password, the data is better protected because the key itself does not transit the network. Symmetric keys can also be secured with asymmetric keys or certificates, which are discussed in the following section.

Asymmetric Encryption

A more secure and more difficult-to-crack type of encryption is an asymmetric encryption using a two-key algorithm. In this type of encryption, two keys are paired together. One key encrypts the data, and the other decrypts the data (or vice versa). Typically one key is known only to a single user and is called the private key. The other half of the pair is made available to other users and is known as the public key. If you encrypt data with your private key, anyone with your public key can decrypt it. However, anyone who does decrypt the data knows that you must have encrypted it since you have the private key. This provides the additional element of identity verification that symmetric algorithms do not provide.

By using a combination of your private key and someone else’s public key, you can enable a secure method of communication that ensures that only you and one other party can read the information. It also ensures that each of you is the party you claim to be with the additional element of identity verification.

SQL Server 2005 supports the RSA algorithm for asymmetric keys, but you can choose to specify a key length of 512; 1,024; or 2,048 bits. Longer keys provide stronger encryption but at the expense of additional processing power being required to complete the operation. You can create an asymmetric key as shown here:

 CREATE ASYMMETRIC KEY StrongerKey01   WITH ALGORITHM RSA_1024   ENCRYPTION BY PASSWORD = 'A Very Str0ng P@ssWorD!'

This creates a public and private key pair that will be used to secure data. The private key is protected by the Database Master Key, which is discussed in the “SQL Server 2005 Encryption Hierarchy” section. Since Certificate Authorities (CAs) often issue public and private key pairs, SQL Server 2005 allows you to load a key pair from a file or .NET assembly. The following code loads a key from the file MyPrivateKey.key, which was received from a CA. SQL Server 2005 can also act as its own CA.

 CREATE ASYMMETRIC KEY CompanyPublicKey01   FROM FILE = 'C:\MyPrivateKey.key'   ENCRYPTION BY PASSWORD = 'Rkd*2kdajds83ksdFs'

Certificates

Certificates are another method of encrypting data in SQL Server 2005. Certificates function as asymmetric keys with the additional features of expiration and revocation. These allow you to automatically remove access for a certificate after a set amount of time or immediately without having to reencrypt your data with another key. SQL Server 2005 certificates conform to the X.509 specification.

Certificates are often issued by trusted CAs such as VeriSign or Thawte to allow businesses to secure and verify communications between them. SQL Server 2005 can create certificates from files or issue its own certificates. Creating a certificate is similar to creating an asymmetric key. Note that you can include an expiration date and an identifying subject, among other optional fields.

 CREATE CERTIFICATE GlobelSalesCert2007   ENCRYPTION BY PASSWORD = 'My#nCrypTioNKey'   WITH EXPIRY_DATE = '12/31/2007',   SUBJECT = '2007 Sales Encryption Certificate'

Internally, SQL Server can use certificates to secure endpoints for Service Broker and web services.

SQL Server 2005 Encryption Hierarchy

Since keys are inherently difficult to remember and manage, SQL Server 2005 has a number of features built into it that enable you to implement encryption features securely and easily. When each instance of SQL Server first looks to create another key, a service master key is created and encrypted with the local machine key. This key is the root of the encryption hierarchy and used to encrypt all other keys. It is also required to decrypt other keys and therefore should be backed up and carefully protected.

To perform a backup of the service master key, you can use a specific BACKUP option:

 BACKUP SERVICE MASTER KEY   TO FILE = 'c:\MyServiceMasterKey.key'   ENCRYPTION BY PASSWORD = '12dsk4f93jsd'

It is important that this backup file be protected because it is needed in disaster recovery situations if you need to restore encrypted databases to a new server. It is also important to protect this password and store it securely because it is part of what is needed to gain access to your encrypted data.

To implement encryption in each database, you must create a database master key. This key is encrypted using the service master key created by SQL Server 2005 and ensures that a database is protected from being attached or restored to another SQL Server 2005 instance. This database master key is also used to encrypt all other asymmetric keys in the database.

To create a database master key, you can issue the following in the database in which it is needed:

 CREATE MASTER KEY   ENCRYPTION BY PASSWORD = 'Pr0T#ect3dMaster'

This creates the key with the Triple DES algorithm and protects it with the service master key.

Certificates and asymmetric keys are below the database master key in the encryption hierarchy. These are protected by the database master key and in turn are used to protect other asymmetric keys or symmetric keys. The symmetric keys are at the lowest level of the encryption hierarchy in SQL Server 2005.

Tip 

Maintaining the security of your keys has always been a problem in any large organization. Since multiple people need to know the passwords for secured items, such as keys and accounts, some type of secure storage is required. Password Safe is one such mechanism. It is an open source product whose code has been reviewed to be sure it is secure. You can store usernames and passwords in this product and protect the data with a password. We recommend regularly changing the password of your data store as well.

Encryption Strategy

Implementing encryption of any kind imposes an additional resource demand on your SQL Server. Cryptographic operations are CPU intensive and can easily overwhelm your server if you were to encrypt all your data. You must determine two different parts of your encryption strategy: the encryption algorithms and the scope of encryption.

Each of the algorithms that SQL Server 2005 supports has different characteristics, but they are all either symmetric or asymmetric algorithms. Asymmetric algorithms are much stronger but require orders of magnitude more processing power to complete. For this reason, the recommended strategy is to encrypt your data with symmetric keys and then encrypt the symmetric key with an asymmetric key. This provides strong encryption for the keys necessary for decrypting data while balancing the processing requirements.

The other important consideration in determining your encryption strategy is the scope of encryption. No matter which type of encryption algorithms you choose, you’ll encounter a performance penalty for both encrypting and decrypting data. This is a result of two different issues, both of which will impact which data you choose to encrypt.

The first issue is that if you encrypt every column in a table, then even if you need one or two columns, the server must decrypt the data. Although you might not want to have the names of your customers exposed unnecessarily, do you really need to encrypt the primary key? Or is the company name sufficient and you can let the name of the contact stay in plain text? When deploying your encryption, think carefully about which columns need encryption and which ones do not provide a measurable benefit from being obscured. Often you will find that most columns can remain plain text and only a few pieces of data need to be protected.

The second issue is even more critical to ensuring that the performance of your server remains at its peak. Columns that are encrypted cannot be indexed, or used in joins, sorts, grouping operations, or filtering. This means much of the optimization that can take place inside a query is negated if you are encrypting all the columns in your table.

This elimination of encrypted columns from query processing means that primary and foreign keys should probably never be encrypted. Also columns that are good candidates for indexing should probably not be encrypted.

Tip 

If you really need to encrypt a column that is functioning as a primary or foreign key, it is recommended that you derive a surrogate key and use that instead as the primary or foreign key.

There is one other consideration as well. Data that is encrypted is usually larger than unencrypted data. This means the more data you choose to encrypt, the more storage space and backup space will be required for your system. For most systems this is likely not a significant amount of storage, but you would need to calculate this for your system.

Because of these factors, the deployment of encryption inside a database is usually limited in scope to just those columns that truly must be secured-data such as salary information, cost data, or some other potential competitive advantage that you do not want an administrator to read or to be disclosed if the system is compromised.

Using a Server-Level Audit Checklist

This list is a consolidation of those areas in which you should perform periodic actions to verify that your security policy is properly implemented:

• Change service account passwords.

• Audit membership of service account groups.

• Verify all accounts have the minimum privileges required to perform their jobs.

• Ensure service accounts are not being shared among services.

• Back up the service master key and store it securely off-site.