Jak działa Partially Contained Database w SQL Server

W SQL Server standardowo ścieżka uwierzytelnienia wygląda następująco: użytkownik łączący się do serwera musi mieć tzw. login na poziomie instancji, który może być loginem SQL Server lub użytkownikiem lokalnym/domenowym opartym o Windows (bądź grupę Windows do której określony użytkownik należy). W zależności od potrzeb możemy wybrać uwierzytelnienie Windows bądź SQL Server właśnie. Jednakże posiadanie samego loginu to nie wszystko gdyż dodatkowo musimy stworzyć użytkownika w bazie do której dana osoba ma mieć dostęp. Ten schemat postępowania jest nam wszystkim znany już od wielu lat – nie jest to jednakże jedyna możliwość. Możemy również definiować tzw. Contained Databases, które mimo, że są dostępne od SQL Server 2012 nie są powszechnie znane – dlatego też chciałbym dziś na napisać na ten temat parę słów.

Z punktu widzenia tematu niniejszego artykułu wyróżniamy trzy rodzaje bazy danych tj.:

• Fully Contained
• Partially Contained
• Non Contained

Tradycyjnie postrzegane bazy SQL Server są typu Non Contained (NC) tzn. bardzo wiele funkcjonalności w tym również metadanych przechowywanych jest poza bazą, a uwierzytelnienie jest wykonywane na poziomie instancji. W przypadku bazy Fully Contained (FC) nasza baza jest niejako “samowystarczalna” gdyż wszelkie ustawienia, obiekty czy metadane zawarte są właśnie w tej bazie danych tj. nie ma potrzeby “sięgania” do ustawień na poziomie instancji. Jak zapewne się domyślacie baza Partially Contained (PC) z kolei jest czymś pomiędzy czyli w dużej mierze zawiera w sobie wszystko to czego potrzebujemy, ale pewne aspekty znajdują się na poziomie instancji. Jakie to aspekty? Postaram się najpopularniejsze z nich przedstawić w dalszej części artykułu.

Jednym z najczęstszych problemów z tradycyjnymi bazami NC jest fakt, iż aby je przenieść z serwera A na serwer B musimy zazwyczaj wykonać dodatkową pracę, za doskonały przykład posłużą nam tutaj loginy serwerowe przechowywane w systemowej bazie danych master. W momencie gdy chcemy naszą bazę przenieść na inny serwer to loginy też musimy albo odtworzyć ręcznie albo też kombinować w inny sposób z pakietami SSIS lub bardziej wymyślnymi sposobami migracji. W takim przypadku w zależności od tego jak wielu użytkowników mamy oraz od tego w jaki sposób ich loginy zostały zaimplementowane może nam to przysporzyć więcej lub mniej pracy. W przypadku baz FC problem ten jest w dużej mierze ograniczony gdyż uwierzytelnienie jest w całości “oderwane” od instancji, a dane uwierzytelniające (w tym również hasła jeśli opieramy się na uwierzytelnieniu SQL Server) są przechowywane w bazie samej w sobie bez pośrednictwa bazy master.

Innym problemem na jaki możemy również natrafić jest niezgodność Collation (czyli ustawienia kodowania,porównywania i szeregowania znaków) pomiędzy bazą użytkownika, a bazą systemową tempdb gdzie przechowywane są obiekty tymczasowe. Problem ten może pojawić się np. po wspomnianym wcześniej przeniesieniu bazy użytkownika na inny serwer. Jeżeli ustawienie COLLATION naszej bazy danych różni się od ustawień bazy tempdb to będziemy zmuszeni jawnie wskazać to ustawienie które nas interesuje na poziomie zapytania tworzącego obiekt lub też w zrobić to w zapytaniu SELECT. Jak możecie się domyślać może to być naprawdę bardzo problematyczne – powiem  więcej – jest to jeden z większych koszmarów na jakie można w świecie SQL Server natrafić. W przypadku baz PC problem ten po prostu nie występuje ze względu na fakt, iż domyślnie obiekty tymczasowe stworzone w tej bazie używają COLLATION nie bazy tempdb, a specjalnie zdefiniowanego ustawienia bazy CONTAINED o czym dowiemy się w dalszej części.

