Mechanizm szyfrowania baz danych (TDE) w SQL Server 2008

Autor: Damian Widera

Opublikowano: 18 listopada 2008

Zawartość strony

	Konfiguracja i działanie mechanizmu TDE
	Zasada działania szyfrowania baz danych
	Przenoszenie zaszyfrowanych baz danych
	Wydajność
	Uwagi
	Podsumowanie

Informacje zawarte w bazach danych bardzo często są poufne i nie powinny zostać ujawnione osobom niepowołanym. W wielu krajach na świecie istnieją przepisy, które wymagają odpowiedniego zabezpieczania danych w taki sposób, aby nawet np. po kradzieży nie można ich było odczytać.

Implementacja szyfrowania w systemach bazodanowych zazwyczaj pociąga za sobą konieczność znaczących zmian w istniejących aplikacjach. Prowadzi to np. do zmian w schematach table i wiąże się także z widocznym spadkiem wydajności systemu. W poprzedniej wersji serwera SQL administrator czy programista miał możliwość użycia szyfrowania na poziomie kolumn. Wiązało się to z wprowadzeniem dodatkowej kolumny typu varbinary i wstawienie do tej kolumny zaszyfrowanej informacji. Po utworzeniu indeksu na takiej kolumnie można było przekonać się, że optymalizator z niego nie skorzysta i będzie preferował skanowanie całej tabeli.

Sytuacja zmieniła się w wraz z nadejściem najnowszej wersji serwera SQL, która w wersji Enterprise posiada mechanizm szyfrujący bazy danych (ang. Transparent Data Encryption, TDE) w sposób niewidoczny dla aplikacji klienckich.

Konfiguracja i działanie mechanizmu TDE

Mechanizm szyfrowania baz danych szyfruje wszystkie informacje, które zapisywane są na dysku twardym, czyli:

• Pliki bazy danych
• Plik dziennika transakcji
• Kopie zapasowe bazy danych
• Migawki bazy danych

Szyfrowana jest również baza tempdb, w której mogą istnieć obiekty zawierające dane pochodzące z zaszyfrowanej bazy danych. Inne systemowe bazy danych nie mogą obecnie zostać zaszyfrowane.

Dane, które są umieszczone w pamięci (w buforach) nie są szyfrowane, dlatego z punktu widzenia aplikacji klienckiej nie trzeba wprowadzać żadnych dodatkowych zmian po włączeniu mechanizmu TDE. Ma to pozytywny wpływ na wydajność serwera (w stosunku do znanych z wcześniejszych wersji systemu metod szyfrowania), ale z drugiej strony jest to potencjalny punkt niebezpieczny, ponieważ np. zrzut pamięci na dysk, który może być wynikiem awarii, spowoduje, że dane zostaną zapisane w postaci niezaszyfrowanej.

Do włączenia mechanizmu TDE potrzebne są takie same uprawnienia, jak przy tworzeniu klucza głównego bazy danych oraz certyfikatów w bazie master. Szyfrowanie bazy danych wiąże się bezpośrednio z istnieniem klucza szyfrowania bazy danych (ang. Database Encryption Key, DEK). Jest to nowy klucz dodany do hierarchii istniejących kluczy, który zostaje zapisany w bazie master, a którym zostanie zaszyfrowana wybrana baza danych. Klucz ten jest chroniony certyfikatem, którym ze względów bezpieczeństwa nie powinny być szyfrowane żadne inne dane. Nowością jest to, że certyfikat chroniący klucz DEK może być składowany na urządzeniu zewnętrznym (np. USB) czy karcie inteligentnej. Jest to więc dodatkowy element bezpieczeństwa, bowiem bez tego certyfikatu nie będzie można np. odtworzyć bazy danych na innym serwerze, co zostanie pokazane w dalszej części artykułu.

Dodatkowo, administrator musi posiadać uprawnienia CONTROL w bazie danych, która zostanie zaszyfrowana.

Aby włączyć szyfrowanie bazy danych AdventureWorks2008 wykonaj poniższe kroki:

     1. W bazie danych master utwórz klucz główny (o ile jeszcze taki nie istnieje):

