SQL Server中的加密

標籤：style   blog   http   color   io   os   使用   ar   for   

參考文獻：

細說SQL Server中的加密

Transparent Data Encryption (TDE)

Database Encryption Key (DEK) management

SQL Server中的加密簡介

　　在SQL Server2000和以前的版本，是不支援加密的。所有的加密操作都需要在程式中完成。這導致一個問題，資料庫中加密的資料僅僅是對某一特定程式有意義，而另外的程式如果沒有對應的解密演算法，則資料變得毫無意義。舉個例子來說，我以前寫過一個web應用程式(參考部落格：自訂SqlMembershipProvider方法)，想讓使用者資訊中的密碼欄位用密文儲存，我是在程式中將使用者的純文字密碼加密以後儲存到資料庫中的。

　　到了SQL Server2005，引入了列級加密。使得加密可以對特定列執行，這個過程涉及4對加密和解密的內建函數。

　　SQL Server 2008時代，則引入的了透明資料加密（TDE），所謂的透明資料加密，就是加密在資料庫中進行，但從程式的角度來看就好像沒有加密一樣，和列級加密不同的是，TDE加密的層級是整個資料庫。使用TDE加密的資料庫檔案或備份在另一個沒有認證的執行個體上是不能附加或恢複的。

對稱式加密與非對稱式加密

　　通常來說，加密可以分為兩大類，對稱(Symmetric)加密和非對稱(Asymmetric)加密。

　　對稱式加密是那些加密和解密使用同一個密鑰的密碼編譯演算法，就是加密金鑰=解密密鑰。對稱式加密通常來說會比較羸弱，因為使用資料時不僅僅需要傳輸資料本身，還是要通過某種方式傳輸密鑰，這很有可能使得密鑰在傳輸的過程中被竊取。

　　非對稱式加密是那些加密和解密使用不同密鑰的密碼編譯演算法，就是加密金鑰!=解密密鑰。用於加密的密鑰稱之為公開金鑰，用於解密的密鑰稱之為私密金鑰。因此安全性相比對稱式加密來說會大大提高。當然有一長必有一短，非對稱式加密的方式通常演算法會相比對稱金鑰來說複雜許多，因此會帶來效能上的損失。

　　因此，一種折中的辦法是使用對稱金鑰來加密資料，而使用非對稱金鑰來加密對稱金鑰。這樣既可以利用對稱金鑰的高效能，還可以利用非對稱金鑰的可靠性。

認證與密鑰的建立

每個資料庫有且只有一個資料庫主要金鑰(master key)，這是一個資料庫層級的密鑰。可以用於為建立資料庫層級的認證或非對稱金鑰提供加密。通過T-SQL語句建立，如代碼1所示。

--建立認證，對稱與非對稱金鑰需要master key--建立master keyCREATE MASTER KEY ENCRYPTION BY PASSWORD =‘[email protected]‘;--刪除master keydrop MASTER KEY ; 

 

如果要查看資料庫是否有master key，可以通過下面的T-SQL語句查詢

--在SSMS中沒有查看master key的地方，但是可以在sys.databases中查看到資料庫是否有master keyselect name,is_master_key_encrypted_by_server from sys.databases

 

我的查詢結果如所示：

從中我們可以看到master和TESTDB4這兩個資料庫含有master key，而且資料庫沒有。所以說master並不是只在master資料庫下面的，如果我們要在TESTDB4中建立認證與密鑰，那麼就必須為TESTDB4這個資料庫建立master key。

下面給出建立認證、對稱金鑰、非對稱金鑰的T-SQL代碼

use TESTDB3;--建立認證CREATE CERTIFICATE CertTest with SUBJECT = ‘Test Certificate‘GO--建立非對稱金鑰CREATE ASYMMETRIC KEY TestAsymmetric    WITH ALGORITHM = RSA_2048     ENCRYPTION BY PASSWORD = ‘[email protected]‘; GO--建立對稱金鑰CREATE SYMMETRIC KEY TestSymmetric    WITH ALGORITHM = AES_256    ENCRYPTION BY PASSWORD = ‘[email protected]‘;GO

 

建立完以後我們可以在SSMS中找到我們建立的認證與密鑰，如所示：

上面建立的認證、對稱金鑰、非對稱金鑰都是由固定的對稱金鑰[email protected]加密的。其實建立的認證、對稱金鑰、非對稱金鑰的時候，也可以使用認證、對稱金鑰、非對稱金鑰密碼編譯，T-SQL代碼如下：

