将受 TDE 保护的数据库移到其他 SQL ServerMove a TDE Protected Database to Another SQL Server

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

本主题介绍如何使用 SQL Server Management StudioSQL Server Management StudioTransact-SQLTransact-SQL 通过透明数据加密 (TDE) 来保护数据库,然后再将数据库移动到 SQL ServerSQL Server 的其他实例。This topic describes how to protect a database by using transparent data encryption (TDE), and then move the database to another instance of SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. TDE 可对数据和日志文件执行实时 I/O 加密和解密。TDE performs real-time I/O encryption and decryption of the data and log files. 这种加密使用数据库加密密钥 (DEK),该密钥存储在数据库引导记录中以供恢复时使用。The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. DEK 是使用存储在服务器的 master 数据库中的证书保护的对称密钥,或者是由 EKM 模块保护的非对称密钥。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.

限制和局限Limitations and Restrictions

  • 在移动 TDE 保护的数据库时,您还必须移动用于打开 DEK 的证书或非对称密钥。When moving a TDE protected database, you must also move the certificate or asymmetric key that is used to open the DEK. 该证书或非对称密钥必须安装在目标服务器的 master 数据库中,以便 SQL ServerSQL Server 可以访问数据库文件。The certificate or asymmetric key must be installed in the master database of the destination server, so that SQL ServerSQL Server can access the database files. 有关详细信息,请参阅透明数据加密 (TDE)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 ServerSQL Server 默认情况下,将此处创建的文件存储在 C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA 中。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.

权限Permissions

  • 要求针对 master 数据库的 CONTROL DATABASE 权限以便创建数据库主密钥。Requires CONTROL DATABASE permission on the master database to create the database master key.

  • 要求针对 master 数据库的 CREATE CERTIFICATE 权限以便创建保护 DEK 的证书。Requires CREATE CERTIFICATE permission on the master database to create the certificate that protects the DEK.

  • 需要拥有已加密数据库的 CONTROL DATABASE 权限和用于加密数据库加密密钥的证书或非对称密钥的 VIEW DEFINITION 权限。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

以下过程演示了必须使用 SQL Server Management Studio 和 Transact-SQL 创建由 TDE 保护的数据库。The following procedures show you have to create a database protected by TDE using SQL Server Management Studio and by using Transact-SQL.

使用 SQL Server Management StudioUsing SQL Server Management Studio

  1. master 数据库中创建数据库主密钥和证书。Create a database master key and certificate in the master database. 有关详细信息,请参阅下面的 使用 Transact-SQLFor more information, see Using Transact-SQL below.

  2. master 数据库中创建服务器证书的备份。Create a backup of the server certificate in the master database. 有关详细信息,请参阅下面的 “使用 Transact-SQL”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. 有关创建新的数据库的详细信息,请参阅 Create a DatabaseFor more information on creating a new database, see Create a 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 为默认算法。AES128 is the default algorithm. 此字段不能为空。This field cannot be blank. 有关加密算法的详细信息,请参阅 Choose an Encryption AlgorithmFor 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. 如果没有服务器证书的 VIEW DEFINITION 权限,此列表将为空。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. 有关证书的详细信息,请参阅 SQL Server Certificates and Asymmetric KeysFor 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. 只有受 EKM 模块保护的非对称密钥才可以使用 TDE 对数据库进行加密。Only an asymmetric key protected by an EKM module can encrypt a database using TDE.

    将数据库加密设置为 ONSet Database Encryption On
    将数据库更改为打开(选中)或关闭(取消选中)TDE。Alters the database to turn on (checked) or turn off (unchecked) TDE.

  8. 完成后,单击 “确定”When finished, click OK.

使用 Transact-SQLUsing Transact-SQL

  1. “对象资源管理器” 中,连接到 数据库引擎Database Engine的实例。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 MASTER KEY ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1';  
    GO  
    CREATE CERTIFICATE TestSQLServerCert   
    WITH SUBJECT = 'Certificate to protect TDE key'  
    GO  
    -- Create a backup of the server certificate in the master database.  
    -- 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'  
    WITH PRIVATE KEY   
    (  
        FILE = 'SQLPrivateKeyFile',  
        ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1'  
    );  
    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.   
    -- 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  
    

有关详细信息,请参阅:For more information, see:

移动由透明数据加密保护的数据库To move a database protected by transparent data encryption

以下过程演示了必须使用 SQL Server Management Studio 和 Transact-SQL 移动由 TDE 保护的数据库。The following procedures show you have to move a database protected by TDE using SQL Server Management Studio and by using Transact-SQL.

