Empire Data Systems

Move TDE Protected Database to SQLServer

Daniel AG by Daniel A G

This topic describes how to to protect a database by using transparent data encryption (TDE), and then move the database to another instance of SQL Server by using SQL Server Management Studio or Transact-SQL. 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.

Before You Begin

Limitations and Restrictions

  • 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 Transparent Data Encryption (TDE).

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

  • SQL Server stores the files created here in C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA by default. Your file names and locations might be different.

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.

To create a database protected by transparent data encryption


Using SQL Server Management Studio

  1. Create a database master key and certificate in the master database. For more information, see Using Transact-SQL below.

  2. Create a backup of the server certificate in the master database. For more information, see Using Transact-SQL below.

  3. In Object Explorer, right-click the Databases folder and select New Database.

  4. In the New Database dialog box, in the Database name box, enter the name of the new database.

  5. In the Owner box, enter the name of the new database's owner. Alternately, click the ellipsis (…) to open the Select Database Owner dialog box. For more information on creating a new database.

  6. In Object Explorer, click the plus sign to expand the Databases folder.

  7. Right-click the database you created, point to Tasks, and select Manage Database Encryption.

    The following options are available on the Manage Database Encryption dialog box.

    Encryption Algorithm

    Displays or sets the algorithm to use for database encryption. AES128 is the default algorithm. This field cannot be blank. For more information on encryption algorithms, see Choose an Encryption Algorithm.


    Use server certificate

    Sets the encryption to be secured by a certificate. Select one from the list. If you do not have the VIEW DEFINITION permission on server certificates, this list will be empty. If a certificate method of encryption is selected, this value cannot be empty. For more information about certificates, see SQL Server Certificates and Asymmetric Keys.


    Use server asymmetric key

    Sets the encryption to be secured by an asymmetric key. Only available asymmetric keys are displayed. Only an asymmetric key protected by an EKM module can encrypt a database using TDE.


    Set Database Encryption On

    Alters the database to turn on (checked) or turn off (unchecked) TDE.

  8. When finished, click OK.

    Using Transact-SQL

  1. In Object Explorer, connect to an instance of Database Engine.

  2. On the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click Execute

-- Create a database master key and a certificate in the master database.  


USE master ;  

GO  

CREATE CERTIFICATE TestSQLServerCert   


CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1';  

GO  

-- Create a backup of the server certificate in the master database.  


WITH SUBJECT = 'Certificate to protect TDE key'  

GO  

-- The following code 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\MSSQL13.MSSQLSERVER\MSSQL\DATA).  

BACKUP CERTIFICATE TestSQLServerCert   
TO FILE = 'TestSQLServerCert'  

-- Switch to the new database.  

WITH PRIVATE KEY   
(  

    FILE = 'SQLPrivateKeyFile',  


    ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1'  

);  
GO  

CREATE DATABASE CustRecords ;  

-- Create a database to be protected by TDE.  
GO  

CREATE DATABASE ENCRYPTION KEY  


-- Create a database encryption key, that is protected by the server certificate in the master database.   

-- Alter the new database to encrypt the database using TDE.  
USE CustRecords;  
GO  

GO  

WITH ALGORITHM = AES_128  
ENCRYPTION BY SERVER CERTIFICATE TestSQLServerCert;  
GO  

ALTER DATABASE CustRecords  