--由認證加密對稱金鑰CREATE SYMMETRIC KEY SymmetricByCert    WITH ALGORITHM = AES_256    ENCRYPTION BY CERTIFICATE CertTest;GO--由對稱金鑰密碼編譯對稱金鑰OPEN SYMMETRIC KEY TestSymmetric    DECRYPTION BY PASSWORD=‘[email protected]‘--必須先open 了TestSymmetric以後才可以用TestSymmetric來加密CREATE SYMMETRIC KEY SymmetricBySy    WITH ALGORITHM = AES_256    ENCRYPTION BY SYMMETRIC KEY TestSymmetric;GO--由非對稱金鑰密碼編譯對稱金鑰CREATE SYMMETRIC KEY SymmetricByAsy    WITH ALGORITHM = AES_256    ENCRYPTION BY ASYMMETRIC KEY TestASymmetric;GO

 

建立完以後我們在Symmetric Key中可以看到我們查詢的結果：

 列級加密

　　在列級加密中我們將使用AdventureWorks2008R2這個範例資料庫，在Sales.CreditCard這張表中我們可以看到CardNumber列是明文的，如所示：

在實際生產環境中，如果使用者的信用卡號是明文的是非常不安全的， 我們在這項想將這個CardNumber使用列級加密。需要注意的是，進行加密或者解密的列必須是Varbinary類型。

首先我們建立不帶資料的CreditCard的表結構，並且CardNumber這個列是Varbinary類型，T-SQL代碼如下：

--建立不帶資料的表結構，有where 1<>1來控制SELECT CreditCardID, CardType,CardNumber_encrypt = CONVERT(varbinary(500), CardNumber), ExpMonth, ExpYear, ModifiedDateINTO CreditCard_Encrypt FROM AdventureWorks2008R2.Sales.CreditCardWHERE 1<>1

 

在建立表結構以後，我們將資料匯入到這張表中去：

--開啟之前建立的由認證加密的對稱金鑰OPEN SYMMETRIC KEY SymmetricByCert DECRYPTION BY CERTIFICATE CertTest;--利用這個祕密金鑰加密CardNumber這個資料列,插入建立的表中,使用了EncryptByKey這個函數insert CreditCard_encrypt (CardType,CardNumber_encrypt, ExpMonth, ExpYear, ModifiedDate) select top 10CardType,CardNumber_encrypt = EncryptByKey(KEY_GUID(‘SymmetricByCert‘), CardNumber),ExpMonth,ExpYear, ModifiedDatefrom AdventureWorks2008R2.Sales.CreditCard

 

查詢CreditCard_encrypt這個表，我們可以發現CardNumber列是密文了，如所示：

但是我們可以通過對稱金鑰來解密CardNumber這個列

--開啟之前建立的由認證加密的對稱金鑰OPEN SYMMETRIC KEY SymmetricByCert DECRYPTION BY CERTIFICATE CertTest;--查看CardNumber_encryptselect convert(nvarchar(25), DecryptByKey(CardNumber_encrypt)) from CreditCard_Encrypt;

 

使用這條語句查詢出來的結果就是CardNuber的明文了。 注意這裡需要OPEN SYMMETRIC KEY，如果不開啟這個對稱私密金鑰的話查詢結果是null。如果之前開啟過這個私密金鑰，那麼此處可以不用再次開啟。

透明資料加密

　　在SQL Server 2008中引入了透明資料加密(Transparent Data Encryption ，以下簡稱TDE)，之所以叫透明資料加密，是因為這種加密在使用資料庫的程式或使用者看來，就好像沒有加密一樣。TDE加密是資料庫層級的。資料的加密和解密是以頁為單位，由資料引擎執行的。在寫入時進行加密，在讀出時進行解密。用戶端程式完全不用做任何操作。（Encryption of the database file is performed at the page level. The pages in an encrypted database are encrypted before they are written to disk and decrypted when read into memory. TDE does not increase the size of the encrypted database.）

　　TDE的主要作用是防止Database Backup或資料檔案被偷了以後，偷Database Backup或檔案的人在沒有資料加密金鑰的情況下是無法恢複或附加資料庫的。我將一個encrypted database拷貝到另外的一台伺服器上，視圖attach這個database，但是報錯如下：

