Monthly Archives: September 2008

SQL Server 2008 Transparent Data Encryption

Posted on by

Whether we like it or not, DBAs are becoming security experts. It’s not a job we want, but it’s been thrust upon us as we are the protectors of the organization’s data. Whether required by law, or just for self-protection, more and more of the data in our databases need to be encrypted.

In SQL Server 2000 and earlier, if we wanted to encrypt data in our databases, this usually meant client-side encryption, where all the encryption and decryption occurred in the application, not in the database. This required custom-written applications.

In SQL Server 2005, column-level (sometimes called cell-level) encryption became available. Now, encryption could occur within the database, but it was not easy to use, offered poor performance, and it required a re-architecture of the application, along with changes to the database schema. Even with these downsides, column-level encryption offered some advantages: such as granular security; data is encrypted in memory and disk; and explicit key management, which allows different users to protect their own data using their own keys, even preventing the DBA from seeing a user’s data. Even so, the disadvantages were so great that only the most sensitive columns of a table were generally encrypted, which meant that much of the data in a database was still left unencrypted.

In SQL Server 2008 (Enterprise Edition only), a new form of database encryption has been introduced: Transparent Data Encryption (TDE), which includes these major features:

  • Encrypts the Entire Database: With essentially a flip of a switch, the entire contents of MDF files, LDF files, snapshots, tempdb, and backups are encrypted. Encryption occurs in real-time as data is written from memory to disk, and decryption occurs when data is read from disk and moved into memory. Encryption is done at the database level, so you can choose to encrypt as few or as many databases as you want. The major benefit of encrypting a database with TDE is that if a database or backup is stolen, it can’t be attached or restored to another server without the original encryption certificate and master key. This prevents those nasty situations you hear about in the news where a backup of a database has been shipped from one location to another and is “lost,” which potentially exposes a company to liability issues.
  • Easy to Implement and Administer: As its name implies, Transparent Data Encryption is transparent to applications. This means that your applications, and database schema, don’t have to be modified to take advantage of TDE. In addition, initial setup and key management is simple and requires little ongoing maintenance.
  • Uses Minimal Server Resources to Encrypt Data: While additional CPU resources are required to implement TDE, overall, it offers much better performance that column-level encryption. The performance hit averages only about 3-5%, according to Microsoft.

While TDE offers many benefits over other types of encryption, it has some of its own limitations, which are important to consider. These include:

  • TDE does not protect data in memory, so sensitive data can be seen by anyone who has DBO rights to a database, or SA rights to the SQL Server instance. In other words, TDE cannot prevent DBAs from viewing any data they want to see.
  • TDE is not granular. Then entire database in encrypted.
  • TDE does not protect communications between client applications and SQL Server, so other encryption methods must be used to protect data flowing over the network.
  • FILESTREAM data is not encrypted.
  • When any one database on a SQL Server instance has TDE turned on, then the tempdb database is automatically encrypted, which can contribute to poor performance for both encrypted and non-encrypted databases running on the same instance.
  • Although fewer resources are required to implement TDE than column-level encryption, it still incurs some overhead, which may prevent it from being used on SQL Servers that are experiencing CPU bottlenecks.
  • Databases encrypted with TDE can’t take advantage of SQL Server 2008’s new backup compression. If you want to take advantage of both backup compression and encryption, you will have to use a third-party application, such as SQL Backup, which allows you to perform both of these tasks without penalty.

For some organizations, they might want to consider implementing both column-level encryption (which still is available in SQL Server 2008) along with TDE for a database. While more complex to set up and administer, this combination offers greater security and encryption granularity than does either method used alone.

How Transparent Data Encryption Works

TDE is able to minimize resource utilization and hide its activities from user applications and the Relational Engine because all encryption/decryption occurs when data pages are moved between the buffer pool and disk.

Let’s say that TDE has been turned on for a database that includes a single MDF file, a single LDF file, and tempdb. As I mentioned earlier, whenever any database on a SQL Server instance is encrypted using TDE, then the tempdb database for that instance is also encrypted.

As SQL Server moves data pages from the buffer pool to the MDF file, the LDF file, or tempdb, the data is encrypted in real-time before it is written to disk. On the flip side, as data pages are moved from the MDF file or tempdb to the buffer pool, they are decrypted. In other words, when data is on disk, it is encrypted, but when data in memory, it is not encrypted.

When a backup is made of an encrypted database, it cannot be restored unless the DBA has access to the certificate and master key that was used to encrypt the database. This prevents anyone from stealing a backup and restoring it on a different SQL Server. The same goes for when you detach and reattach a database to a different SQL Server.

