Akademia SQL - Część 2: Resource Governor     Akademia SQL     Akademia SQL - Część 4: Kompresja kopii zapasowej

Akademia SQL - Część 3: Transparent Data Encryption Udostępnij na: Facebook

Autor: Damian Widera

Opublikowano: 18 września 2008

Zawartość strony
 Transparent Data Encryption – informacje podstawowe   Transparent Data Encryption – informacje podstawowe
 Transparent Data Encryption – laboratorium   Transparent Data Encryption – laboratorium
 Transparent Data Encryption – referencje   Transparent Data Encryption – referencje

Transparent Data Encryption – informacje podstawowe

SQL Server 2008 pozwala zaszyfrować bazy danych, pliki danych, pliki dzienników transakcji oraz pliki kopii zapasowych bez potrzeby wprowadzania zmian w aplikacji. Oznacza to, iż operacja ta jest przezroczysta dla aplikacji klienckich i wykonywana wewnętrznie przez silnik SQL Server. Silnik baz danych dba również o zapewnienie wspomnianej powyżej ‘przezroczystości’, to znaczy dane dla aplikacji klienckich, w tym również dla SQL Server Management Studio, wysyłane są odszyfrowane, natomiast fizycznie (jako pliki) pozostają zaszyfrowane. Systemowa baza danych tempdb również zostaje zaszyfrowana – automatycznie, a jest to podyktowane faktem, iż tam właśnie system oraz np. procedury składowane użytkownika mogą tymczasowo przechowywać dane z zaszyfrowanej wcześniej bazy danych.

Bardzo ważną zaletą zaszyfrowanej bazy danych jest, iż nie może ona zostać podłączona na innej instancji serwera baz danych, o ile na drugiej instancji nie ma klucza szyfrującego, którym to baza danych została zabezpieczona. To samo stwierdzenie jest prawdziwe także dla plików kopi zapasowej zaszyfrowanej bazy danych. Z tego względu najistotniejszym z punktu widzenia bezpieczeństwa systemu staje się odpowiednie przechowywanie klucza szyfrującego, który może być np. składowany na urządzeniu zewnętrznym (podłączonym przez port USB).

Na zakończenie wprowadzenia do TDE słów kilka o technicznej stronie szyfrowania baz danych. Po włączeniu mechanizmu szyfrowania w widoku katalogowym sys.databases szyfrowana baza danych zostaje oznaczona jako zaszyfrowana (is_encrypted = 1). SQL Server uruchamia w tle wątki (tzw. encrytption scan), które skanują wszystkie pliki bazy danych oraz szyfrują je (lub deszyfrują w przypadku, gdy mechanizm TDE zostaje wyłączony). W sytuacji, w której następuje uruchomienie wyzwalacza DDL na bazie danych zakładana jest blokada typu U (update). Proces skanowania tabel (encryption scan) działa asynchronicznie do wyzwalaczy DDL i zakłada blokadę typu S (shared). Inne operacje, które nie wywołują konfliktu z opisanymi powyżej blokadami mogą działać normalnie, ale już takie operacje jak modyfikacja struktury plików czy próba odłączenia bazy danych – zostaną zablokowane. Po zakończeniu procesu skanowania tabel wszystkie pliki bazy danych oraz dziennika transakcji, które zostaną zapisane na dysk, są zaszyfrowane. SQL Server 2008 daje możliwość zaszyfrowania bazy danych przy użyciu jednego z czterech dostępnych algorytmów – AES z kluczem 128, 192 lub 256 lub przy pomocy algorytmu Triple DES. Zaszyfrowane pliki bazy danych po zapisaniu na dysk mają ten sam rozmiar co pliki bez szyfrowania, ponieważ wektor inicjalizacyjny (IV) oraz zaszyfrowany DEK są przechowywane w ramach istniejącego miejsca (na stronach). Jako informację dodatkową należy przyjąć, iż w sposób szczególny jest potraktowany dziennik transakcji w momencie włączenia szyfrowania, a mianowicie zerowana jest pozostała część aktualnego wirtualnego pliku dziennika transakcji i rozpoczęty zostaje nowy plik wirtualny. Takie podejście gwarantuje, iż żadna operacja przechowana w dzienniku transakcji po włączeniu szyfrowania bazy danych nie zostania zapisana jako zwykły tekst.

