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
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).
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.
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.
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).
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.
Create a database to be protected by TDE.
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
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.
To move a TDE protected database to a new instance of SQL Server
Detach the TDE protected database from the source server.
To perform this step by using Management Studio, in Object Explorer, right-click the database, point to tasks, and then click Detach. Click OK.
Using Window Explorer, move or copy the database files from the source server to the same location on the destination server.
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.
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.
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.
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.
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
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.
Test access to the database by selecting data from the table.
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.