SQL Server 2016 – Always Encrypted

Bezpieczeństwo danych od zawsze było i jest bardzo istotnym czynnikiem doboru oprogramowania w biznesie. Dostawcy prześcigają się w coraz to nowszych sposobach zabezpieczania i dostępu do danych – nie inaczej jest z SQL Server, który w wersji 2016 wprowadza mechanizm Always Encrypted będący obok opisywanego wcześniej Row Level Security najważniejszą nowością związaną z najnowszą wersją serwera bazodanowego Microsoftu. Niniejszy artykuł ma na celu przybliżyć Wam nieco bardziej tą niezwykle ciekawą funkcjonalność – zaczynajmy!

Mechanizm Always Encrypted pozwala na zaszyfrowanie wrażliwych danych w konkretnych kolumnach. Całość działa w taki sposób aby dane szyfrowane i odszyfrowywane były na poziomie narzędzia klienckiego. Dzięki temu podejściu w bazie danych widoczne są tylko szyfrogramy, które bez odpowiedniego klucza niewiele mówią. Daje to możliwość separacji pomiędzy tym kto może określone dane oglądać, a tym kto danymi( a raczej serwerem) zarządza. Całość procesu szyfrowania spoczywa na odpowiednim sterowniku aplikacji klienckiej, który tak jak już wspomniałem, w odpowiednim momencie w sposób transparentny podejmuje działania szyfrujące/deszyfrujące. Słowo “Always” w nazwie technologii pojawia się nie bez przyczyny – technologia ta działa w taki sposób, że dane są po stronie serwera zaszyfrowane praktycznie zawsze – bez względu na to czy znajdują się w danym momencie na dysku, w pamięci, podczas wykonywania zapytaniami czy też podczas przesyłania przez sieć do narzędzia klienckiego.

Sterownik, o którym mowa to ADO.Net SqlClient driver for .Net 4.6. Musi on być dostępny na maszynie klienckiej i mieć dostęp do klucza szyfrującego tak aby transparentnie zapewnić komunikację pomiędzy serwerem SQL, a samym narzędziem klienckim. Architekturę całego mechanizmu została przedstawiona na poniższym schemacie:

alwaysencrypted

W ramach architektury opisywanego mechanizmu mamy do czynienia z dwoma rodzajami kluczy:

Column Encryption Key – służący do szyfrowania danych w kolumnach
Master Encryption Key -służący do szyfrowania wyżej wymienionych Column Encryption Key

Sprawdźmy działanie Always Encrypted na przykładzie. Stwórzmy przykładową bazę danych o nazwie AlwaysEncryptedDemo:

CREATE DATABASE AlwaysEncryptedDemo
GO

Następnie stwórzmy sobie tabelę pacjentów, w której będziemy szyfrować dwie kolumny z wrażliwymi danymi oraz wstawmy przykładowe dane:

USE AlwaysEncryptedDemo
GO

CREATE TABLE dbo.Patients
(
PatientID INT IDENTITY,
SSN NVARCHAR(10),
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
City NVARCHAR(50),
BirthDate date
)
GO

INSERT INTO Dbo.Patients
(
SSN,FirstName,LastName,City,BirthDate
)
VALUES
('1234567890','Jan','Kowalski','Katowice','20000101'),
('1234567890','Karol','Nowak','Warszawa','20000101'),
('5555555555','Joanna','Kowalska','Łódź','19900101')
GO

Następnie włączymy sobie Always Encrypted używając graficznego interfejsu użytkownika – kliknijmy prawym przyciskiem myszy na wybraną tabelę i z menu kontekstowego wybierzmy Encrypt Columns:

ssms_alwaysencrypted

Po wybraniu powyższej opcji naszym oczom powinien ukazać się kreator. Pierwsze okno jest niczym innym jak oknem informacyjnym niewymagającym wyjaśnienia.

alwaysencrypted1

Drugie okno kreatora jest dużo bardziej interesujące. W tym miejscu musimy zaznaczyć te kolumny, które chcemy zaszyfrować oraz wybrać typ szyfrowania. Mamy do dyspozycji dwie metody szyfrowania:

Randomized – którego szyfrogram będzie zawsze różny
Deterministic – którego szyfrogram będzie taki sam jeśli wartość szyfrowana będzie taka sama

Np. W przypadku gdy szyfrujemy kolumnę zawierającą Płeć przechowującą wartości K oraz M to szyfrogram deterministyczny będzie taki sam dla wszystkich K oraz taki sam dla wszystkich M. Z kolei szyfrogram niedeterministyczny (Randomized) będzie różny bez względu na wartość jaka została zaszyfrowana. Możecie się domyślać, że typ niedeterministyczny jest bezpieczniejszy jednakże uniemożliwia nam wyszukiwanie po wartości itp.