Uwaga: w trakcie ćwiczenia należy zwrócić szczególną uwagę na instancję serwera, w kontekście której wykonywane są skrypty T-SQL (do wykonania laboratorium potrzebne będą dwie instancje serwera baz danych), oraz na ścieżki do plików *.mdf oraz *.ldf używane podczas przyłączania baz do innej instancji.

 Do początku strony Do początku strony

Transparent Data Encryption – laboratorium

Celem pierwszego ćwiczenia jest zaszyfrowanie bazy danych oraz sprawdzenie, w którym widoku katalogowym ta informacja jest zapisana. Drugim zadaniem będzie próba podłączenia zaszyfrowanej bazy danych na instancji, na której brakuje klucza szyfrującego.

Zadanie 1

  1.  Uruchom SQL Server Management Studio (SSMS) i połącz się z serwerem EVALUATION używając uwierzytelnienia WINDOWS.

  2.  Przejdź do bazy danych TDE_Test i z menu kontekstowego wybierz opcję Properties. W oknie własności bazy danych przejdź do zakładki opcji (Options) i sprawdź, czy szyfrowanie tej bazy danych jest wyłączone:

  3.  Otwórz skrypt Encrypt DB.sql znajdujący się w katalogu C:\SQLAdmin\TDE. Zaznacz i uruchom sekcję opisaną jako Step 1. Wykonanie tej części skryptu spowoduje utworzenie nowego klucza, tzw. SystemMaster Key za pomocą którego można będzie tworzyć inne klucze, certyfikaty, loginy a także szyfrować bazę danych. Certyfikat MyServerCert jest utworzony w drugiej komendzie w ramach omawianego kroku. Utworzenie klucza czy certyfikatu nie oznacza, iż system zaszyfrował bazę danych czy wykonał jakieś inne akcje.

USE master;

GO



CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘UseStrongPassword1!’; 

GO



CREATE CERTIFICATE MyServerCert WITH SUBJECT = ‘My DEK Certificate for Sensitive Data’ 

GO

  4.  Teraz należy wykonać trzy zapytania znajdujące się w otwartym skrypcie i oznaczone jako Step 2. Pozwolą one potwierdzić, iż dane zawarte w bazie danych TDE_Test wyświetlają się poprawnie. Pierwsze z zapytań wyświetla informacje o klientach, których konta zawarte są w określonym przedziale; drugie zapytanie pozwala odnaleźć zduplikowane numery ubezpieczenia (SSN) a w trzecim wyświetlane są informacje o hasłach, które nie zawierają litery ‘@’ i uznane są przez to jako tzw. hasła słabe.

  5.  W następnym kroku zaszyfrujemy bazę danych TDE_Test. W tym celu należy ją zaznaczyć i z menu kontekstowego wybrać opcję Tasks / Manage Database Encryption jak pokazano na rysunku poniżej.

  6.  W nowo otwartym oknie mamy możliwość odpowiedniego do naszych potrzeb wyboru sposobu szyfrowania bazy danych. Po pierwsze – należy wybrać jeden z czterech algorytmów szyfrowania. Dostępne algorytmy to:

    a.  AES128

    b.  AES192

    c.  AES256

    d.  TripleDES

Wybór algorytmu będzie zależał bezpośrednio od wymagań stawianych na bezpieczeństwo danych zawartych w szyfrowanej bazie danych.

Po drugie – należy wskazać certyfikat utworzony w jednym z wcześniejszych kroków tego ćwiczenia oraz zaznaczyć pole Set Database Encryption On.

  7.  Po zatwierdzeniu pokazanych powyżej zmian w oknie zarządzania szyfrowaniem bazy danych możemy być pewni, iż baza danych TDE_Test została zaszyfrowana. Podobnie można bazę danych zaszyfrować za pomocą komend T-SQL, jak pokazano poniżej. Technicznie szyfrowanie bazy danych polega na utworzeniu specjalnego klucza nazywanego Database Encryption Key (DEK) a następnie poinformowaniu silnika baz danych, iż należy wybraną bazę zaszyfrować (drugie polecenie poniżej):

