Sqlserver關於TDE透明資料加密的使用總結

TDE：Transparent Data Encryption透明資料加密

master key XX：SSMS圖形介面工具中見master-security-symmetric key或見sys.symmetric_keys
CERTIFICATE YY：SSMS圖形介面工具中見master-security-certificates或見sys.certificates


總結
1、每個資料庫中都可以建立master key和CERTIFICATE，一個資料中要建立CERTIFICATE，則這個資料庫中必須先存在master key。如果master庫有master key但是DBA庫沒有master key，這時在DBA庫中建立CERTIFICATE會報錯Please create a master key in the database or open the master key in the session before performing this operation.
2、一個資料庫是否建立了master key，可以進入這個資料庫下查詢sys.symmetric_keys,當然更好的方法是透過sys.databases的is_master_key_encrypted_by_server欄位來看，也就是說要查詢哪些資料庫建立了master key可以使用語句select name,is_master_key_encrypted_by_server from sys.databases
3、一個資料庫是否建立CERTIFICATE，只能進入這個資料庫下查詢sys.certificates才能看到
4、如果建立master key或證照certificate都使用密碼加密了，但是忘記了加密的密碼，可以使用alter master key和alter certificate改過來
5、某個資料庫是否已經啟用TDE加密，可以透過sys.dm_database_encryption_keys檢視的欄位encryption_state看到，此檢視是例項級別的全域性在任何資料庫下執行的結果都是一樣。對某個資料庫create database encryption key後在sys.dm_database_encryption_keys中可查到該資料庫了，但是sys.dm_database_encryption_keys的encryption_state欄位還是顯示1未加密，只有alter database databasename set encryption on後，sys.dm_database_encryption_keys的encryption_state欄位顯示為2正在加密或3已加密。
6、如果 SQL Server 例項中的任何其他資料庫是使用 TDE 加密的，則會加密 tempdb 系統資料庫。
7、TDE透明資料加密用到master key和證照 certificate，而master key就是SYMMETRIC KEY，因為master key和SYMMETRIC KEY都存放在sys.symmetric_keys中，當然TDE透明資料加密也可以使用ASYMMETRIC KEY，參見CREATE DATABASE ENCRYPTION KEY  WITH ALGORITHM = { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY }  ENCRYPTION BY SERVER { CERTIFICATE Encryptor_Name | ASYMMETRIC KEY Encryptor_Name }，所以SYMMETRIC KEY和ASYMMETRIC KEY和證照certificate都是為TDE透明資料加密服務的
8、TDE透明資料加密是使用對稱金鑰進行加密的一種特殊情況，TDE使用稱為資料庫加密金鑰的對稱金鑰加密整個資料庫。 資料庫加密金鑰受由資料庫主金鑰或儲存在EKM模組中的非對稱金鑰保護的其他金鑰或證照保護。TDE透明資料加密一般用到master key和證照CERTIFICATE即可完成，雖然對資料庫建立加密秘鑰也可以使用ASYMMETRIC KEY，但是很少用這種方式，衍生出來的SYMMETRIC KEY和ASYMMETRIC KEY可參考官方文件。TDE透明資料加密是針對整個資料庫層面的也就是針對資料和日誌檔案，比如影響整個資料庫的備份恢復、快照、刪除、離線、分離、轉換READ ONLY 狀態等，主要是DBA這種管理人員使用的。
9、涉及的系統檢視或系統表是sys.databases、sys.symmetric_keys、sys.certificates、sys.dm_database_encryption_keys



TDE涉及到的一些管理規範
1、in one instance，should  all dbs encryption use the same certificate  or each db have a seperate certificate
在一個例項中，是否只在master庫在建立一個證照，然後所有的資料庫共用這個證照？還是在master庫中為每個資料庫建立一個證照？
2、if each db have a seperate certificate， how do we name the certificate name，like instancename_dbname_cert?
如果在master庫中為每個資料庫建立一個證照，證照的命名規範?
3、if we backup certificate  ，should we need private key and encryption password？   
備份證照時，是否需要使用私有秘鑰和加密密碼對備份檔案加密？
4、if we need private key and encryption password， should the private key named as instancename_dbname_certificatename_privatekey, and every certificate backup file use the same encryption password ?
如果備份證照時，使用了私有秘鑰檔案和加密密碼對備份檔案加密，私有秘鑰檔案的命名規範？每個證照的備份是否使用相同的加密密碼



資料庫啟用TDE的大致步驟：
在master資料庫裡建立主秘鑰。
建立/使用受主密匙保護的證照。
使用證照對某個資料庫建立加密秘鑰。
對某個資料庫啟用加密。

1、先drop master key主秘鑰
drop master key
如果報錯，說明有certificate在使用它，需要先把certificate刪除再刪除master key
Cannot drop master key because certificate 'C_databaseXX' is encrypted by it.

2、建立master key主秘鑰
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'XX';
示例create master key encryption by password = 'TD_123456';
--備註：直接CREATE MASTER KEY會有報錯Database master keys without password are not supported in this version of SQL Server.

3、建立certificate證照，名稱一般為certdbname
create certificate certtificatename with subject ='XX';
示例create certificate certSSRSTEST with subject ='SSRSTEST database certificate data encription';
--備註：如果該資料庫中沒有master key就建立certificate證照會報錯Please create a master key in the database or open the master key in the session before performing this operation.