alwaysencrypted2

W ramach tego okna możemy również wybrać lub wygenerować Column Encryption Key (w naszym przypadku wygenerowaliśmy nowy klucz o nazwie CEK_Auto1 – będzie on używany w stosunku do obu kolumn). Dodatkowo w przypadku kolumny SSN można zauważyć znak ostrzeżenia – mówi nam ono o tym, że zmienione zostanie COLLATION na BIN2 gdyż właśnie rodzina takich COLLATION jest wspierana przez mechanizm AlwaysEncrypted.

Kolejne okno pozwala nam wygenerować Column Master Key, który może być przechowywany w bezpiecznym kontenerze na certyfikaty systemu Windows lub też w chmurze Azure.

alwaysencrypted3

Następnie możemy wygenerować skrypt Powershell, który będzie możliwy do wykorzystania w późniejszym czasie lub uruchomić cały proces natychmiast – wybierzmy drugą opcję. Naszym oczom ukaże się okno podsumowania, a zaraz po nim okno Results.

alwaysencrypted5

Okno Results pozwala nam na sprawdzenie czy cała operacja przebiegła pomyślnie i ewentualnie przejrzeć szczegółowy log.

alwaysencrypted6

W tym momencie nasz mechanizm zaczął działać. Oczywiście wszelkie informacje na temat zaszyfrowanych kolumn możemy znaleźć w metadanych – widok systemowy sys.columns został wzbogacony o nowe kolumny mówiące o zastosowanym algorytmie szyfrowania, kluczu, typie szyfrowania itp:

select 
	name,
	encryption_type,
	encryption_type_desc,
	encryption_algorithm_name,
	column_encryption_key_id 
from sys.columns
where object_id=OBJECT_ID('dbo.Patients')

alwaysencrypted7

Poprawność wygenerowania kluczy możemy również dostrzec używając graficznego interfejsu użytkownika w sekcji Security:

alwaysencrypted8

Jeżeli mamy już gotowe klucze i chcemy ich użyć na nowej tabeli możemy użyć następującej składni:

CREATE TABLE [dbo].[Patients](
	[PatientID] [int] IDENTITY(1,1) NOT NULL,
	[SSN] [nvarchar](10) COLLATE Polish_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
	[FirstName] [nvarchar](50) NULL,
	[LastName] [nvarchar](50) NULL,
	[City] [nvarchar](50) NULL,
	[BirthDate] [date] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL
) ON [PRIMARY]

W tym miejscu przetestujemy sobie Always Encrypted w akcji – jako narzędzie klienckie posłuży nam Management Studio, które obecnie wspiera jedynie deszyfrację kolumn (nie jest możliwa filtracja po zaszyfrowanych kolumnach, wstawianie ani aktualizacja danych zaszyfrowanych…) jednak na ten moment będzie to dla nas wystarczające.

Pierwszym krokiem będzie uruchomienie nowego okna SSMS i tam napisanie prostego zapytania na tabeli z wrażliwymi danymi:

select * from dbo.Patients

W rezultacie otrzymaliśmy zestaw danych, gdzie nasze wrażliwe dane są zaszyfrowane.

alwaysencrypted9

Warto w tym miejscu odnotować, że szyfrogram BirthDate jest taki sam dla Jana Kowalskiego oraz Karola Nowaka ze względu na to, że wybraliśmy deterministyczny typ szyfrowania, a zaszyfrowana kolumna zawiera te same dane. Kolumna SSN również jest zaszyfrowana jednak każdy szyfrogram będzie inny co daje nam zwiększone bezpieczeństwo, jednak będziemy mogli użyć tej kolumny tylko do wyświetlania wartości.

Kolejnym krokiem naszej demonstracji jest otwarcie nowego połączenia używając SSMS – tym razem jednak musimy wejść na zakładkę Additional Connection Parameters i tam jako dodatkowy parametr wpisujemy Column Encryption Setting=Enabled.

alwaysencrypted10

Używając tego połączenia ponownie odpytajmy naszą zaszyfrowaną tabelę.

select * from dbo.Patients

alwaysencrypted11

Tym razem dane zostały odszyfrowane – ponieważ nasze narzędzie klienckie miało dostęp do odpowiedniego klucza. To co jest najważniejsze w tym przypadku to fakt, iż SQL Server zawsze widzi te dane jako zaszyfrowane – dzięki temu są one zawsze bezpieczne. Mechanizm ten jest niezwykle ważną nowością – dzięki niemu wrażliwe dane będą zabezpieczone od strony SQL Server nawet przed oczyma administratorów bazy danych. W przypadku gdy posiadamy licencję na SQL Server 2016 i przechowujemy wrażliwe dane warto rozważyć użycie tejże funkcjonalności.