TDE supports several different encryption options, such as AES with 128-bit, 192-bit, or 256-bit keys or 3 Key Triple DES. You make your choice when implementing TDE.

How to Implement Transparent Data Encryption

Now, let’s take a brief look at how to turn on TDE for a database. This can only be done using Transact-SQL code, as SSMS (SQL Server Management Studio) has no option to perform this task. Before we drill down into the details, let’s look at the four key steps required to turn on TDE. They include:

  • Create a Master Key: A master key is first created. This key, which is accessible with a password, is used to protect a certificate, which we will create in the next step. This key is stored in the master database in an encrypted format.
  • Create or Obtain a Certificate Protected by the Master Key: This certificate is used to protect the database encryption key we will create in the next step. In addition, this certificate is protected by the master key we created in the previous step. The certificate is stored in the master database in an encrypted format.
  • Create a Database Encryption Key: This is the key that will be used by SQL Server to actually encrypt the data. It is protected by the certificate created in the previous step. This key is stored in the database that is encrypted, and is stored in an encrypted format.
  • Turn TDE On: Once all the above has been created, a command is run to tell SQL Server to begin encrypting all of the data using the database encryption key created in the previous step. This process may take some time, depending on the size of the database. Ideally, the database should not be used in production until the database has completed the initial encryption process.

Creating a Master Key

Assuming one has not already been created for another reason, the first step is to create a master key. The master key is a symmetric key used to protect the private keys of certificates and asymmetric keys. In this particular case, the master key is used to protect the certificate which will be created in the next step. When a master key is created, it is encrypted using the Triple DES encryption method and protected by a user-provided password.

To create a master key, run the following code:

–Create a master key
–The master key must be created within the master database
USE master ;
CREATE MASTER KEY ENCRYPTION BY PASSWORD =
‘User-Provided Password’ ;
GO

Obviously, the password you provide should be very obscure, and you will want to document in a secure location.

Create or Obtain a Certificate Protected by the Master Key

For this example, we are going to create a new certificate, although you can use a pre-existing certificate if available. The certificate is used to protect the database encryption key that we will create next. In addition, this certificate is protected by the master key created in the previous step.

–Create a certificate by the name of TDECert
USE master ;
CREATE CERTIFICATE TDECert WITH SUBJECT = ‘TDE Certificate’ ;
GO

Create a Database Encryption Key

Now that the certificate has been created, the next step is to create a database encryption key and protect it with the certificate we created in the last step. This is the encryption key that is used by the database to encrypt all of the data. It is during this step that you choose which encryption method is used to encrypt your database.

–Create Database Encryption Key Inside Database to Be Encrypted,
–and Protect It with the Certificate
USE AdventureWorks ;
GO
CREATE DATABASE ENCRYPTION KEY WITH
ALGORITHM = AES_256 ENCRYPTION BY
SERVER CERTIFICATE TDECert ;
GO

Backup the Private Encryption Key and Certificate

Once you have created the master key and certificate, they should be backed up immediately. If you lose these, you can’t move or restore the database.

–Backup the private key and certificate to two separate disk files
USE master ;
GO
BACKUP CERTIFICATE TDECert TO FILE =
‘c:certificate_backupsAdventureWorks_Certificate.cer’
WITH PRIVATE KEY ( FILE =
‘c:certificate_backupsNorthwindCert_Key.pvk’,
ENCRYPTION BY PASSWORD =
‘User-Provided Password’ ) ;
GO

When this command is run, the master key and the certificate are taken from the master database and written to separate files (both in an encrypted format).

Turn TDE On

The last step is to turn TDE on. Once you run the following command, the database will begin to encrypt itself. Depending on the size of the database, and the hardware running it, this process could be lengthy. While it is possible to keep the database in production during this process, it will cause some user blocking and performance will suffer. Because of this, ideally you should only turn TDE on when the database is not being used.

–Turn TDE on
USE AdventureWorks
ALTER DATABASE AdventureWorks
SET ENCRYPTION ON ;
GO

If you want to watch the progress of the encryption, run this statement:

SELECT DB_NAME(database_id), encryption_state
FROM sys.dm_database_encryption_keys ;
GO

When the statement above is run, a state is returned. A database encryption state of “2” means that encryption has begun, and an encryption state of “3” indicates that encryption has completed. Once the tempdb database and the user database you are encrypting reach a state of “3,” you are ready to put them back into production.

From this point on, the entire user database, and tempdb database will be encrypted, although your applications will never know the difference.

Summary