Bardzo często zdarza się również, iż instancja SQL Server jest jedynie kontenerem na wiele niezależnych od siebie baz danych, które mimo swojej izolacji w tradycyjnym podejściu muszą być centralnie zarządzane z poziomu instancji, co nie zawsze jest najtrafniejszym wyborem – bazy contained mogą być rozwiązaniem tych wszystkich problemów. Póki co jednak przejdźmy do praktyki – SQL Server sam w sobie wspiera tworzenie dwóch z trzech rodzajów baz danych tj. możemy tworzyć bazy Non Contained oraz Partially contained. Aby móc użyć opisywanej funkcjonalności najpierw musimy taką opcję włączyć – możemy to zrobić z poziomu interfejsu graficznego wchodząc we właściwości instancji:

Bądź też używając TSQL:

sp_configure 'contained database authentication', 1;  
GO  
RECONFIGURE ;  
GO

Po włączeniu tej opcji możemy stworzyć bazę danych, która będzie partially contained – tutaj znów możemy użyć GUI lub TSQL. W zapytaniu tworzącym bazę danych wystarczy, że dodamy klauzulę CONTAINMENT i przypiszemy jej właściwość Partial:

CREATE DATABASE ContainedDb
CONTAINMENT=Partial

Domyślną wartością tej właściwości jest NONE z czego możemy wnioskować, że każda nowo powstała baza jest Non Contained. Z poziomu graficznego wystarczy wejść we właściwości bazy danych i tam zmienić właśnie właściwość CONTAINMENT:

Oczywiście informacja o typie bazy danych istnieje standardowo w metadanych – wystarczy odpytać sys.databases:

SELECT 
       name,
       containment,
       containment_desc 
FROM 
       sys.databases
ORDER BY 
       containment desc

Teraz mając już stworzoną bazę w taki sposób jak tego chcieliśmy spróbujmy stworzyć użytkownika i przypisać go do roli db_owner:

USE [ContainedDB]
GO
CREATE USER [ContainedUser] WITH PASSWORD=N'zaq1@WSX'
GO
ALTER ROLE [db_owner] ADD MEMBER [ContainedUser]
GO

Następnie spróbujmy się na niego zalogować z poziomu Management Studio. Logujemy się w standardowy sposób – jedyne o czym musimy pamiętać to fakt aby ustawić bazę danych do której się logujemy na naszą ContainedDb w przeciwnym wypadku użytkownik nie zostanie zalogowany:

Po zalogowaniu w Object Explorer możemy zobaczyć, że jedynym widocznym węzłem jest baza danych. Warto też zwrócić uwagę na to, że obok nazwy zalogowanego użytkownika widzimy również nazwę bazy danych – wskazuje to na fakt, iż mamy do czynienia z uwierzytelnieniem zrealizowanym przy pomocy bazy funkcjonującej jako Partially Contained:

Użytkownik z którego skorzystaliśmy może robić w naszej bazie danych wszystko to do czego ma uprawnienia. Ciekawe jest to, że możemy stworzyć login serwerowy, który będzie miał uprawnienia do naszej bazy danych. Tworząc takie powiązanie musimy zdawać sobie sprawę, że ogranicza mobilność naszej bazy danych – jednakże gdy istnieje taka potrzeba to nic nie stoi na przeszkodzie. Do dosyć dziwnej sytuacji może dojść w przypadku gdy stworzymy login serwerowy o takiej samej nazwie jak Contained User – sprawdźmy to na przykładzie – stwórzmy taki obiekt z innym hasłem i odpowiadającego mu użytkownika w ContainedDB:

USE [master]
GO
CREATE LOGIN [ContainedUser] 
	WITH PASSWORD=N'zaq1@WSX2', 
	DEFAULT_DATABASE=[ContainedDb]
GO
USE [ContainedDb]
GO
CREATE USER [ContainedUser2] FOR LOGIN [ContainedUser]
GO

