五餅二魚工作室

-- >> NOTE: 該範例必須在 SSMS 下使用 SQLCMD 模式執行. << --
:on error exit

:setvar MasterKeyPassword        "P@ssw0rd123"
:setvar CertificateName          "TaichungCertificate"
:setvar CertificateSubject       "TaichungCertificate"
:setvar EncryptionDatabase       "A004"

 
-- 1. 建立 Master Key
USE [master]
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '$(MasterKeyPassword)';
GO
 
-- 2. 建立 Server 憑證
CREATE CERTIFICATE  $(CertificateName) 
    WITH SUBJECT = '$(CertificateSubject)',     EXPIRY_DATE = '12/30/2100'
GO
 
-- 3. 在欲加密的資料庫下建立資料庫加密憑證
USE [$(EncryptionDatabase)]
GO
CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE $(CertificateName)
GO
 
-- 4. 設定資料庫加密
ALTER DATABASE $(EncryptionDatabase) 
    SET ENCRYPTION ON
GO

SELECT 
    db_name(database_id) DBName, EncryptionState =
    CASE encryption_state
        WHEN 0 THEN 'No database encryption key present, no encryption'
        WHEN 1 THEN 'Unencrypted'
        WHEN 2 THEN 'Encryption in progress'
        WHEN 3 THEN 'Encrypted'
        WHEN 4 THEN 'Key change in progress'
        WHEN 5 THEN 'Key change in progress'
        WHEN 6 THEN 'Protection change in progress/'
        ELSE 'Un-recognized encryption status.'
    END
FROM sys.dm_database_encryption_keys

-- 備份憑證，並指定憑證加密的 Private Key 和 密碼
BACKUP CERTIFICATE TaichungCertificate
    TO FILE = 'D:\TDE\SQLServerCertificateFile.CER'
    WITH PRIVATE KEY (FILE='D:\TDE\SQLServerCertificateFile.PVK',  ENCRYPTION BY PASSWORD='P@ssw0rd987')
GO

-- 在新電腦上也同樣先建立一個 Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd123';
GO
 
-- 從備份檔案中還原憑證
CREATE CERTIFICATE TaichungCertificate
    FROM FILE='D:\TDE\Temp\SQLServerCertificateFile.CER'
    WITH PRIVATE KEY (FILE = 'D:\TDE\Temp\SQLServerCertificateFile.PVK',  
    DECRYPTION BY PASSWORD='P@ssw0rd987')
GO