If you got lost with all the keys and certificates required to implement TDE, you are not alone. It is a complex topic and beyond the scope of this chapter. The focus of this chapter was to provide you an overview of what TDE is, how it works, and how to implement it. Because of the complexity involved in using TDE, you should only implement this technology when you full understand its many complexities and after thorough testing in a test environment.

How to Script a Profiler Trace for Use as a SQL Trace Data Collection Set for the SQL Server 2008 Performance Data Collector

Posted on by

In SQL Server 2008, a new feature called the Performance Data Collector was added. It has the ability to collect SQL Server-related performance data, store it in a database called the Management Data Warehouse, and then produce various performance reports based on the collected data.

By default, the Performance Data Collector includes three different data collection sets (Disk Usage, Query Statistics, and Server Activities) that are used to collect a wide variety of SQL Server 2008 performance data. One of the features of the Performance Data Collector is that you can create your own data collection sets, which allow you to decide what kind of data you want to collect and store in the Performance Data Collector’s Management Data Warehouse. For example, if you want, you can create your own custom data collection set to collect and store Profiler trace data. Once the data has been collected and stored in the Management Data Warehouse, you can then create queries or Reporting Services reports to analyze the data you have collected.

There are two ways to create your own custom data collection set to collect and store Profiler trace data. You can either write a custom Transact-SQL script from scratch that creates the custom data collector, or you can let Profiler write the script for you. Let’s see how Profiler can do this for us.

First, ensure that the SQL Server 2008 Data Collector has been properly configured and is running properly. Next, start SQL Server 2008 Profiler and load an existing Profiler trace definition, or create a new Profiler trace definition. Keep in mind that the SQL Trace Data Collection Set will be based on your trace definition, so choose an appropriate one.

clip_image002

Figure 1: Export a Profiler Trace to a Performance Data Collector SQL Trace Collection Set.

The next step is to export the Profiler trace definition into a Transact-SQL file that can be executed later to create the SQL Trace Collection Set. To do this, From the SQL Server 2008 Profiler, select “File|Export|Script Trace Definition|For SQL Trace Collection Set,” and a “Save As” dialog box appears, allowing you to name the file and to save it in any folder you prefer. Once the script has been saved, it can be opened in SSMS. For example, the Transact-SQL script that is created looks similar to this (it has been truncated because of its length).

clip_image004

Figure 2: Profiler can create a SQL Trace Collection Set for you automatically.

Before you can run this script and create the SQL Trace Collection Set for the Performance Data Collector, you need to make two changes in the code. If you look through the generated code, you will see these place holders:

‘SqlTrace Collection Set Name Here’

‘SqlTrace Collection Item Name Here’

You will need to replace these place holders with descriptive names. Once the code is changed and executed, you will see the new SQL Trace Collection Set in Performance Data Collection portion of SSMS, along with the three default collection sets that come with SQL Server 2008. At this point, you can enable the new custom collection set, and it will begin collecting Profiler trace data based on the criteria you specified in the Profiler template you used as the basis for creating it. If you like, you can create as many different SQL Trace Collection Sets as you want, with each one based on a different Profiler template.

Unfortunately, there are no built-in reports available to view the Profiler data you have collected. Because of this, you will need to create your own Transact-SQL scripts, or to create Reporting Service reports, to view and analyze the Profiler trace data.

One warning about creating your own custom SQL Trace Collection Sets, and that is they can use a lot of SQL Server resources and disk space when enabled. To minimize this impact, ensure that the Profiler trace definitions that you use to create your SQL Trace Collection Sets only collect the minimum number of events and data columns you need. In addition, only enable SQL Trace Collection Sets as needed; don’t run them all the time. If this is the first time you have tried this, you might want to first test this on a test SQL Server before implementing it on production server.

Can Social Networking Help Your DBA Career?

Posted on by

In previous blog entries, I have talked about the importance of managing your online brand. One important part of this is participation in social networking websites. If you are not yet familiar with the term, social networking refers to websites that are designed to make it easy for people to share information about themselves, and at the same time, connect with others who have similar interests.

Social network websites come in many different forms to meet the diverse needs of their members. According to Wikipedia, the 10 most popular social networking websites are:

  • MySpace
  • Facebook
  • Windows Live Spaces
  • Habbo
  • hi5
  • Orkut
  • Friendster
  • Flixster
  • Classmates.com
  • Bebo

While you may be familiar with some of these social networking websites, many of them may be new to you. This is because many of these top 10 sites are popular in countries other than the United States.