SET ENCRYPTION ON;

    To move a database

    Using SQL Server Management Studio

      In Object Explorer, right-click the database you encrypted above, point to Tasks and select Detach….The following options are available in the Detach Database dialog box.

      Databases to detach

      Lists the databases to detach.


      Database Name

      Displays the name of the database to be detached.


      Drop Connections

      Disconnect connections to the specified database.

      Note

      You cannot detach a database with active connections.

      Update Statistics

      By default, the detach operation retains any out-of-date optimization statistics when detaching the database; to update the existing optimization statistics, click this check box.


      Keep Full-Text Catalogs

      By default, the detach operation keeps any full-text catalogs that are associated with the database. To remove them, clear the Keep Full-Text Catalogs check box. This option appears only when you are upgrading a database from SQL Server 2005.


      Status

      Displays one of the following states: Ready or Not ready.


      Message

      The Message column may display information about the database, as follows:

      To obtain more information about a message, click the hyperlinked text to open Activity Monitor.

      • When a database is involved with replication, the Status is Not ready and the Message column displays Database replicated.

      • When a database has one or more active connections, the Status is Not ready and the Message column displays Active connection(s) â€” for example: 1 Active connection(s). Before you can detach the database, you need to disconnect any active connections by selecting Drop Connections.

      • Click OK.

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

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

      • Create a database master key on the destination instance of SQL Server. For more information, see Using Transact-SQL below.

      • Recreate the server certificate by using the original server certificate backup file. For more information, see Using Transact-SQL below.

      • In Object Explorer in SQL Server Management Studio, right-click the Databases folder and select Attach….

      • In the Attach Databases dialog box, under Databases to attach, click Add.

      • In the Locate Database Files â€“server_name dialog box, select the database file to attach to the new server and click OK.

      The following options are available in the Attach Databases dialog box.

      Databases to attach

      Displays information about the selected databases.


      <no column header>

      Displays an icon indicating the status of the attach operation. The possible icons are described in the Status description, below).


      MDF File Location

      Displays the path and file name of the selected MDF file.


      Database Name

      Displays the name of the database.


      Attach As

      Optionally, specifies a different name for the database to attach as.


      Owner

      Provides a drop-down list of possible database owners from which you can optionally select a different owner.


      Status

      Displays the status of the database according to the following table.



      1. Message

        Displays either a blank message or a "File not found" hyperlink.


        Add

        Find the necessary main database files. When the user selects an .mdf file, applicable information is automatically filled in the respective fields of the Databases to attach grid.


        Remove

        Removes the selected file from the Databases to attach grid.


        " " database details

        Displays the names of the files to be attached. To verify or change the pathname of a file, click the Browse button (…).


        Note

        If a file does not exist, the Message column displays "Not found." If a log file is not found, it exists in another directory or has been deleted. You need to either update the file path in the database details grid to point to the correct location or remove the log file from the grid. If an .ndf data file is not found, you need to update its path in the grid to point to the correct location.

        Original File Name

        Displays the name of the attached file belonging to the database.


        File Type

        Indicates the type of file, Data or Log.


        Current File Path

        Displays the path to the selected database file. The path can be edited manually.


        Message

        Displays either a blank message or a "File not found" hyperlink.

      Using Transact-SQL

      1. In Object Explorer, connect to an instance of Database Engine.

      2. On the Standard bar, click New Query.

      3. Copy and paste the following example into the query window and click Execute.

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

        USE master ;  
        
GO  

        -- Move or copy the database files from the source server to the same location on the destination server.   
        

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

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

        -- Create a database master key on the destination instance of SQL Server.   
        
USE master;  
GO  

        CREATE CERTIFICATE TestSQLServerCert   
        
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1';  
GO  

        -- 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.  
        
FROM FILE = 'TestSQLServerCert'  
WITH PRIVATE KEY   
(  

        CREATE DATABASE [CustRecords] ON   
        

            FILE = 'SQLPrivateKeyFile',  
        

            DECRYPTION BY PASSWORD = '*rt@40(FL&dasl1'  
        
);  
GO  

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

        GO  
        

        ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\CustRecords.mdf' ),  
        

        ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\CustRecords_log.LDF' )  
        

        FOR ATTACH ;

      Statistics Says It All

      16

      Years of Experience

      3000

      Gratified Students

      100

      Training Batches

      9600

      Training Hours

      Subscribe

      Please subscribe our technical blog to get recent updates.

      Empire Data Systems

      Social Links