USE master

GO

CREATE MASTER KEY ENCRYPTION BY

PASSWORD = '10_Plenumm_spo%%%ldzilNI_Z()E*&N^%U$#M';

     2. Utwórz certyfikat, który będzie chronił klucz DEK:

CREATE CERTIFICATE CERT_TDE

WITH SUBJECT = 'Certyfikat TDE';

     3. Wykonaj kopię zapasową certyfikatu i zachowaj ją w bezpiecznym miejscu, ponieważ jak już wspomniałem wcześniej, bez tego certyfikatu nie będzie możliwe odtwarzanie zaszyfrowanej bazy danych na innych serwerach:

BACKUP CERTIFICATE CERT_TDE 

    TO FILE = 'C:\TEMP\CERT_TDE.cer' 

    WITH PRIVATE KEY 

    (    FILE = 'C:\TEMP\CERT_TDE_PK.pvk' , 

        ENCRYPTION BY PASSWORD = '@LLA_ma_k)OTT_a' );

GO

Uwaga! Po wykonaniu te operacji powstaną dwa pliki, ponieważ jeden z nich zawiera klucz prywatny:

Rysunek 1: Kopia zapasowa certyfikatu CERT_TDE.

     4. Utwórz klucz szyfrujący bazę danych (DEK), który jest chroniony certyfikatem CERT_TDE:

use AdventureWorks2008

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE CERT_TDE

     5. Włącz szyfrowanie bazy danych wykonując następujące polecenie:

ALTER DATABASE AdventureWorks2008 SET ENCRYPTION ON

Proces szyfrowania bazy danych może być monitorowany przy pomocy dynamicznego widoku sys.dm_database_encryption_keys:

SELECT DB_NAME(database_id), encryption_state, key_algorithm, key_length 

FROM sys.dm_database_encryption_keys;

Rysunek 2 przedstawia zachowany stan po rozpoczęciu procesu szyfrowania bazy danych AdventureWorks2008. Kolumna encryption_state informuje o postępie w szyfrowaniu. Wartość 3 w tej kolumnie oznacza, że baza danych została zaszyfrowana, a 2 — szyfrowanie w toku.

Rysunek 2: Monitorowanie procesu szyfrowania bazy danych.

     6. Operacje opisane w punktach 4-5 można wykonać również przy pomocy konsoli SSMS, używając graficznego kreatora:

• W oknie eksploratora obiektów zaznacz bazę danych AdventureWorks2008 i z menu kontekstowego wybierz Tasks/Manage Database Encryption,
• Po wybraniu użytego do szyfrowania bazy algorytmu i wskazaniu certyfikatu zaznacz pole wyboru Set Database Encryption On i kliknij OK (rysunek 3).

Rysunek 3: Konfiguracja szyfrowania bazy danych przy pomocy kreatora.

Raz skonfigurowane szyfrowanie bazy danych można też włączać i wyłączać w oknie jej właściwości, na zakładce Options.

 Do początku strony

Zasada działania szyfrowania baz danych

Po włączeniu mechanizmu TDE baza danych zostaje oznaczona jako zaszyfrowana w widoku katalogowym sys.datbases. Serwer SQL 2008 uruchamia działający w tle, asynchroniczny proces, który skanuje i szyfruje wszystkie pliki bazy danych. Proces szyfrowania nie wpływa na dostępność danych - zakładana jest bowiem blokada typu S na aktualnie szyfrowane strony danych. Operacje DDL zakładające blokady typu UPDATE nie mają wpływu na proces szyfrowania bazy danych. Administrator ma do wyboru dwa algorytmy szyfrowania: potrójny DES lub AES z 128, 192 lub 256 bitowym kluczem. Ważne jest, że rozmiar bazy danych nie zmienia się po zaszyfrowaniu, ponieważ wektor inicjalizujący (ang. initialization vector, IV) oraz zaszyfrowany DEK są przechowywane w ramach istniejącego miejsca w pliku.

