Transparent Data Encryption (od SQL Server 2008) chroni całą bazę danych składowaną na dysku. Odszyfrowanie następuję podczas wczytania danych do pamięci i pozostają tam w takiej postaci. Szyfrowanie jest kompletnie transparentne dla aplikacji i nie wymaga implementacji żadnych zmian w kodzie. Dzięki tej funkcjonalności można szyfrować pliki bazy danych, pliki dzienników i pliki kopii zapasowych bez zmiany istniejących aplikacji. TDE używa do szyfrowanie algorytmów AES i 3DES.
Certyfikat lub klucz asymetryczny
Transparent Data Encryption do szyfrowania bazy danych wymaga certyfikatu bądź klucza asymetrycznego. Należy pamiętać, że nie jest możliwe odczytanie bazy danych na instancji, na której nie istnieje klucz lub certyfikat, którym została zaszyfrowana.
Tworzenie certyfikatu
Decydując się na certyfikat, jako element szyfrujący, w pierwszym kroku należy przygotować klucz główny w bazie master tzw. Database Master Key a następnie certyfikat. Nie zachowując odpowiedniej kolejności otrzymamy poniższy błąd.
Msg 15581, Level 16, State 1, Line 54
Please create a master key in the database or open the master key in the session before performing this operation.
Dlatego w pierwszym kroku należy stworzyć Database Master Key [DMK].
1 2 3 4 5 |
-- Utworzenie DMK USE master GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'BJ7M5srL1WkSx0pit5Vd'; GO |
Później dopiero certyfikat, którego klucz prywatny zostanie zaszyfrowany wcześniej stworzonym kluczem DMK.
1 2 3 4 5 |
--Certyfikat USE master GO CREATE CERTIFICATE cTDEdmk WITH SUBJECT = 'CERTIFICATE ENCRYPTION BY DMK' GO |
Certyfikat, który zostanie zaszyfrowany hasłem nie jest obsługiwany przez Transparent Data Encryption.
Kwestie związane z kluczem asymetrycznym
Zastosowanie klucza asymetrycznego do zaszyfrowania bazy danych za pomocą Transparent Data Encryption wymaga stosowania Extensible Key Management. Uściślając tylko klucz asymetryczny chroniony przez moduł EKM może zostać użyty do zaszyfrowania bazy danych.
Extensible Key Menagment (EKM) umożliwia zarządzanie kluczami do szyfrowania poza samym SQL Server. Tradycyjnie, wszystkie klucze dla kryptografii symetrycznych i asymetrycznych wykonywanych przez SQL Server znajdują się w samej bazy danych, jednak EKM pozwala na tworzenie kluczy, ich przechowywanie, szyfrowanie i deszyfrowanie poza samą bazą danych dzięki użyciu urządzeń typu Hardware security module HSM
SQL Server nie dostarcza dostawcy EKM. Każdy dostawca EKM może mieć różne procedury instalowania, konfigurowania i autoryzowania użytkowników.
Niezbędne uprawnienia
Niezbędne uprawnienia do wdrożenia szyfrowania bazy danych Transparent Data Encryption w przypadku zastosowanie certyfikatu:
- uprawnienie
CONTROL DATABASE
do bazy master do utworzenia Database Master Key - uprawnienie
CREATE CERTIFICATE
do bazy master do utworzenia certyfikatu szyfrującego Database Encrytpion Key - uprawnienie
CONTROL DATABASE
do szyfrowanej bazy danych - uprawnienie
VIEW DEFINITION
do certyfikatu który jest używany do szyfrowania DEK
Niezbędne uprawnienia do wdrożenia TDE przy zastosowaniu klucza asymetrycznego przechowywanego poprzez EKM:
- uprawnienie poziomu bazy danych
ALTER SETTINGS
do zmiany konfiguracji oraz do uruchomienia poleceniaRECONFIGURE
- uprawnienie
ALTER ANY CREDENTIAL
- uprawnienie
ALTER ANY LOGIN
- uprawnienie
CREATE ASYMMETRIC KEY
- uprawnienie
CONTROL DATABASE
do zaszyfrowania bazy danych - uprawnienie
VIEW DEFINITION
do klucza asymetrycznego który jest używany do szyfrowania DEK
Widoki systemowe związane z Transparent Database Encryption
sys.dm_database_encryption_keys
widok z listą wszystkich zaszyfrowanych baz danych w danej instancji.
1 2 3 4 |
--https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-database-encryption-keys-transact-sql USE master; GO SELECT * FROM sys.dm_database_encryption_keys |
Kolumny na które warto zwrócić uwagę:
encryption_state
= wskazuję czy baza danych jest zaszyfrowana, oczekiwaną wartością jest 3 = Encryptedpercent_complete
= kolumna wskazuje procent zakończenia szyfrowania bazy danych, oczekiwaną wartością jest 0 które oznacza zakończone szyfrowanie
sys.symmetric_key
widok zwraca liste kluczy symetrycznych. Przydatny do zweryfikowania posiadania Service Master Key oraz Database Master Key.
1 2 3 4 5 |
--Widok zwracający wszystkie klucze symetryczne --https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-symmetric-keys-transact-sql USE master; GO SELECT * FROM sys.symmetric_keys; |
sys.asymmetric_key
widok zwraca listę kluczy asymetrycznych.
1 2 3 4 5 |
--Widok na klucze symetryczne --https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-asymmetric-keys-transact-sql USE master; GO SELECT * FROM sys.asymmetric_keys |
sys.certificates
zwróci wszystkie dostępne certyfikaty. Kolumna pvt_key_encryption_type
informuję jak został zaszyfrowany klucz prywatny.
1 2 |
--Widok na certyfikaty SELECT * FROM sys.certificates; |
Włączanie szyfrowania bazy danych
SQL Server Management Studio
Konfiguracja Transparent Database Encryption możliwe jest poprzez SSMS. Aby wykonać w menu kontekstowym dla bazy danych (po kliknięciu prawym) wybieramy Tasks i następnie Manage Database Encryption.
Manage Database Encryption jest podzielony na dwie sekcje. Database Encryption Key, w którym definiujemy opcję dotyczące klucza szyfrującego baza danych oraz Database Encryption Options gdzie zaznaczenie opcji Set Databeses Encryption On jest równoznaczne z rozpoczęciem szyfrowania bazy danych.
T-SQL
Podobnie jak w SQL Server Management Studio w T-SQL włączanie można podzielić na dwa etapy. Pierwszym poleceniem tworzy się Database Encryption Key, którego klucz prywatny zostanie zaszyfrowany za pomocą zdefiniowanego certyfikatu lub klucza asymetrycznego.
1 2 3 4 5 6 |
USE [TDE] GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE cert_TDE_dmk GO |
Drugie polecenie rozpoczyna szyfrowanie bazy danych.
1 2 |
ALTER DATABASE [TDE] SET ENCRYPTION ON GO |
Kopia zapasowa certyfikatu
Po procesie szyfrowania nie jest możliwe odczytanie bazy danych na instancji, na której nie istnieje certyfikat, dlatego tak ważne jest wykonanie kopii zapasowej certyfikatu wraz z kluczem prywatnym.
Konfigurując Transparent Data Encryption zarówno poprzez SSMS jak i T-SQL, SQL Server ostrzega nas o braku wykonania kopii zapasowej certyfikatu. Przy czym stosując polecenia T-SQL ostrzeżenie jest dłuższe i przedstawia pełniejsze zagrożenia wynikające z braku kopii zapasowej.
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.
Backup należy wykonać poleceniem T-SQL:
1 2 3 4 5 6 7 8 |
--Kopia zapasowa certyfikatu --https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-certificate-transact-sql USE master; GO BACKUP CERTIFICATE cert_TDE_dmk TO FILE = 'D:\SQLBackup\cert_TDE_dmk.crt' WITH PRIVATE KEY ( FILE = 'D:\SQLBackup\cert_TDE_dmk.pvk', ENCRYPTION BY PASSWORD = 'daoiJKH(9823))dj2k2o'); GO |
Odzyskanie bazy danych na innej instancji SQL Server
Restore zaszyfrowanej bazy danych na innej instancji SQL Server bez wcześniejszego odzyskania certyfikatu zakończy się błędem.
Msg 33111, Level 16, State 3, Line 215
Cannot find server certificate with thumbprint 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'.
Msg 3013, Level 16, State 1, Line 215
RESTORE DATABASE is terminating abnormally.
Kroki które należy wykonać w celu odzyskania bazy na innej instancji:
- wykonanie
BACKUP CERTIFICATE
- wykonanie
BACKUP DATABASE
- utworzenie Database Master Key dla bazy master jeśli jeszcze nie istnieje
- wykonanie
CREATE CERTIFICATE
- wykonanie
RESTORE DATABASE
Skrypt należy wykonać w trybie SQLCMD mode.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
--Połączenie do instancji źródłowej :CONNECT SERWER-SQL --wykonanie BACKUP CERTIFICATE USE master; GO BACKUP CERTIFICATE cert_TDE_dmk TO FILE = '\\serwer\\Backup\cert_TDE_dmk.crt' WITH PRIVATE KEY ( FILE = '\\serwer\cert_TDE_dmk.pvk', ENCRYPTION BY PASSWORD = 'daoiJKH(9823))dj2k2o'); GO --wykonanie BACKUP DATABASE BACKUP DATABASE TDE TO DISK = '\\serwer\\Backup\TDE_full.bak' GO --Połaczenie do instancji docelowej :CONNECT SERWER-DB --utworzenie Database Master Key dla bazy master jeśli jeszcze nie istnieje USE master; GO IF NOT EXISTS ( SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##' ) CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ejij3432J((*@#doadssla;'; GO --wykonanie CREATE CERTYFICATE USE master; GO CREATE CERTIFICATE cert_TDE_dmk FROM FILE = 'D:\MSSQL\Backup\cert_TDE_dmk.crt' WITH PRIVATE KEY ( FILE = 'D:\MSSQL\Backup\cert_TDE_dmk.pvk', DECRYPTION BY PASSWORD = 'daoiJKH(9823))dj2k2o'); GO --wykonanie RESTORE DATABASE RESTORE FILELISTONLY FROM DISK = 'D:\MSSQL\Backup\TDE_full.bak' GO |
Plik danych po zaszyfrowaniu (zaleta)
Z plików danych bazy, która nie jest zaszyfrowana istnieje możliwość odczytania pewnych informacji. Przykładowy plik mdf otwarty w notepad.exe, w którym widać jawny tekst.
Ta sam plik danych, po zaszyfrowaniu przy użyciu Transparent Data Encryption, z którego nie jest możliwe odczytanie żadnych wartości zapisanych w bazie danych.
Ruch sieciowy a Transparent Data Encryption (wada)
Należy pamiętać, że mimo stosowanie Transparent Data Encryption dane transferowane są jawnym tekstem. Transparent Data Encryption zapewnia bezpieczeństwa tylko dla bazy danych w spoczynku i aby zapewnić pełną ochronę danych należałby zastosować np. Connection Encryption.
Poniżej zrzut ekranu Wireshark z przykładową odpowiedzią od SQL Server do klienta.
Transparent Data Encryption a baza tymczasowa
Zaszyfrowanie jakiejkolwiek bazy danych w ramach instancji SQL Server spowoduję również zaszyfrowanie bazy tymczasowej. Nawet jeśli zaszyfrowana baza zostanie usunięta to baza tymczasowa zostanie w takim stanie.

Mateusz Nadobnik
Z pasją poświęcam czas na zdobywanie wiedzy w zakresie szeroko rozumianej Data Platform. Zachwycony językiem skryptowym Windows PowerShell. Swoją wiedzę, doświadczenia i spostrzeżenia opisuję na blogu.
read more
2 komentarze
Pawel
4 stycznia, 2019 at 2:34 pm
Dziękuję, bardzo dobrze opisane. Przydało się !!!
Mateusz Nadobnik
4 stycznia, 2019 at 8:29 pm
To cieszy a jeszcze bardziej, że wykorzystałeś to w praktyce. Optymalnego dnia, pozdrawiam.