Artikel
10/04/2012

Moving a TDE Protected Database to Another SQL Server

This topic describes the steps to protect a SQL Server database by using transparent data encryption (TDE), and then move the database to another instance of SQL Server.

In This Topic

Before you begin:

Background

Security
Example:

To create a database protected by transparent data encryption

To move a TDE protected database a new instance of SQL Server
Follow Up: Steps to take after you move a TDE protected database to a new instance of SQL Server

Background

TDE performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a symmetric key secured by using a certificate stored in the master database of the server or an asymmetric key protected by an EKM module. When moving a TDE protected database, you must also move the certificate or asymmetric key that is used to open the DEK. The certificate or asymmetric key must be installed in the master database of the destination server, so that SQL Server can access the database files. For more information, see Understanding Transparent Data Encryption (TDE).

Security

Permissions

Requires CONTROL DATABASE permission on the master database to create the database master key.

Requires CREATE CERTIFICATE permission on the master database to create the certificate that protects the DEK.

Requires CONTROL DATABASE permission on the encrypted database and VIEW DEFINITION permission on the certificate or asymmetric key that is used to encrypt the database encryption key.

[Top]

Creating a TDE protected database

To create a database protected by transparent data encryption, by using a database encryption key protected by a certificate in the master database.

To create a database protected by transparent data encryption

In Query Editor, enter the following Transact-SQL commands to create a database master key, and a certificate in the master database.

Security Note

Replace the asterisks with a password. Do not use a blank password. Use a strong password.
```
USE master ;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**************';
GO
CREATE CERTIFICATE TestSQLServerCert 
WITH SUBJECT = 'Certificate to protect TDE key'
GO
```

Security Note
Replace the asterisks with a password. Do not use a blank password. Use a strong password.

Create a backup of the server certificate in the master database. If the certificate that is used to protect the database encryption key is lost, you will not be able to access the data in a TDE protected database. The following example stores the backup of the certificate and the private key file, in the default data location for this instance of SQL Server (C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA).

Security Note
You must retain copies of both the certificate file and the private key file in order to recover the certificate. The password for the private key, does not have to be the same as the database master key password.

BACKUP CERTIFICATE TestSQLServerCert 
TO FILE = 'TestSQLServerCert'
WITH PRIVATE KEY 
(
    FILE = 'SQLPrivateKeyFile',
    ENCRYPTION BY PASSWORD = '**************'
);
GO

Create a database to be protected by TDE.
```
CREATE DATABASE CustRecords ;
GO
```
Switch to the new database. Create a database encryption key, that is protected by the server certificate in the master database. Then alter the new database to encrypt the database using TDE.
```
USE CustRecords;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TestSQLServerCert;
GO
ALTER DATABASE CustRecords
SET ENCRYPTION ON;
GO
```
Note

To performed this step by using SQL Server Management Studio, in Object Explorer, right-click the database, point to tasks, and then click Manage Database Encryption. Use the Manage Database Encryption dialog box to select a database encryption key and set database encryption on.

Create a simple table. Enter data into the table and then select from the table to test the database.

CREATE TABLE CustomerRecord
    (CustomerID int PRIMARY KEY,
    SecretData nvarchar(100) NOT NULL) ;
GO
INSERT CustomerRecord (CustomerID,SecretData)
VALUES (23997, 'Plain text data') ;
GO
SELECT CustomerID, SecretData FROM CustomerRecord ;
GO

[Top]