use TDE_test

GO



CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE [MyServerCert]

GO



USE master

GO



ALTER DATABASE TDE_test SET ENCRYPTION ON

GO

  8.  Następnym krokiem w ramach tego zadania będzie sprawdzenie za pomocą odpytania widoków katalogowych, czy i jakie bazy danych zostały zaszyfrowane. W tym celu należy uruchomić poniższe zapytanie:

SELECT 

    DB.name as [Nazwa bazy danych]

    ,K.create_date as [data zaszyfrowania bazy danych]

    ,K.key_algorithm as [Algorytm]

    ,K.key_length as [Długość klucza szyfrującego]

FROM 

    sys.dm_database_encryption_keys K

JOIN sys.databases DB

ON K.database_id = DB.database_id   

WHERE K.encryption_state=3

Zapytanie to pozwala wyświetlić informacje o wszystkich aktualnie zaszyfrowanych bazach oraz wybranym algorytmie szyfrowania i długości klucza szyfrującego. W wyniku wykonania powyższego zapytania można uzyskać następujący wynik:

Rzeczywiście, oprócz zaszyfrowania bazy danych TDE_Test wyświetlona jest informacja, iż systemowa baza danych tempdb również została zaszyfrowana. Dodatkowo dowiadujemy się, iż zaszyfrowano ją algorytmem AES256, czyli silniejszym od wybranego przez nas dla bazy TDE_Test.

  9.  Ostatnim krokiem będzie sprawdzenie, czy rzeczywiście zaszyfrowanie bazy danych pozwala na wyświetlanie danych do aplikacji klienckich w formie czystego tekstu. W tym celu należy ponownie uruchomić sekcję Step 2 skryptu Encrypt DB.sql i stwierdzić, że wyświetlane wyniki są identyczne, jak miało to miejsce przed operacją szyfrowania bazy danych TDE_Test

Zadanie 2

  1.  Dla sprawdzenia, czy można zaszyfrowaną bazę danych podłączyć do innej instancji serwera baz danych należy ją odłączyć a pliki bazy skopiować na dysk twardy innego serwera i spróbować podłączyć. Taka sytuacja nie jest niestety rzadkością i do tej pory wykradzione pliki bazy danych mogły zostać użyte przez niepowołane osoby.

W celu odłączenia bazy danych TDE_Test należy ją zaznaczyć i z menu kontekstowego wybrać opcję Tasks / Detach. W nowym oknie należy zaznaczyć opcje jak pokazano na rysunku poniżej:

  2.  Teraz należy skopiować pliki danych (TDE_Test.mdf) i dziennika transakcji (TDE_Test.ldf) bazy danych TDE_Test znajdujące się w folderze C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA do innego folderu – w naszym wypadku będzie to folder zawierający pliki baz danych instancji EVALUATION\MIRROR. Katalog dla tej instancji można znaleźć na dysku lokalnym C:\Program Files\Microsoft SQL Server\MSSQL10.MIRROR\MSSQL\DATA.

  3.  Kolejny krok będzie polegał na podłączeniu się do instancji EVAUATION\MIRROR za pomocą uwierzytelniania Windows – tak, jak to zrobiliśmy w punkcie 1 zadania nr 1. Po nawiązaniu połączenia należy uruchomić skrypt Attach DB Fails.sql znajdujący się w katalogu C:\SQLAdmin\TDE.

Ważne: skrypt musi zostać uruchomiony na instancji EVALUATION\MIRROR.

Okazuje się, iż próba podłączenia zaszyfrowanej bazy danych TDE_Test kończy się niepowodzeniem. Przyczyna powstania błędu jest brak certyfikatu, którego nie ma na instancji, na której próbowaliśmy podłączyć zaszyfrowaną wcześniej bazę danych.

  4.  Jedynym sposobem na podłączenie zaszyfrowanej bazy danych na instancji EVALUATION\MIRROR jest przeniesienie tam kopii certyfikatu, za pomocą którego baza danych TDE_Test została zaszyfrowana. W tym celu należy uruchomić skrypt Export Cert.sql znajdujący się w katalogu C:\SQLAdmin\TDE, który wykonuje kopię zapasową certyfikatu MyServerCert (z hasłem) i zapisuje ją we wskazane miejsce na dysku lokalnym.

