SQL2008透明加密腳本

最新推荐文章于 2025-09-06 09:33:29 发布

转载最新推荐文章于 2025-09-06 09:33:29 发布 · 37 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/ywkpl/archive/2011/04/19/2020566.html

文章标签：

#数据库

本文介紹了如何在SQL Server中實現數據庫的透明加密，包括創建和備份證書、加密密鑰，設置數據庫加密，以及備份與還原整個過程。

透明加密必須運行在企業版以上版本中，以下代碼中New為測試數據庫

備份數據庫及證書：

USE master;

Go
--创建主密钥
CREATE MASTER KEY
    ENCRYPTION BY
    PASSWORD = 'password';
--创建或获取由主密钥保护的证书
CREATE CERTIFICATE MyServerCert
  WITH SUBJECT = 'My DEK Certificate';

USE New;
GO
--创建数据库加密密钥并通过此证书保护该密钥
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
--将数据库设置为使用加密
ALTER DATABASE New
  SET ENCRYPTION ON;

USE master
GO
--备份Master Key
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
BACKUP MASTER KEY TO FILE = 'd:\exportedmasterkey'
    ENCRYPTION BY PASSWORD = 'password';
GO

--备份证书
BACKUP CERTIFICATE MyServerCert
  TO FILE = 'D:\MyServerCert.cer'
        WITH PRIVATE KEY (
        FILE = 'D:\MyServerCert.pvk',
        ENCRYPTION BY
        PASSWORD = 'password');

--备份数据库
Backup Database New
To Disk='d:\New1.bak';
GO
sp_helpdb New;

還原數據庫

GO
RESTORE MASTER KEY FROM FILE = 'd:\exportedmasterkey'
    DECRYPTION BY PASSWORD = 'password'
    ENCRYPTION BY PASSWORD = 'password';
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password';

CREATE CERTIFICATE MyServerCert
    FROM FILE = 'D:\MyServerCert.cer'
        WITH PRIVATE KEY
         (FILE = 'D:\MyServerCert.pvk',
        DECRYPTION  BY
        PASSWORD = 'password');
GO
/*

Restore database New
From Disk = 'D:\New1.bak'
用於查看邏輯數據名稱，下面的 Move 中用到
*/
Restore database New
From Disk = 'D:\New1.bak'
With File=1,
Move 'Data' to 'D:\NE.mdf',

Move 'Log' to 'D:\NE.ldf'

转载于:https://www.cnblogs.com/ywkpl/archive/2011/04/19/2020566.html