Następnie spróbujmy się zalogować przez SSMS – mimo usilnych starań nasze działania zakończą się niepowodzeniem – zwrócony zostanie błąd mimo, że dane logowania zostały podane poprawnie:

Dosyć ciekawe zachowanie prawda? Wynika ono z faktu, że domyślną bazą naszego użytkownika jest baza ContainedDB, która zawiera użytkownika o takiej samej nazwie jak login przez co próbuje go uwierzytelnić co ostatecznie się nie udaje ze względu na inne hasła. Jakie jest rozwiązanie takiego przypadku? Najlepiej nie tworzyć użytkownika w bazie contained i loginu o tej samej nazwie – gdy jednak musimy coś takiego wykonać to przy łączeniu do bazy loginem wybierzmy inną bazę niż domyślna bądź zmieńmy mu domyślną bazę na np. tempdb. Tego typu przypadki mogą powodować niemałe zamieszanie czemu nie należy się dziwić.

Oprócz tego zawsze istnieje możliwość odcięcia użytkowników bazy Contained od loginów- możemy to zrobić przy pomocy procedury sp_migrate_user_to_contained:

sp_migrate_user_to_contained  
	 @username = N'ContainedUser2' ,   
     @rename =  N'keep_name',   
     @disablelogin =  N'disable_login'

Procedura przyjmuje trzy parametry: @username to wskazanie nazwy użytkownika bazy contained, @rename wskazuje czy użytkownik ma zatrzymać swoją dotychczasową nazwę (wartość keep_name) czy tez ma być zastąpiona nazwą loginu (wartość copy_login_name) oraz @disablelogin która jak sama nazwa wskazuje pozwala wyłączyć login po dokonaniu “konwersji” (dostępne wartości to N’disable_login’ i ‘do_not_disable_login’). Proste łatwe i przyjemne.

W momencie gdy chcemy przenieść naszą bazę contained na inny serwer lub ustawić istniejącą bazę na contained warto wiedzieć, które obiekty przekraczają granice bazy i ograniczając przy tym naszą mobilność. Możemy to zrobić w dwojaki sposób – pierwszym z nich jest wykorzystanie widoku sys.dm_db_uncontained_entities:

SELECT 
      SO.name, 
      UE.* 
FROM sys.dm_db_uncontained_entities AS UE  
LEFT JOIN sys.objects AS SO  
    ON UE.major_id = SO.object_id;

Widok ten zawiera wszystkie elementy, które przekraczają granicę bazy danych. Zwracane są nie tylko obiekty same w sobie ale również zapisy, których SQL Server nie jest w stanie sprawdzić takie jak dynamiczny SQL. Oprócz niego mamy możliwość wykorzystania Extended Events, który to mechanizm posiada specjalne wydarzenie, które możemy śledzić o nazwie database_uncontained_usage – poniżej zawarłem skrypt tworzenia sesji (dodatkowo dodałem filtr w taki sposób aby wyłapywane były tylko zdarzenia wywołane przez użytkownika ContainedUser lub Administratora):

CREATE EVENT SESSION [CDB] ON SERVER 
ADD EVENT sqlserver.database_uncontained_usage(
    WHERE 
([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[username],N'ContainedUser') 
	OR [sqlserver].[session_nt_user]=N'Administrator'))
ADD TARGET package0.event_file
(SET filename=N'C:\xEvents.xel',max_rollover_files=(1))
GO

Następnie wywołajmy kilka zapytań używając połączenia użytkownika ContainedUser. Oprócz tego logując się na standardowego użytkownika o nazwie Administrator z poziomu ContainedDb odpytajmy bazę WideWorldImportersDW:

-- zapytania z poziomu ContainetUser
SELECT @@VERSION
GO

SELECT * FROM sys.databases
GO

--zapytania z poziomu Administator
SELECT top 100 * FROM WideWorldImportersDW.Fact.Movement
GO 5