BACKUP CERTIFICATE MyServerCert

TO FILE = 'c:\temp\MyServerCert'

WITH PRIVATE KEY (file='c:\temp\MyServerCertKey',

ENCRYPTION BY PASSWORD ='UseStrongPassword1!')

Do katalogu C:\Temp zostały zapisane dwa pliki – jeden plik zawierający certyfikat, a drugi plik z kluczem szyfrującym.

Ważne: skrypt musi zostać uruchomiony na instancji EVALUATION.

  5.  Po tym, jak właściwy certyfikat został wyeksportowany na dysk można zaimportować go do instancji EVALUATION\MIRROR. W tym celu należy uruchomić skrypt Import Cert.sql znajdujący się w katalogu C:\SQLAdmin\TDE. Importowanie certyfikatu składa się z dwóch kroków (podobnych do tych wykonanych na początku zadania nr 1). W pierwszym kroku tworzy się klucz tzw. System Master Key, ale z jedną różnicą – ten klucz może posiadać inne hasło niż analogiczny klucz na instancji EVALUATION:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'UseDifferentStrongPassword1!'

W drugim kroku następuje utworzenie certyfikatu z certyfikatu wyeksportowanego i zapisanego wcześniej na dysku lokalnym:

CREATE CERTIFICATE MyServerCert

FROM FILE='c:\temp\MyServerCert'

WITH PRIVATE KEY (

FILE = 'c:\temp\MyServerCertKey',

DECRYPTION BY PASSWORD='UseStrongPassword1!')

Ważne: skrypt musi zostać uruchomiony na instancji EVALUATION\MIRROR.

  6.  Pomyślnie zaimportowany certyfikat pozwoli poprawnie podłączyć zaszyfrowaną bazę danych TDE_Test na instancji EVALUATION\MIRROR. W tym celu należy jeszcze raz uruchomić skrypt Attach DB Fails.sql znajdujący się w katalogu C:\SQLAdmin\TDE.

Ważne: skrypt musi zostać uruchomiony na instancji EVALUATION\MIRROR

Tym razem operacja podłączenia bazy danych została przeprowadzona pomyślnie.

  7.  W podobny sposób działa szyfrowanie kopii zapasowej bazy danych i odtwarzanie jej na serwerze, który nie posiada odpowiedniego certyfikatu. W takim przypadku przy próbie wykonania operacji RESTORE nastąpi błąd, z którym zetknęliśmy się w trakcie wykonywania zadania nr 2. Postępowanie jest analogicznie także w tym przypadku – należy zaimportować odpowiedni certyfikat na drugim serwerze ponieważ w przeciwnym wypadku nie będzie żadnej możliwości odzyskania takiej bazy danych.

  8.  Ostatnim krokiem zadania nr 2 jest wykonanie testu, w którym sprawdzimy, czy rzeczywiście można na instancji EVALUATION\MIRROR odczytać dane zawarte w zaszyfrowanej bazie danych TDE_Test. W tym celu należy wykonać trzy zapytania, które zostały pokazane i omówione przy okazji zadania nr 1 – opisane w sekcji Step 2 skryptu Encrypt DB.sql. Wynik uruchomienia zapytań jest zgodny z oczekiwanym – informacje zapisane w bazie danych TDE_Test są dostępne także na drugiej instancji – jak pokazano na rysunku poniżej.

 Do początku strony Do początku strony

Transparent Data Encryption – referencje

Dodatkowe informacje na temat funkcjonalności TDE można znaleźć w Internecie:

[1] Database Encryption in SQL Server 2008 Enterprise Edition - Books Online

[2] Co nowego w silniku bazodanowym SQL Server 2008 November CTP

[3] SQL Server 2008: Transparent data encryption feature - a quick overview on Laurentiu Cristofor's blog

[4] Prezentacja na temat TDE

[5] SQL Server 2008 - Transparent Data Encryption - blog Kimberly Tripp


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 Do początku strony

Akademia SQL - Część 2: Resource Governor     Akademia SQL     Akademia SQL - Część 4: Kompresja kopii zapasowej