Proces szyfrowania bazy danych powoduje, że dziennik transakcji przesunie się do następnego aktywnego pliku VLF i od tego miejsca zostanie zaszyfrowany. Istniejące przed rozpoczęciem szyfrowania informacje w dzienniku transakcji pozostają niezaszyfrowane aż do ponownego nadpisania.

Po zakończeniu szyfrowania każdy wpis do dziennika transakcji lub zapis do pliku bazy danych będzie zaszyfrowany.

Mechanizm TDE szyfruje wszystkie kopie zapasowe wykonane po włączeniu mechanizmu TDE dla bazy danych. Z tego powodu należy zawsze mieć wszystkie kopie certyfikatów, które chroniły klucz bazy danych DEK. Nic nie stoi bowiem na przeszkodzie, aby ze względów bezpieczeństwa zmieniać certyfikaty po pewnym czasie. Bez certyfikatu, który w danym momencie chronił klucz DEK, nie można odtworzyć kopii zapasowej lub podłączyć plików bazy danych na innym serwerze.

Żeby sprawdzić w praktyce, czy dane rzeczywiście zostały zaszyfrowane, utworzymy i zaszyfrujemy testową bazę danych:

     1. Za pomocą polecenia CREATE DATABASE utwórz bazę danych:

IF EXISTS (

  SELECT * 

    FROM sys.databases 

   WHERE name = N'BazaNiezaszyfrowana'

)

  DROP DATABASE BazaNiezaszyfrowana

GO



CREATE DATABASE BazaNiezaszyfrowana

ON PRIMARY

    (NAME = BazaNiezaszyfrowana_Data,

      FILENAME = N'C:\Temp\BazaNiezaszyfrowana_Data.mdf')

    

LOG ON

    ( NAME = BazaNiezaszyfrowana_Log,

      FILENAME = N'C:\Temp\BazaNiezaszyfrowana_Log.ldf')

GO

      2. W bazie danych BazaNiezaszyfrowana utwórz tablicę, która będzie zawierała poufne informacje:

use BazaNiezaszyfrowana

GO

CREATE TABLE dbo.TajnaTablica

(

    Imie varchar(10)

    ,NrKarty varchar(20)

)

INSERT INTO TajnaTablica

VALUES ('Damian','1234-000'),('Jerzy','000999-9783'),('Piotr','00-222')

      3. Wykonaj kopię niezaszyfrowanej bazy danych:

BACKUP DATABASE BazaNiezaszyfrowana  TO

DISK = 'C:\Temp\BazaNiezaszyfrowana.bak'

GO

      4. Utwórz bazę danych BazaZaszyfrowana z wykonanej w poprzednim punkcie kopii zapasowej:

RESTORE DATABASE BazaZaszyfrowana 

FROM DISK = N'C:\Temp\BazaNiezaszyfrowana.bak' WITH

MOVE 'BazaNiezaszyfrowana_Data' TO N'C:\Temp\BazaZaszyfrowana_Data.mdf',

MOVE 'BazaNiezaszyfrowana_Log' TO N'C:\Temp\BazaZaszyfrowana_Log.ldf',

REPLACE;

      5. W bazie danych master utwórz klucz główny (o ile jeszcze taki nie istnieje):

USE master

GO

CREATE MASTER KEY ENCRYPTION BY

PASSWORD = '10_Plenumm_spo%%%ldzilNI_Z()E*&N^%U$#M';

       6. Utwórz certyfikat, który będzie chronił klucz DEK:

CREATE CERTIFICATE NOWY_CERT_TDE

WITH SUBJECT = 'Nowy_Certyfikat TDE';

       7. Wykonaj kopię zapasową certyfikatu i zachowaj ją w bezpiecznym miejscu. Bez tego certyfikatu nie będzie możliwe odtwarzanie zaszyfrowanej bazy danych na innych serwerach:

BACKUP CERTIFICATE NOWY_CERT_TDE

    TO FILE = 'C:\TEMP\ NOWY_CERT_TDE.cer' 

    WITH PRIVATE KEY 

    (    FILE = 'C:\TEMP\ NOWY_CERT_TDE _PK.pvk' , 

        ENCRYPTION BY PASSWORD = 'silne_H@$$$lo');