Rezultatem naszych działań będzie plik, który powinien zawierać informacje o zapytaniach wywołanych przez użytkowników ContainedUser lub Administrator, które wykraczały poza granicę bazy ContainedDB. Plik możemy otworzyć w SSMS – po przejrzeniu zawartości możemy zauważyć, że takie właśnie działania zostały zarejestrowane:

Powiedzmy nieco więcej o wspomnianych wcześniej problemach z COLLATION – zobaczmy to zachowanie na konkretnym przykładzie. Zalogujmy się jako nasz ContainedUser do bazy i zmieńmy COLLATION w taki sposób aby baza odróżniała wielkość znaków czyli była CASE SENSITIVE:

USE [master]
GO
ALTER DATABASE [ContainedDb] COLLATE SQL_Latin1_General_CP1_CS_AS
GO

Następnie stwórzmy dwie tabele o dokładnie tej samej strukturze z tą różnicą, że jedna będzie tabelą tradycyjną, a druga tabelą tymczasową (a więc przechowywaną w tempdb):

CREATE TABLE StringTests
(
Name varchar(20)
)
GO

CREATE TABLE #TempStringTests
(
Name varchar(20)
)

Następnie wstawmy do tych tabel dokładnie te same przykładowe wiersze:

INSERT INTO StringTests
VALUES
(
'Test String'
),
(
'test string'
)
GO
INSERT INTO #TempStringTests
VALUES
(
'Test String'
),
(
'test string'
)
GO

Ostatnim krokiem naszego testu jest próba połączenia tych tabel po istniejącej kolumnie:

SELECT *
FROM StringTests AS S
JOIN #TempStringTests AS T
ON S.Name=T.Name

Rezultat jest dosyć łatwy do przewidzenia – zapytanie zwróciło dokładnie taki zestaw danych jaki potrzebowaliśmy:

Teraz możemy wykonać dokładnie te same czynności tylko na jakiejkolwiek innej bazie, która nie jest zdefiniowana jako contained np. WideWorldImportersDW (pamiętajmy o zmianie COLLATION) – w tym przypadku zapytanie zwróciło błąd związany z niezgodnością ustawienia COLLATION:

W tym wypadku jedynym sposobem jest jawne wskazanie COLLATION w zapytaniu lub zmiana dla całej bazy:

SELECT *
FROM StringTests AS S
JOIN #TempStringTests AS T
ON S.Name=T.Name COLLATE SQL_Latin1_General_CP1_CS_AS

Błąd powstał dlatego, że obiekty tworzone w TEMPDB domyślnie będą miały COLLATION takie jak baza TEMPDB co nie zawsze jest zgodne z bazą użytkownika. Obiekty tymczasowe w bazie oznaczonej jako PARTIALLY CONTAINED są tworzone z COLLATION ustawionym jako tzw. CATALOG COLLATION. Jest to sztywne ustawienie obiektów tymczasowych bazy CONTAINED na Latin1_General_100_CI_AS_WS_KS_SC.

Standardowe ustawienie COLLATION w bazie odnosi się do danych użytkownika, a obiekty w TempDB związane z nasza bazą otrzymają właśnie CATALOG COLLATION. Do tego ustawienia możemy również odwoływać się w TSQL używając słów kluczowych CATALOG_DEFAULT:

SELECT * FROM WideWorldImportersDW.dbo.StringTests AS F
JOIN ContainedDb.dbo.StringTests AS C
ON F.Name=C.Name COLLATE CATALOG_DEFAULT

Oczywiście Contained databases posiadają też liczne ograniczenia takie jak brak możliwości użycia Change Data Capture, Change Tracking czy chociażby replikacji, jednakże w przypadku wymogu mobilności mogą one zaoszczędzić nam sporo pracy. Pamiętajmy również o aspektach bezpieczeństwa gdyż w przypadku baz tego typu użytkownicy mający rolę db_owner czy też db_securityadmin mogą dodawać użytkowników do bazy bez wiedzy administratora baz danych co może być dosyć poważną luką w naszej polityce bezpieczeństwa. Podsumowując – bazy tego typu mogą być bardzo pomocne w określonych warunkach dlatego też jak zawsze warto pamiętać, że taka funkcjonalność istnieje.