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.

Transparent Data Encryption Architecture

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].

-- Utworzenie DMK
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'BJ7M5srL1WkSx0pit5Vd';
GO

-- Utworzenie DMK

USE master

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'BJ7M5srL1WkSx0pit5Vd';

Później dopiero certyfikat, którego klucz prywatny zostanie zaszyfrowany wcześniej stworzonym kluczem DMK.

--Certyfikat
USE master 
GO
CREATE CERTIFICATE cTDEdmk WITH SUBJECT = 'CERTIFICATE ENCRYPTION BY DMK'
GO

--Certyfikat

USE master

CREATE CERTIFICATE cTDEdmk WITH SUBJECT = 'CERTIFICATE ENCRYPTION BY DMK'

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 DEFINITIONdo 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 polecenia RECONFIGURE
uprawnienie ALTER ANY CREDENTIAL
uprawnienie ALTER ANY LOGIN
uprawnienie CREATE ASYMMETRIC KEY
uprawnienie CONTROL DATABASE do zaszyfrowania bazy danych
uprawnienie VIEW DEFINITIONdo 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.

--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

--https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-database-encryption-keys-transact-sql

USE master;

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 = Encrypted
percent_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.

--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;

--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;

SELECT * FROM sys.symmetric_keys;

sys.symmetric_key - Transparent Data Encryption

sys.asymmetric_key widok zwraca listę kluczy asymetrycznych.

--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

--Widok na klucze symetryczne

--https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-asymmetric-keys-transact-sql

USE master;

SELECT * FROM sys.asymmetric_keys

sys.asymmetric_key - Transparent Data Encryption

sys.certificates zwróci wszystkie dostępne certyfikaty. Kolumna pvt_key_encryption_type informuję jak został zaszyfrowany klucz prywatny.

--Widok na certyfikaty
SELECT * FROM sys.certificates;

1 2	--Widok na certyfikaty SELECT * FROM sys.certificates;

sys.certificates - Transparent Data Encryption

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.

SSMS - Transparent Data 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.

USE [TDE]
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE cert_TDE_dmk
GO

USE [TDE]

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE cert_TDE_dmk

Drugie polecenie rozpoczyna szyfrowanie bazy danych.

ALTER DATABASE [TDE] SET ENCRYPTION ON
GO

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.

Backup Certificate - Transparent Data Encryption

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:

--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

--Kopia zapasowa certyfikatu

--https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-certificate-transact-sql

USE master;

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');

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.

--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

--Połączenie do instancji źródłowej

:CONNECT SERWER-SQL

--wykonanie BACKUP CERTIFICATE

USE master;

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');

--wykonanie BACKUP DATABASE

BACKUP DATABASE TDE TO DISK = '\\serwer\\Backup\TDE_full.bak'

--Połaczenie do instancji docelowej

:CONNECT SERWER-DB

--utworzenie Database Master Key dla bazy master jeśli jeszcze nie istnieje

USE master;

IF NOT EXISTS

(

SELECT *

FROM sys.symmetric_keys

WHERE name = '##MS_DatabaseMasterKey##'

)

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ejij3432J((*@#doadssla;';

--wykonanie CREATE CERTYFICATE

USE master;

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');

--wykonanie RESTORE DATABASE

RESTORE FILELISTONLY FROM DISK = 'D:\MSSQL\Backup\TDE_full.bak'

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.

przed Transparent Data Encryption
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.

po Transparent Data Encryption

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.

Tags: Certificate SQL Security SQL Server TDE Transparent Data Encryption

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.

2 komentarze

Reply
Pawel

4 stycznia, 2019 at 2:34 pm

Dziękuję, bardzo dobrze opisane. Przydało się !!!
- Reply
  Mateusz Nadobnik
  
  4 stycznia, 2019 at 8:29 pm
  
  To cieszy a jeszcze bardziej, że wykorzystałeś to w praktyce. Optymalnego dnia, pozdrawiam.

Wszystko co warto wiedzieć o Transparent Data Encryption

Certyfikat lub klucz asymetryczny

Tworzenie certyfikatu

Kwestie związane z kluczem asymetrycznym

Niezbędne uprawnienia

Widoki systemowe związane z Transparent Database Encryption

Włączanie szyfrowania bazy danych

SQL Server Management Studio

T-SQL

Kopia zapasowa certyfikatu

Odzyskanie bazy danych na innej instancji SQL Server

Plik danych po zaszyfrowaniu (zaleta)

Ruch sieciowy a Transparent Data Encryption (wada)

Transparent Data Encryption a baza tymczasowa

Mateusz Nadobnik

2 komentarze

Pawel

Mateusz Nadobnik

LEAVE A REPLY Anuluj pisanie odpowiedzi

Wszystko co warto wiedzieć o Transparent Data Encryption

Darmowy ebook “Zacznij z PowerShell”

Certyfikat lub klucz asymetryczny

Tworzenie certyfikatu

Kwestie związane z kluczem asymetrycznym

Niezbędne uprawnienia

Widoki systemowe związane z Transparent Database Encryption

Włączanie szyfrowania bazy danych

SQL Server Management Studio

T-SQL

Kopia zapasowa certyfikatu

Odzyskanie bazy danych na innej instancji SQL Server

Plik danych po zaszyfrowaniu (zaleta)

Ruch sieciowy a Transparent Data Encryption (wada)

Transparent Data Encryption a baza tymczasowa

Mateusz Nadobnik

You may also like

SSIS and Google Sheet API v4

SHRINKFILE – budzi strach i sieje spustoszenie

TRIM, CONCAT_WS, TRANSLATE, STRING_AGG czyli nowe funkcję T-SQL w SQL Server 2017

Darmowy ebook “Zacznij z PowerShell”

2 komentarze

Pawel

Mateusz Nadobnik

LEAVE A REPLY Anuluj pisanie odpowiedzi