SQL Server 加密/解密

資料當然需要保護，SQL Server有不同加密方式

1. 憑證加密
2. 非對證式金鑰
3. 對稱式
4. 片語加密

這邊記錄憑證加密的指令
最重要一句是：

憑證很重要，千萬要備份!

CREATE TABLE 加密表
(
  編號 INT,
  資料 NVARCHAR(5),
  加密資料 VARBINARY(MAX)
)

--1. 啟用該資料庫的 master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD='aAzZ111'

--2. 建立憑證
CREATE CERTIFICATE 加密憑憑
WITH SUBJECT = '用來加密的憑證',EXPIRY_DATE = '2014-12-31'
--BACKUP CERTIFICATE 加密憑憑，務必要記得備份 

--以加密方式Insert進資料表
INSERT INTO 加密表 VALUES(1,N'憑憑憑憑',ENCRYPTBYCERT(CERT_ID('加密憑憑'),N'憑憑憑憑'))
INSERT INTO 加密表 VALUES(2,N'證證證證',ENCRYPTBYCERT(CERT_ID('加密憑憑'),N'證證證證'))

SELECT * FROM 加密表


--以解密方式查出資料表
SELECT 編號,資料
 , CONVERT(NCHAR,DECRYPTBYCERT(CERT_ID('加密憑憑'),加密資料)) AS 解密後資料
FROM 加密表

接著整個資料庫有「被複製」的風險
自SQL Server2008企業版後
就有所謂「透明式加密」方式防止外洩
資料庫離開指定的伺服器，就會失效
不論是用備份方式帶走，或整個磁碟抽走
只要沒有當初設立的憑證，那只要掛在別的SQL Server就讀取不了
當然真的有需要移機房時，就要記得憑證要跟著

--透明式加密(企業版才有)
--只能用在非系統資料庫
USE master
--1. 啟用 master 資料庫的 master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD='aAzZ111'

--2. 在 master 資料庫中建立憑證
CREATE CERTIFICATE 透明憑憑 
WITH SUBJECT='用來透明式加密用的憑証',EXPIRY_DATE='2014-12-31'


USE 新新
--3. 設定欲使用透明式加密的資料庫的加密方式
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE 透明憑憑
GO

--4. 啟用資料庫透明式加密
ALTER DATABASE 新新 SET ENCRYPTION ON