GO

      8. Utwórz klucz szyfrujący bazę danych BazaZaszyfrowana, który jest chroniony certyfikatem NOWY_CERT_TDE:

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE NOWY_CERT_TDE

      9. Włącz szyfrowanie bazy danych wykonując następujące polecenie:

use BazaZaszyfrowana

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE NOWY_CERT_TDE;

GO

ALTER DATABASE BazaZaszyfrowana SET ENCRYPTION ON;

GO

      10. Zaszyfrowanie tak małej bazy danych będzie błyskawiczne. Wykonaj kopię zapasową zaszyfrowanej bazy danych:

BACKUP DATABASE BazaZaszyfrowana  TO

DISK = 'C:\Temp\BazaZaszyfrowana.bak'

GO

      11. Zatrzymaj serwer SQL klikając prawym przyciskiem myszy na jego nazwie w oknie eksploratora obiektów konsoli SSMS i wybierając z menu kontekstowego zadanie Stop.

      12. Otwórz w programie WordPad plik C:\Temp\BazaNiezaszyfrowana_Data.mdf (plik niezaszyfrowanej bazy danych) i poszukaj w nim imion Damian, Jerzy i Piotr. Zostaną one odnalezione razem z innymi poufnymi informacjami zawartymi w tablicy TajnaTablica. W analogiczny sposób przeszukaj plik zaszyfrowanej bazy danych — C:\Temp\ BazaNiezaszyfrowana_Data.mdf. Tym razem informacje nie zostaną wyświetlone.

      13. Otwórz w tym samym programie pliki kopii zapasowych niezaszyfrowanej i zaszyfrowanej bazy (pliki C:\Temp\ BazaNiezaszyfrowana.bak oraz C:\Temp\ BazaZaszyfrowana.bak) i sprawdź, czy można w nich odnaleźć informacje. W pierwszym przypadku zostaną one znalezione, w drugim — nie.14.Uruchom serwer SQL klikając prawym przyciskiem myszy na jego nazwie w oknie eksploratora obiektów konsoli SSMS i wybierając z menu kontekstowego zadanie Start.  

 Do początku strony

Przenoszenie zaszyfrowanych baz danych

Baza danych może być przeniesiona i odtworzona na innym serwerze na dwa sposoby:

• poprzez skopiowanie plików bazodanowych i podłączenie ich na docelowym serwerze,
• odtwarzając kopię zapasową bazy.

W obu przypadkach potrzebny będzie klucz DEK, którego odszyfrowanie będzie niemożliwe bez certyfikatu znajdującego się w bazie master oryginalnego serwera SQL.

Odtworzenie zaszyfrowanej bazy danych na innym serwerze, na którym nie ma certyfikatu chroniącego klucz DEK zakończy się niepowodzeniem:

RESTORE DATABASE BazaZaszyfrowana

FROM DISK = 'C:\Temp\ BazaZaszyfrowana.bak'

WITH MOVE 'BazaNiezaszyfrowana_Data' TO N'C:\Temp\BazaZaszyfrowana2_Data.mdf',

MOVE 'BazaNiezaszyfrowana_Log' TO N'C:\Temp\BazaZaszyfrowana2_Log.ldf',

REPLACE;

-------------

Msg 33111, Level 16, State 3, Line 1

Cannot find server certificate with thumbprint ‘0xD726F9B73062701FAA832C1D3A200A4B7EB0E82A’.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

Aby operacja odtworzenia zaszyfrowanej bazy danych na innym serwerze zakończyła się sukcesem należy odtworzyć certyfikat chroniący klucz DEK:

CREATE CERTIFICATE NOWY_CERT_TDE

FROM FILE = 'C:\TEMP\ NOWY_CERT_TDE.cer' 

WITH PRIVATE KEY (

FILE = 'C:\TEMP\ NOWY_CERT_TDE _PK.pvk' , 

DECRYPTION BY PASSWORD = 'silne_H@$$$lo');

Teraz możliwe już będzie podłączenie bazy danych lub odtworzenie jej z kopi zapasowej.

 Do początku strony

Wydajność