4、備份上面第3步建立certificate證照
BACKUP CERTIFICATE certtificatename TO FILE = 'XX'
[WITH PRIVATE KEY ( FILE = 'XXkey' ,
ENCRYPTION BY PASSWORD = 'XX' )];
示例
BACKUP CERTIFICATE certSSRSTEST TO FILE = '\\testdb1\mirror\certSSRSTEST'
WITH PRIVATE KEY ( FILE = '\\testdb1\mirror\certSSRSTESTkey' ,
ENCRYPTION BY PASSWORD = '654321_DT' );
--除非非常嚴厲的許可權管控，一般不需要對備份的證照再進行私鑰加密

5、對某個資料庫使用上面第3步的certificate進行加密，並啟用這個加密
CREATE DATABASE ENCRYPTION KEY  WITH ALGORITHM = { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY }  ENCRYPTION BY SERVER { CERTIFICATE Encryptor_Name | ASYMMETRIC KEY Encryptor_Name }  
alter database databasename set encryption on
示例
use SSRSTEST;
go
create database encryption key with algorithm = AES_128 encryption by server certificate certSSRSTEST
go
alter database SSRSTEST set encryption on
go
--備註1：對某個資料庫create database encryption key可以使用證照CERTIFICATE也可以使用非對稱秘鑰ASYMMETRIC KEY，但是隻能使用master庫中的證照XX，使用其他資料庫或當前資料庫的證照XX會報錯Cannot find the certificate 'XX', because it does not exist or you do not have permission。比如DBA資料庫也有master key也有證照DBA_cert,但是master庫並沒有證照DBA_cert,此時DBA資料庫建立加密秘鑰時如果指定DBA_cert會報錯Cannot find the certificate 'DBA_cert'
--備註2：對某個資料庫create database encryption key後在sys.dm_database_encryption_keys中可查到該資料庫了，但是sys.dm_database_encryption_keys的encryption_state欄位還是顯示1未加密，只有alter database databasename set encryption on後，sys.dm_database_encryption_keys的encryption_state欄位顯示為2正在加密或3已加密

異機恢復一個TDE備份的資料庫
1、備份TDE資料庫庫
backup database SSRSTEST to disk = '\\testdb1\mirror\SSRSTEST.bak'

2、異機恢復這個資料庫
2.1、異機建立master key，這個密碼可以隨便
create master key encryption by password = '999_TD999';

2.2、異機建立CERTIFICATE證照，這個密碼必須和源端備份CERTIFICATE時的密碼一致(即上面第4步)，否則會報錯
CREATE CERTIFICATE certClientData
FROM FILE='\\testdb1\mirror\certSSRSTEST'
WITH PRIVATE KEY(
FILE='\\testdb1\mirror\certSSRSTESTkey',
DECRYPTION BY PASSWORD='654321_DT')

2.3、
restore database SSRSTEST from disk = '\\testdb1\mirror\SSRSTEST.bak'



異機恢復這個資料庫，直接恢復，有報錯，說明需要在異機建立certificate證照
restore database SSRSTEST from disk = '\\testdb1\mirror\SSRSTEST.bak'
報錯Cannot find server certificate with thumbprint '0x1640C78B8E4C6DCFA2DB4D2E97E3B206F2672FAB'.

異機建立certificate證照，有報錯說明DECRYPTION BY PASSWORD必須等於上面第4步的ENCRYPTION BY PASSWORD = '654321_DT'
use master;
go
CREATE CERTIFICATE certClientData
FROM FILE='\\testdb1\mirror\certSSRSTEST'
WITH PRIVATE KEY(
FILE='\\testdb1\mirror\certSSRSTESTkey',
DECRYPTION BY PASSWORD='TD_123456')
go
報錯The private key password is invalid

異機建立certificate證照，正確密碼還有報錯，說明需要先在異機建立master key
use master;
go
CREATE CERTIFICATE certClientData
FROM FILE='\\testdb1\mirror\certSSRSTEST'
WITH PRIVATE KEY(
FILE='\\testdb1\mirror\certSSRSTESTkey',
DECRYPTION BY PASSWORD='654321_DT')
go
報錯Please create a master key in the database or open the master key in the session before performing this operation.


建立master key隨便設定密碼password = '999_TD999'，建立證照輸入正確密碼PASSWORD='654321_DT'，一切正常
use master;
create master key encryption by password = '999_TD999';
CREATE CERTIFICATE certClientData
FROM FILE='\\testdb1\mirror\certSSRSTEST'
WITH PRIVATE KEY(
FILE='\\testdb1\mirror\certSSRSTESTkey',
DECRYPTION BY PASSWORD='654321_DT')


取消資料庫加密的方法
1、
use master
select * from sys.certificates
--查出certificatename

2、
use master
SELECT db_name(database_id), encryption_state,   percent_complete, key_algorithm, key_length FROM sys.dm_database_encryption_keys WHERE db_name(database_id) not in('tempdb')
--查詢哪個資料使用了加密

3、
use master
alter database databasename set encryption off
--在master中取消某個databasename的加密功能

use databasename
DROP DATABASE ENCRYPTION KEY  
--如果不先把資料庫的加密功能取消，則無法刪除資料庫的加密，會出現這個報錯Cannot drop the database encryption key because it is currently in use. Database encryption needs to be turned off to be able to drop the database encryption key.
--一般到了這個步驟，再對這個資料庫備份，後面這個備份拿到其他例項恢復就不會出現這個報錯Cannot find server certificate with thumbprint 'XX'.

4、
use master
drop certificate certificatename
--刪除加密證照，如果不先把某個資料庫的加密刪除，會出現這個報錯The certificate 'certificatename' cannot be dropped because it is bound to one or more database encryption key.