W SQL Server można szyfrować dane w tabeli, można szyfrować cały plik bazy danych (transparent data encryption), od wersji 2016 można także stosować szyfrowanie po stronie klienta. Tutaj i teraz pokażę jak włączyć szyfrowanie komunikacji z SQL.
Do szyfrowania SQL wykorzystuje certyfikat, który jest ładowany podczas startu usługi. Jeśli w żaden sposób nie zostało to skonfigurowane, to SQL nie posiada żadnego dedykowanego certyfikatu, dlatego podczas startu na wszelki wypadek sam sobie generuje certyfikat. Można powiedzieć, że ten certyfikat jest nic nie warty, bo SQL sam go sobie podpisuje, a poprawny certyfikat powinien być podpisany przez zewnętrzny zaufany root certificate authority. Ten certyfikat przyda się jednak, jeżeli któryś z klientów zechce uzyskać szyfrowane połączenie. Ten wpis odnajdziesz w errorlog, jeżeli znajdujesz się dokładnie w tej sytuacji (A Self-generated certificate was succesfully loaded for encryption):
Kiedy klient łączy się do takiego serwera domagając się szyfrowania może jednak otrzymać błąd, ponieważ certyfikat (jak wcześniej zaznaczyłem) nie jest poprawny (tzw self signed certificate). Oto connection properties w SQL Server Management Studio:
A oto błąd jaki może być przy tym zgłoszony (A connection was successfully established with the server, but then an error occurred during the pre-login handshake / The certificate’s CN name does not match the passed value):
Istnieje metoda, aby mimo wszystko włączyć szyfrowanie z wykorzystaniem tego nie do końca poprawnego certyfikatu. Trzeba w Additional Connection Parameters dodać linijkę:
TrustServerCertificate=true
Wtedy połączenie się uda.
Przy okazji – to czy połączenie jest zaszyfrowane czy nie można łatwo sprawdzić korzystając z widoku sys.dm_exec_connections (warunkiem jest połączenie z wykorzystaniem protokołu TCP/IP i włączenie opcji szyfrowania):
Wartość w kolumnie encrypt_option równa true oznacza że szyfrowanie jest włączone.
W takim razie jak skonfigurować poprawne szyfrowanie?
Zacznijmy od tego, że potrzebny jest poprawny certyfikat. Może to być certyfikat dedykowany dla SQL server ale można również skorzystać z najprawdopodobniej już zainstalowanego certyfikatu na serwerze. Tutaj pokażę jak skorzystać z certyfikatu komputera. Korzystanie z dedykowanego certyfikatu różni się tylko tym, że certyfikat ten należy otrzymać i zainstalować na komputerze w prywatnym magazynie certyfikatów konta na którym działa SQL server. Uprawnienia do klucza prywatnego takiego certyfikatu SQL otrzymuje wtedy automatycznie i można właściwie nic więcej nie robić… No ale skupmy się na scenariuszu gdzie skorzystamy z certyfikatu komputera. Zaczynamy od uruchomienia
mmc.exe
Następnie dodajemy przystawkę do zarządzania certyfikatami:
Kliknij File >> Add / remove snap-in, a następnie zaznacz certificate i kliknij Add:
Pojawi się wtedy okienko z pytaniem, jakie certyfikaty mają zostać pokazane. Wybierz Computer account żeby pracować z certyfikatami istniejącymi obecnie na komputerze i wskaż o który komputer chodzi (u nas jest to komputer lokalny)
Certyfikat prywatny komputera znajduje się w folderze Personal:
Certyfikat jest, cały problem w tym, że SQL nie może odczytać jego klucza prywatnego co jest niezbędne do wykonywania czynności szyftowania. W takim razie nadajmy SQLowi wymagane uprawnienia. Kliknij certyfikat i z menu kontekstowego wybierz All tasks >> Manage private keys:
Potem nadaj uprawnienia – wystarczy sam odczyt:
I gotowe! Po zrestartowaniu serwera SQL w errorlogu będzie można znaleźć następujący wpis:
Od tej pory, kiedy klient zażyczy sobie szyfrowanego połączenia, SQL skorzysta z poprawnego certyfikatu komputera i linijka TrustServerCertificate nie będzie potrzebna. Spotkaź za to możesz całkiem inne problemy (A connection was successfully established with the server, but then an error occurred during the pre-login handshake / The target principal name is incorrect):
Będzie tak jeśli łączysz się do serwera korzystając np. z aliasu a nie z pełnej nazwy serwera zgodnej z tym co jest na certyfikacie z którego korzysta SQL. Pomóc wtedy może Certificate with subject alternative names, ale to już całkiem inna historia.
Zwróćmy jeszcze uwagę na to, że szyfrowanie połączenia można też wymusić po stronie serwera, a nie klienta i to na dodatek można wskazać, jaki certyfikat ma być w tym celu wykorzystywany. To wszystko można zrobić na zakładce Flags podczas konfigurowania ustawień sieciowych w SQL Configuration Managerze.
Zwróć też uwagę na zakładkę certificate:
Pozwala ona w jawny sposób wskazać jaki certyfikat ma być używany przez SQL. Jeśli nie wskażesz tu żadnego certyfikatu, to SQL wybierze pierwszy lepszy certyfikat do którego klucza prywatnego ma dostęp i który jest ważny.
