• Article

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

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

    Security noteSecurity 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

  2. 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 noteSecurity 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

  3. Create a database to be protected by TDE.

    CREATE DATABASE CustRecords ;
GO

  4. 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.

  5. 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]

Moving a TDE protected database

To move a TDE protected database to a new instance of SQL Server

  1. Detach the TDE protected database from the source server.

    USE master ;
GO
EXEC master.dbo.sp_detach_db @dbname = N'CustRecords';
GO

    Note

    To perform this step by using Management Studio, in Object Explorer, right-click the database, point to tasks, and then click Detach. Click OK.

  2. Using Window Explorer, move or copy the database files from the source server to the same location on the destination server.

    Note

    Normally the two database files (CustomerRecords.mdf and CustomerRecords_log.LDF) will be in the default location C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA. Your file names and locations might be different.

  3. Using Windows Explorer, move or copy the backup of the server certificate and the private key file from the source server to the same location on the destination server. In our example, these files are stored in the default data folder at C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA.

  4. Create a database master key on the destination instance of SQL Server. The password does not have to be the same as the source server.

    USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**************';
GO

  5. Recreate the server certificate by using the original server certificate backup file. The password must be the same as the password that was used when the backup was created.

    CREATE CERTIFICATE TestSQLServerCert 
FROM FILE = 'TestSQLServerCert'
WITH PRIVATE KEY 
(
    FILE = 'SQLPrivateKeyFile',
    DECRYPTION BY PASSWORD = '**************'
);
GO

  6. Attach the database that is being moved. The path of the database files must be the location where you have stored the database files.

    CREATE DATABASE [CustRecords] ON 
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\CustRecords.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\CustRecords_log.LDF' )
FOR ATTACH ;
GO

    Note

    To performed this step by using Management Studio, in Object Explorer, right-click the Databases, and then click Attach. Click Add to locate the CustRecords.mdf file, and then click OK.

  7. Test access to the database by selecting data from the table.

    USE CustRecords ;
GO
SELECT CustomerID, SecretData FROM CustomerRecord ;
GO

    [Top]

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

Backup the master database

  • Installing the certificate and attaching the database have changed information in the master database. You should create a new backup of the master database.

[Top]