資料庫經過加密後，若遺失加密憑證或金鑰，將無法開啟資料庫，請熟悉操作後再進行，並妥善保存憑證與金鑰

• TDE / Transparent Data Encryption / 透明資料加密

查詢資料庫加密情形

--在master資料庫，查詢主要金鑰(Master Key)
SELECT	[name],principal_id,symmetric_key_id,
		key_length,key_algorithm,algorithm_desc,[key_guid]
FROM	[master].sys.symmetric_keys

--在master資料庫，查詢有主要金鑰的資料庫
SELECT	is_master_key_encrypted_by_server,is_encrypted,
		[name],database_id,[compatibility_level],collation_name
FROM	[master].sys.databases
-------------------------------------------------
--查詢憑證
SELECT	[name],pvt_key_encryption_type_desc,
		[subject],thumbprint,key_length,certificate_id,
		pvt_key_encryption_type,[expiry_date]
FROM [master].sys.certificates
-------------------------------------------------
--查詢哪些資料庫使用SQL Server加密憑證、加密狀態
SELECT	db_name(database_id) AS [name],encryption_state,
		encryption_state_desc,percent_complete,
		key_algorithm,key_length,encryptor_type,
		encryptor_thumbprint
FROM	[master].sys.dm_database_encryption_keys

加密資料庫(TDE)

--在master資料庫，建立主要金鑰
USE master
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Master*r@4(F&d'
GO
-------------------------------------------------
--切換到master資料庫
--在master資料庫，建立憑證
USE master
CREATE CERTIFICATE FlyStudioX_Certificate
	WITH SUBJECT = '用來保護TDE金鑰的憑證'  
GO
-------------------------------------------------
--切換到需加密的資料庫
--建立資料庫加密金鑰，並使用憑證保護金鑰
USE AdventureWorksLT2019
CREATE DATABASE ENCRYPTION KEY  
	WITH ALGORITHM = AES_256
	ENCRYPTION BY SERVER CERTIFICATE FlyStudioX_Certificate;  
GO

--啟用資料庫加密(TDE)
ALTER DATABASE AdventureWorksLT2019  
	SET ENCRYPTION ON;
GO  
-------------------------------------------------

• 加密過程中，系統會提醒備份加密憑證

--警告: 用來加密資料庫加密金鑰的憑證尚未備份。
--您應該立即備份此憑證和與此憑證關聯的私密金鑰。
--萬一憑證無法使用時，或者您必須還原資料庫或將它附加到另一部伺服器時，
--就必須有憑證和私密金鑰的備份，否則就無法開啟資料庫。

查詢資料庫主要金鑰

-------------------------------------------------
--在master資料庫，查詢主要金鑰(Master Key)
SELECT	[name],principal_id,symmetric_key_id,
		key_length,key_algorithm,algorithm_desc,[key_guid]
FROM	[master].sys.symmetric_keys

--在master資料庫，查詢有主要金鑰的資料庫
SELECT	is_master_key_encrypted_by_server,is_encrypted,
		[name],database_id,[compatibility_level],collation_name
FROM	[master].sys.databases

建立資料庫主要金鑰

-------------------------------------------------
--在master資料庫，建立主要金鑰
USE master
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Master*r@4(F&d'
GO

查詢資料庫憑證

-------------------------------------------------
--查詢憑證
SELECT	[name],pvt_key_encryption_type_desc,
		[subject],thumbprint,key_length,certificate_id,
		pvt_key_encryption_type,[expiry_date]
FROM [master].sys.certificates
-------------------------------------------------
--查詢哪些資料庫使用SQL Server加密憑證、加密狀態
SELECT	db_name(database_id) AS [name],encryption_state,
		encryption_state_desc,percent_complete,
		key_algorithm,key_length,encryptor_type,
		encryptor_thumbprint
FROM	[master].sys.dm_database_encryption_keys
--FlyStudioX_Certificate	0x1255CB161842A5BDEA1B3B17B82656D9E07E940B

匯出資料庫憑證與金鑰

-------------------------------------------------
--在master資料庫，匯出憑證 & 金鑰 (private key)，
--預設路徑為執行個體的DATA資料夾
-- C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\
USE master
BACKUP CERTIFICATE FlyStudioX_Certificate   
	TO FILE = 'C:\Backup\FlyStudioX_Certificate'  
	WITH PRIVATE KEY   
	(  
		FILE = 'C:\Backup\FlyStudioX_PrivateKey',  
		ENCRYPTION BY PASSWORD = 'Private*r@4(F&d'  
	);  
GO

匯入資料庫憑證與金鑰

-------------------------------------------------
--在master資料庫，匯入憑證 & 金鑰 (private key)
--預設路徑為執行個體的DATA資料夾
-- C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\
USE master;
CREATE CERTIFICATE FlyStudioX_Certificate   
	FROM FILE = 'C:\Backup\FlyStudioX_Certificate'  
	WITH PRIVATE KEY   
	(  
		FILE = 'C:\Backup\FlyStudioX_PrivateKey',  
		DECRYPTION BY PASSWORD = 'Private*r@4(F&d'  
	);  
GO

移除資料庫加密

-------------------------------------------------
--切換到master資料庫
--停用資料庫加密(TDE)
USE master;
ALTER DATABASE AdventureWorksLT2019 
	SET ENCRYPTION OFF;
GO
-------------------------------------------------
--切換到需加密的資料庫
--卸除資料庫加密金鑰
USE AdventureWorksLT2019
DROP DATABASE ENCRYPTION KEY
GO
-------------------------------------------------
--切換到master資料庫
--卸除資料庫加密憑證
USE master;
DROP CERTIFICATE FlyStudioX_Certificate;
GO
-------------------------------------------------
--切換到master資料庫
--卸除資料庫主要金鑰
USE master;  
DROP MASTER KEY;  
GO

卸除資料庫加密憑證

如需卸除憑證，務必記得先匯出備份

-------------------------------------------------
--在master資料庫，卸除資料庫加密憑證
USE master;
DROP CERTIFICATE FlyStudioX_Certificate;
GO

卸除資料庫主要金鑰

如需卸除主要金鑰，請確保所有資料庫均已移除加密

USE master;  
DROP MASTER KEY;  
GO