While social network websites can be fun and bring people with similar interests together, what role do they play for DBAs? While this list is not all inclusive, some of the benefits for DBAs joining social networking websites include:

  • Finding and connecting with DBAs locally who you didn’t know exist. This may be a way to find out how many DBAs are in your local area so that you can get together and perhaps start a local user’s group.
  • Connect with DBAs throughout the world who share common interests. For example, if you are into SSIS packages, perhaps you might want to find other DBAs who find SSIS packages as fascinating as you do.
  • To keep up with your DBA friends, no matter where they are.
  • To make it easy for people to find and connect with you. This can be very important if you are a consultant or are looking for a new job.
  • To promote yourself and manage your online brand. Many social network websites are in effect mini-websites you can share with others who you are and what you know.

As you might imagine, some social networking websites are more applicable to DBAs than others, especially if you want to use them for self-promotion or for business-related relationships. In a recent poll on SQLServerCentral.com, DBAs were asked what social networking websites they belong to. The top 6 results included:

  • LinkedIn
  • Facebook
  • MySpace
  • Twitter
  • Del.icio.us
  • Plaxo

As you can see, there is not a lot of overlap between the list of the top 10 most popular social networking websites on the Internet and the top 6 used by DBAs who visit SQLServerCentral.com. I think this is because most DBAs, who do participate in social network websites, choose those that are more business-oriented, not the ones that are more social-orientated.

Personally, there are three social networking websites that I use extensively, all of which happen to be in the top 6 list above. They include LinkedIn, Plaxo, and Twitter. I want to share a little about each of these website, and why I use them.

LinkedIn

LinkedIn is a social networking website dedicated to developing professional relationships, and currently has over 25 million members. Basic membership is free, and allows you to create a profile (essentially a resume) that can be shared with the public, and also allows you to link with others who you have some direct relationship with, such as a friend or business acquaintance. LinkinIn can be used to find new clients, service providers, subject matter experts, business opportunities, connect with industry insiders, ask questions, find a new job, find a job candidate for a new position, and much more. In addition, if you use the free Xobni add-in to Microsoft Outlook, Xobni can display information from LinkedIn profiles as part of your contacts list.

I use LinkedIn because it allows you to identify new contacts from your currently existing contacts. For example, I currently have 213 direct connections in LinkedIn. This are people who I have some sort of direct relationship. But because each of my direct connections have connections of their own, I am one connection away from about 25,000 other people, and two connections away from over 2 million people. So if I want to make a contact with someone who I am not directly connected with, all I have to do is to contact one of my direct connections and ask them if they would introduce me to the person who I want to meet, and so on. This allows me access to a huge number of people with an almost infinite variety of knowledge and interests.

You can see my LinkedIn profile at:

http://www.linkedin.com/in/bradmcgehee

Plaxo

Plaxo started out as a service to help you automatically update your Outlook contacts list. For example, if a contact in my Outlook contacts list is also a Plaxo member, then if they change their contact information, then Plaxo (through an Outlook add-in) automatically update the information in my Outlook contact list. More recently, it has morphed into a business social networking website that offers many other features besides just keeping track of your contact’s address and other contact information. I use Plaxo mainly for its automatic contact update feature, but I also use it for my resume (similar to LinkedIn). It is just another way that people can find me.

You can see my Plaxo profile at:

http://Brad_McGehee.myplaxo.com

Twitter

Twitter is a somewhat odd social networking website. Essentially, it is a mini-blog that allows you to enter short (up to 140 characters) entries about what you are doing. Many people use it like a public diary, sharing their daily activities. Personally, I use it to promote myself, such as when I have blogged, written a new article, or spoken at a users group. As a Twitter member, you have the ability to follow others, and others can follow you. In other words, you develop a network of people who you are interested in and want to follow, and others who want to follow you.

You can see my Twitter feed at:

http://twitter.com/bradmcgehee

Should You Join a Social Networking Website?

Is social networking something you should do as a DBA? If you aren’t interested in promoting yourself or developing online contacts, or if you don’t have enough time to actively engage in social networking, then not participating won’t hurt your DBA career.

On the other hand, if you want to promote yourself and develop new contacts, and you have the time to participate, then do so. It’s a great way to not only stay in contact with your current network of friends, it is a great way to expand that network.

If you decide to participate, the hardest part is deciding which social networking websites best meet your needs. This will depend on you, and your interests. I suggest you check out multiple social networking websites and see what they have to offer. Start with one, and if you enjoy it, then expand and join several others. Each social networking website has its own pros and cons, and the more you join and engage in, the greater your ability to manage your on-line brand.