使用 SQL Server Management StudioUsing SQL Server Management Studio

  1. 在对象资源管理器中,右键单击在前面已进行加密的数据库,指向“任务”,然后选择“分离…” 。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 NameDatabase Name
    显示要分离的数据库的名称。Displays the name of the database to be detached.

    删除连接Drop Connections
    断开与指定数据库的连接。Disconnect connections to the specified database.

    备注

    不能分离连接为活动状态的数据库。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. 只有从 SQL Server 2005 (9.x)SQL Server 2005 (9.x)升级数据库时,才会显示此选项。This option appears only when you are upgrading a database from SQL Server 2005 (9.x)SQL Server 2005 (9.x).

    “状态”Status
    显示以下状态之一:“就绪”或“未就绪” 。Displays one of the following states: Ready or Not ready.

    消息Message
    “消息” 列可显示关于数据库的如下信息:The Message column may display information about the database, as follows:

    • 当数据库进行了复制操作,则 “状态”“未就绪”“消息” 列将显示 “已复制数据库”When a database is involved with replication, the Status is Not ready and the Message column displays Database replicated.

    • 如果数据库有一个或多个活动连接,则“状态”为“未就绪”,“消息”列显示“<number_of_active_connections> 个活动连接”,例如 :“1 个活动连接” 。When a database has one or more active connections, the Status is Not ready and the Message column displays <number_of_active_connections>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.

    若要获取有关消息的详细信息,请单击相应的超链接文本打开活动监视器。To obtain more information about a message, click the hyperlinked text to open Activity Monitor.

  2. 单击“确定” 。Click OK.

  3. 使用 Window 资源管理器,将数据库文件从源服务器移动到或复制到目标服务器上的相同位置。Using Windows Explorer, move or copy the database files from the source server to the same location on the destination server.

  4. 使用 Window 资源管理器,将服务器证书和私钥文件的备份从源服务器移动到或复制到目标服务器上的相同位置。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.

  5. SQL ServerSQL Server的目标实例上创建数据库主密钥。Create a database master key on the destination instance of SQL ServerSQL Server. 有关详细信息,请参阅下面的 使用 Transact-SQLFor more information, see Using Transact-SQL below.

  6. 通过使用原始服务器证书备份文件重新创建服务器证书。Recreate the server certificate by using the original server certificate backup file. 有关详细信息,请参阅下面的 使用 Transact-SQLFor more information, see Using Transact-SQL below.

  7. SQL Server Management StudioSQL Server Management Studio 的对象资源管理器中,右键单击“数据库”文件夹,然后选择“分离…” 。In Object Explorer in SQL Server Management StudioSQL Server Management Studio, right-click the Databases folder and select Attach....

  8. “附加数据库” 对话框中的 “要附加的数据库” 下,单击 “添加”In the Attach Databases dialog box, under Databases to attach, click Add.

  9. 在“定位数据库文件 - server_name”对话框中,选择要附加到新服务器的数据库文件,然后单击“确定” 。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 文件位置MDF File Location
    显示选定 MDF 文件的路径和文件名。Displays the path and file name of the selected MDF file.

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

    图标Icon 状态文本Status text 描述Description
    (无图标)(No icon) (无文本)(No text) 此对象的附加操作尚未启动或者可能挂起。Attach operation has not been started or may be pending for this object. 这是打开该对话框时的默认值。This is the default when the dialog is opened.
    绿色的右向三角形Green, right-pointing triangle 正在进行In progress 已启动附加操作,但是该操作未完成。Attach operation has been started but it is not complete.
    绿色的选中标记Green check mark 成功Success 已成功附加该对象。The object has been attached successfully.
    包含白色十字形的红色圆圈Red circle containing a white cross 错误Error 附加操作遇到错误,未成功完成。Attach operation encountered an error and did not complete successfully.
    包含左、右两个黑色象限和上、下两个白色象限的圆圈Circle containing two black quadrants (on left and right) and two white quadrants (on top and bottom) 已停止Stopped 由于用户停止了附加操作,该操作未成功完成。Attach operation was not completed successfully because the user stopped the operation.
    包含一个指向逆时针方向的曲线箭头的圆圈Circle containing a curved arrow pointing counter-clockwise 已回滚Rolled Back 附加操作已成功,但已对其进行回滚,因为在附加其他对象的过程中出现了错误。Attach operation was successful but it has been rolled back due to an error during attachment of another object.

    消息Message
    显示空消息或“找不到文件”超链接。Displays either a blank message or a "File not found" hyperlink.

    “添加”Add
    查找必需的主数据库文件。Find the necessary main database files. 当用户选择 .mdf 文件时,就会在 “要附加的数据库” 网格的相应字段中自动填充合适的信息。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_name> ”数据库详细信息" <database_name> " database details
    显示要附加的文件的名称。Displays the names of the files to be attached. 若要验证或更改文件的路径名,请单击“浏览”按钮 (…) 。To verify or change the pathname of a file, click the Browse button (...).

    备注

    如果文件不存在,则 “消息” 列显示“找不到”。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. 如果找不到 .ndf 数据文件,则需要更新网格中该文件的路径使其指向正确的位置。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.

使用 Transact-SQLUsing Transact-SQL

  1. “对象资源管理器” 中,连接到 数据库引擎Database Engine的实例。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.

    -- Detach the TDE protected database from the source server.   
    USE master ;  
    GO  
    EXEC master.dbo.sp_detach_db @dbname = N'CustRecords';  
    GO  
    -- Move or copy the database files from the source server to the same location on the destination server.   
    -- 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 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.  
    
    CREATE CERTIFICATE TestSQLServerCert   
    FROM FILE = 'TestSQLServerCert'  
    WITH PRIVATE KEY   
    (  
        FILE = 'SQLPrivateKeyFile',  
        DECRYPTION BY PASSWORD = '*rt@40(FL&dasl1'  
    );  
    GO  
    -- 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\MSSQL13.MSSQLSERVER\MSSQL\DATA\CustRecords.mdf' ),  
    ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\CustRecords_log.LDF' )  
    FOR ATTACH ;  
    GO  
    

有关详细信息,请参阅:For more information, see:

另请参阅See Also

数据库分离和附加 (SQL Server) Database Detach and Attach (SQL Server)
借助 Azure SQL 数据库实现透明数据加密Transparent Data Encryption with Azure SQL Database