上述錯誤表明這台伺服器沒有相應的認證。這從另一個角度也給我們做了提醒，如果是一個encrypted database的話， 我不單要備份資料庫，還要對認證做備份，如果我們自身認證丟失的話，也會開不開資料庫。

　　TDE使用資料加密金鑰（DEK）進行加密。DEK是存在Master資料庫中由服務主要金鑰保護，由的保護層級如所示。

使用TDE的四個步驟為：

1. 建立一個master key(Create a master key)

   USE master;GO--在master資料庫中建立一個master keyCREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘[email protected]‘;go

    

2. 建立或者擷取一個由master key保護的認證(Create or obtain a certificate protected by the master key)

   --使用masterkey建立認證MyServerCertCREATE CERTIFICATE MyServerCert WITH SUBJECT = ‘My DEK Certificate‘;go

    

3. 使用認證建立一個database密鑰(Create a database encryption key and protect it by the certificate)

   1. 但是在建立完DATABASE ENCRYPTION KEY(DEK)以後，會報如下警告：

      USE TESTDB2;GO--建立資料庫加密key，使用MyServerCert這個認證加密CREATE DATABASE ENCRYPTION KEYWITH ALGORITHM = AES_128ENCRYPTION BY SERVER CERTIFICATE MyServerCert;GO

       

      
      

      Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate.If the certificate ever becomes unavailable or if you must restore or attach the database on another server,you must have backups of both the certificate and the private key or you will not be able to open the database.

      這是因為我們使用了認證來建立DEK，並且加密資料庫，因此我們建議為認證做一個備份，否則如果認證被破壞以後，我們自身都無法開啟資料庫。認證備份的方法可以參考：backup certificate，我的T-SQL所示：

      BACKUP CERTIFICATE MyServerCert TO FILE = ‘d:\storedcerts\MyServerCert‘; --cert的儲存地址 

       

      這裡需要注意的是我們的MyServerCert沒有由private key加密，而只是有master key加密。如果使用了private key的話還要為private key進行備份。我們如果對MyServerCert進行備份private key操作會報錯如下：

      No decryption password should be provided because the private key of this certificate is encrypted by a master key.

4. 將資料庫設定為TDE(Set the database to use encryption)

　　　　　　　最後我們需要做的就是講資料庫設定為加密，T-SQL語句如下所示：

ALTER DATABASE TESTDB2 SET ENCRYPTION ON;

　　　　　　　此時我們也可以右鍵TESTDB2->tasks->manage database encryption中查看加密設定，如所示：

 我們也可以通過T-SQL語句來查看有哪些資料庫進行了TDE加密，T-SQL語句如下：

/* The value 3 represents an encrypted state    on the database and transaction logs. */SELECT DBName=DB_NAME(database_id),encryption_state FROM sys.dm_database_encryption_keysWHERE encryption_state = 3;GO

 

查詢結果顯示tempdb也被透明加密了，原因我們可以在msdn上找到，我把原文摘出來：

Transparent Data Encryption and the tempdb System Database

The tempdb system database will be encrypted if any other database on the instance of SQL Server is encrypted by using TDE. This might have a performance effect for unencrypted databases on the same instance of SQL Server. For more information about the tempdb system database, see tempdb Database.

master key和certificate的備份與還原

--備份master keyBACKUP MASTER KEY TO FILE = ‘d:\storedkeys\masterkey‘ ENCRYPTION BY PASSWORD = ‘[email protected]‘go--備份認證BACKUP CERTIFICATE MyServerCert TO FILE = ‘d:\storedcerts\MyServerCert‘; --cert的儲存地址go--還原master keyuse masterRESTORE MASTER KEY     FROM FILE = ‘d:\storedkeys\masterkey‘     DECRYPTION BY PASSWORD = ‘[email protected]‘     ENCRYPTION BY PASSWORD = ‘[email protected]‘;GO--因為我這裡還留有原來的認證，所以會提示如下資訊：The old and new master keys are identical. No data re-encryption is required.--還原認證CREATE CERTIFICATE MyServerCert2    FROM FILE = ‘d:\storedcerts\MyServerCert‘ GO --因為認證已經存在，所以提示如下資訊：--A certificate with name ‘MyServerCert2‘ already exists or this certificate already has been added to the database.--需要注意的是認證並不是按照認證名來區分的。我原來的認證名叫做MyServerCert，此處建立的認證名為MyServerCert2，但是是來自MyServerCert的一個備份，還是報錯。

 

SQL Server中的加密