Zastosowanie mechanizmu TDE ma ważną zaletę - jeżeli odczytywane lub modyfikowane dane znajdują się już w pamięci, dostęp do nich będzie równie szybki, jak do niezaszyfrowanych danych. W większości serwerów to system I/O jest „wąskim gardłem”, jednak szyfrowanie baz danych powoduje niewielkie (z reguły nie przekraczające 3 - 5%) spadki wydajności serwera mierzone czasem jego odpowiedzi, ponieważ za szyfrowanie odpowiada procesor. Szyfrowanie znacznie zwiększa obciążenie procesora podczas odczytywania i zapisywania danych.

 Do początku strony

Uwagi

Na zakończenie artykułu warto zapoznać się z kilkoma generalnymi uwagami, które wiążą się z mechanizmem szyfrowania baz danych:

1. Włączenie mechanizmu TDE powoduje zaszyfrowanie wszystkich danych za wyjątkiem danych typu FILESTREAMzawartego w bazie danych,
2. Zaszyfrowanie bazy danych spowoduje, że kompresja kopii zapasowej (inna nowa funkcjonalność dostępna w edycji Enterprise serwera SQL 2008) praktycznie nie zmieni rozmiaru tej kopii,
3. Szyfrowanie baz danych nie jest zgodne ze znaną z poprzedniej wersji serwera SQL szybką inicjalizacją plików, czyli funkcją pozwalającą powiększać pliki bazy danych bez wcześniejszego wyzerowania miejsca na dysku,
4. Szyfrowanie bazy danych, która jest w trybie READ ONLY powoduje konieczność zmiany tej opcji na czas wykonywania szyfrowania, a następnie przełączenie jej ponownie w tryb tylko do odczytu,
5. Po włączeniu szyfrowania bazy danych, kiedy proces szyfrowania jest w toku, baza danych nie może zostać odłączona.

 Do początku strony

Podsumowanie

Szyfrowanie baz danych jest mechanizmem, który na pewno będzie cieszył się a zainteresowaniem wśród administratorów serwera SQL. Nie jest to mechanizm, który wyklucza użycie innych metod zabezpieczania serwera SQL - korzystając z mechanizmu TDE można również używać innych metod szyfrowania, np. znanego z poprzedniej wersji serwera SQL szyfrowania danych w kolumnach. Inne dostępne metody oferowane przez system operacyjny, takie jak BitLocker czy EFS, pozwalają chronić dane w sytuacji, w której mechanizm TDE nie zadziała, np. podczas hibernacji systemu czy zrzutu pamięci. BitLocker czy EFS mogą być użyte do zabezpieczania systemowych baz danych (master, model, resource, msdb), których obecnie nie można zaszyfrować. Należy przy tym pamiętać także o ograniczeniach, jakie posiadają wspomniany BitLocker czy EFS.

---

Damian Widera, Project Manager & Team Lead (MCT, MCITP – DBA, MCSD.NET) Od 8 lat zajmuje się projektowaniem, tworzeniem i wdrażaniem aplikacji wykorzystujących platformę .NET, SQL Server oraz Oracle. Obecnie pracuje jako project manager dla LGBS Polska. Pracował także jako trener, programista, administrator baz danych, twórca domumentacji oraz analityk biznesowy. Aktywnie współpracuje z polskim oddziałem Microsoft publikując atykuły, webcasty oraz porady z zakresu SQL Server na stronach TechNet. Jest współautorem książki „Serwer SQL 2008. Administracja i programowanie”. Speaker na wielu konferencjach, m.in. Microsoft Heroes Happen Here, C2C, European PASS Conference, Microsoft Technology Summit, Energy Launch, TechED. Od 2004 r. posiada certyfikaty firmy Microsoft: MCT, MCITP–DBA oraz MCSD.NET. Jest współtwórcą oraz liderem jednej z najwiekszych grup pasjonatów SQL Server w Polsce – Śląskiej Regionalnej Grupy Microsoft (PLSSUG Katowice). Od listopada 2008 jest prezesem Polish SQL Server Users Group (PLSSUG) w Polsce. W styczniu 2009 nagrodzony tytułem MVP w kategorii SQL Server.

Do początku strony

---