Backup, SQLVDI und SYSADMIN Rechte

Bei der Einführung eines externen Sicherungssystems kam die folgende Fragestellung auf: werden für eine Sicherung oder Wiederherstellung einer Datenbank im SQL Server SYSADMIN Rechte benötigt? Das einzusetzende Sicherungssystem eines Dritt-Anbieters installiert auf jedem SQL Server einen Agenten. Dieser, als Windows-Dienst implementierte Agent, wird im Dienstkonto „Local System“ ausgeführt und benötigt laut Dokumentation SYSADMIN Rechte. Das würde aber bedeuten, dass auf jedem SQL Server das lokale Systemkonto die vollständige Kontrolle über die SQL Server Instanzen besitzt.

Das Ergebnis vorweg: die Antwort auf die Frage nach den SYSADMIN Rechten heißt JEIN. Prinzipiell werden für die Sicherung und Wiederherstellung einer Datenbank im SQL Server keine SYSADMIN Rechte benötigt. Wenn aber vom Sicherungssystem das SQLVDI (VDI = Virtual Device Interface) genutzt wird, muss der Benutzer über SYSADMIN Rechte verfügen! Diese Anforderung ergibt sich aus dem SQLVDI und nicht durch das Sicherungssystem!

In den nachfolgenden Schritten werden wir das bereits vorweggenommene Ergebnis erarbeiten. Zunächst definieren wir die Rechte, die für eine Sicherung bzw. Wiederherstellung benötigt werden. Im Anschluss daran prüfen wir die Kombination mit dem Sicherungssystem, welches die SQLVDI Schnittstelle nutzt. Da das Sicherungssystem in der Testumgebung auf meinem Notebook nicht verfügbar ist, nutze ich eine kleine Anwendung, die das SQLVDI zur Sicherung verwendet.

Das angestrebte Szenario beinhaltet einen dedizierten Benutzer, der für die Sicherung und Wiederherstellung der Datenbanken verwendet werden kann. Im Kontext dieses Benutzers soll das Sicherungssystem dann die Sicherungen und Wiederherstellungen der Datenbanken durchführen.

Es wird zunächst ein SQL Server Anmeldekonto angelegt, welches später die Sicherung durchführen soll. Da dieser Benutzer jede Datenbank auf unserer SQL Server Instanz sichern soll, wird für dieses Anmeldekonto ein Benutzer in der Datenbank model angelegt.

--
USE [master]
GO

CREATE LOGIN [BackupOperator] 
WITH 
	PASSWORD=N'backupoperator', 
	DEFAULT_DATABASE=[master], 
	CHECK_EXPIRATION=OFF, 
	CHECK_POLICY=OFF;
GO

USE [model]
GO

CREATE USER [BackupOperator] FOR LOGIN [BackupOperator] WITH DEFAULT_SCHEMA=[dbo];
GO

Änderungen in der model Datenbank haben keine Auswirkungen auf bestehende Datenbanken. Daher müssen die beschriebenen Anpassungen der model Datenbank auch auf bestehende Datenbanken angewendet werden!

Da die benötigten Rechte keinem Benutzer direkt zugeordnet werden sollen, wird in der model Datenbank eine Datenbankrolle angelegt. Diese Rolle wird Mitglied in den folgenden Datenbankrollen:

  • db_backupoperator
  • db_denydatareader
  • db_denydatawriter.

Während die Mitgliedschaft in db_backupoperator die Sicherung der Datenbank erlaubt, verhindert die Mitgliedschaft in den beiden anderen Gruppen den Zugriff auf die Daten in der Datenbank. Der Benutzer wird Mitglied der von uns angelegten Datenbank Rolle. Die Änderungen in der model Datenbank sind damit abgeschlossen.

--
CREATE ROLE [BackupOperators]
GO

ALTER ROLE [BackupOperators] ADD MEMBER [BackupOperator];
GO

ALTER ROLE [db_backupoperator] ADD MEMBER [BackupOperators];
ALTER ROLE [db_denydatareader] ADD MEMBER [BackupOperators];
ALTER ROLE [db_denydatawriter] ADD MEMBER [BackupOperators];
GO

Es ist mir bewusst, dass die beiden Rollen db_denydatareader und db_denydatawriter nicht die Ausführung von Gespeicherten Prozeduren verhindern. Da ist also noch ein wenig "Luft nach oben" :)

Zum Test der Datenbanksicherung wird eine neue Datenbank angelegt. Diese entsteht als Kopie der model Datenbank und beinhaltet automatisch die vorgenommenen Anpassungen. Für den Zugriffstest auf mögliche Daten wird eine kleine Beispieltabelle angelegt.

--
USE [master]
GO

IF (SELECT DB_ID('BackupTestDB')) IS NOT NULL
BEGIN
	DROP DATABASE [BackupTestDB];
END
GO

CREATE DATABASE [BackupTestDB];
GO

USE [BackupTestDB]
GO

CREATE TABLE [dbo].[Test] (ID INT);
INSERT INTO [dbo].[Test] VALUES (1),(2),(3);
GO

Nun versuchen wir im Kontext der Anmeldung die Sicherung auszuführen.

--
-- Ändern des Ausführungskontext
EXECUTE AS LOGIN='BackupOperator';
GO

-- Prüfen des Zugriffs auf die Testtabelle
SELECT * FROM [dbo].[Test];

-- Durchführen der Sicherung
BACKUP DATABASE [BackupTestDB] TO DISK='BackupTestDB.bak' WITH INIT;

REVERT;
GO

Unsere Erwartung wird erfüllt: die Sicherung der Datenbank konnte erfolgreich durchgeführt werden. Die Fehlermeldung bezieht sich auf den nicht autorisierten Zugriff auf die Tabelle Test.

Für die Rücksicherung der Datenbank müssen wir Rechte auf der Instanz (Server) Ebene vergeben. Analog zu der Vorgehensweise für die Sicherung, wird zunächst eine Serverrolle definiert. Diese wird Mitglied der Rolle dbcreator. Der Benutzer wird Mitglied unserer neuen Serverrolle.

--
USE [master]
GO

CREATE SERVER ROLE [BackupOperators];
GO

ALTER SERVER ROLE [BackupOperators] ADD MEMBER [BackupOperator];
GO

ALTER SERVER ROLE [dbcreator] ADD MEMBER [BackupOperators];
GO

Nun erfolgt im Kontext der Anmeldung die Wiederherstellung der Datenbank.

--
-- Löschen der bestehenden Datenbank BackupTestDB.
IF (SELECT DB_ID('BackupTestDB')) IS NOT NULL
BEGIN
	DROP DATABASE [BackupTestDB];
END

-- Ändern des Ausführungskontext
EXECUTE AS LOGIN='BackupOperator';
GO

-- Durchführen der Rücksicherung
RESTORE DATABASE [BackupTestDB] FROM DISK='BackupTestDB.bak';

-- Testzugriff auf die Tabelle
SELECT * FROM [BackupTestDB].[dbo].[Test];

REVERT;
GO

Auch hier wird unsere Erwartung erfüllt. Die Wiederherstellung der Datenbank konnte erfolgreich durchgeführt werden. Es besteht weiterhin kein Zugriff auf die in der Datenbank enthaltenen Daten.

Nachdem wir nun das von uns gewünschte Szenario implementiert haben, soll jetzt die Sicherung der Datenbank über das externe Sicherungssystem erfolgen. Hierzu nutze ich eine kleine Testanwendung, die das SQLVDI zur Sicherung und Wiederherstellung nutzt. Die Anwendung wurde von Steve Abraham auf CodeProject veröffentlicht. Der Quellcode ist dort erhältlich. Ich habe ein paar kleine Änderungen vorgenommen, damit die Lauffähigkeit in meiner Umgebung gegeben ist.

Das SQLVDI wurde geschaffen, um die Sicherungssyteme von Dritt-Anbietern zu unterstützen. Die Sicherung erfolgt weiterhin durch den SQL Server, die Daten werden aber nicht durch den SQL Server auf die Platte geschrieben, sondern an das SQLVDI nutzende System zur weiteren Verarbeitung übergeben. Bei der Wiederherstellung erfolgt die Übergabe der Daten in die entgegengesetzte Richtung. Die Dokumentation ist hier zu finden: SQL Server 2005 Virtual Backup Device Interface (VDI) Specification Eine neuere Version scheint nicht zu existieren, die Spezifikationen der Schnittstelle haben sich wohl seit der Version 2005 nicht geändert :)

Die Testanwendung erstellt eine Sicherungsdatei und direkt im Anschluss wird die Anweisung RESTORE HEADER ONLY zur Simulation einer Wiederherstellung ausgeführt. 

Starten wir unsere Testanwendung und schauen uns das Ergebnis an:

Die Sicherung wurde abgebrochen. Die Fehlermeldung definiert die Ursache: fehlende SYSADMIN Rechte! Die Nutzung des SQLVDI ist gut an der Syntax der BACKUP Anweisung zu erkennen: TO VIRTUAL_DEVICE bestimmt, dass die Sicherung nicht durch den SQL Server auf die Platte geschrieben (TO DISK), sondern an das externe Sicherungssystem übergeben werden soll.

Zur Prüfung erhält unser Benutzer SYSADMIN Rechte und wir starten die Testanwendung erneut.

--
-- Zuweisen der SYSADMIN Rechte für den Benutzer
USE [master]
GO

ALTER SERVER ROLE [sysadmin] ADD MEMBER [BackupOperator];
GO

Die BACKUP Anweisung wird erfolgreich ausgeführt!

Da sich diese Tatsache nun mal nicht ändern lässt, wird der Sicherungsagent nicht im lokalen Systemkonto, sondern in einem dedizierten Konto ausgeführt. Ein sinnvoller Einsatz für Group Managed Service Account's.

Es bleibt zu hoffen, dass Microsoft an dieser Stelle in zukünftigen Versionen nachbessert.

Managed Service Accounts - Part 1

Der Managed Service Account (MSA) stellt für den SQL Server ein ideales Dienstkonto dar. Die folgenden Merkmale charakterisieren einen MSA:

  • wird im AD verwaltet
  • automatisches Passwort Management
  • automatisches SPN Management
  • ist einem (sMSA) oder mehreren (gMSA) Computern zugeordnet
  • erlaubt keine interaktive Anmeldung
  • unterstützt Delegierung

Wir wollen uns in mehreren Artikeln mit den MSA’s in Verbindung mit dem SQL Server beschäftigen. Im ersten Teil geht es um die Anlage eines MSA’s und die Nutzung als Dienstkonto für den SQL Server Dienst.

In unserem Beispielsszenario soll für die SQL Standardinstanz auf dem Server sqlhost1.fortsql.com ein MSA mit dem Namen SqlSvcHost1 definiert werden.

Alle relevanten Schritte werden mittels PowerShell umgesetzt, da eine GUI nicht verfügbar ist. Die Ausführung erfolgt auf dem Server, auf dem der MSA eingesetzt werden soll.

Für alle, die schnell die Anweisungen nutzen wollen, vorab ein Screenshot mit allen Befehlen zur Einrichtung des MSA auf dem Server sqlhost1.fortsql.com.

 

Zunächst muss das Active Directory Modul geladen werden:

Import-module ActiveDirectory

Sollte das Modul nicht verfügbar sein, so kann dieses Nachträglich eingerichtet werden. Siehe hierzu Problemstellung 1 am Ende des Artikels.

Die Anlage eines MSA erfolgt mit der folgenden Anweisung

New-ADServiceAccount -Name SqlSvcHost1 -RestrictToSingleComputer -Description "SQL Server Dienstkonto auf SqlHost1"

Der Parameter RestrictToSingleComputer erstellt einen MSA, der ausschließlich auf einem Computer genutzt werden kann. Ohne diesen Parameter wird ein MSA angelegt, der auf mehreren Computern genutzt werden kann. Dieser wird auch als Group Managed Service Account (gMSA) bezeichnet. Wir werden uns dem gMSA in einem späteren Artikel widmen.

Der Name des MSA darf maximal 15 Zeichen lang sein!

Sollte bei der Ausführung von New-ADServiceAccount die Fehlermeldung "Key does not exist" angezeigt werden, so muss zunächst ein Schlüssel für das KDS angelegt werden. Siehe hierzu Problemstellung 2 am Ende des Artikels.

Die Anweisung

dir "AD:CN=Managed Service Accounts,DC=fortsql,DC=com"

zeigt alle definierten MSA's an. MSA’s werden standardmäßig im Container Managed Service Accounts angelegt. Der Container kann aber bei der Anlage des MSA individuell bestimmt werden.

Im nächsten Schritt wird der MSA dem Computer zugeordnet:

Add-ADComputerServiceAccount -Identity sqlhost1 -ServiceAccount SqlSvcHost1 –PassThru

Der Parameter Identity bestimmt den Computer, der Parameter ServiceAccount den angelegten MSA. Hiermit kann der MSA aber noch nicht für einen Dienst auf dem Computer sqlhost1 genutzt werden!

Damit der angelegte und dem gewünschten Computer zugeordnete MSA auf diesem auch genutzt werden kann, muss der MSA auf dem Computer eingerichtet / installiert werden:

Install-ADServiceAccount -Identity SqlSvcHost1

Wichtig: diese Anweisung MUSS (!) auf dem Computer ausgeführt werden, auf dem der MSA genutzt werden soll. In unserem Beispiel also auf dem Server sqlhost1.fortsql.com.

Ein kurzer Test bestätigt die korrekte Einrichtung:

Test-AdServiceAccount -Identity SqlSvcHost1

Nun kann der MSA als Dienstkonto für unseren SQL Server genutzt werden. Die Einrichtung erfolgt mit dem SQL Server Konfiguratonsmanager.

Bei der Angabe des MSA ist zu beachten, dass dem Namen ein $ Zeichen angehangen wird, in unserem Fall wäre die korrekte Eingabe also FORTSQL\SqlSvcHost1$.

Der Dienst muss zur Übernahme der Änderungen neu gestartet werden.

Fazit: die Einrichtung eines MSA's ist schnell umzusetzen und erfordert kein umfangreiches Know-How. Der Einsatz eines MSA's mit dem SQL Server erhöht die Instanz-Sicherheit. Idealerweise wird für jeden Service ein eigener MSA verwendet, zumindest aber für jeden Server ein dedizierter MSA (Stichwort: "Segregation of duties"). Wird der SQL Server in einer Cluster-Umgebung eingesetzt, so muss ein gMSA verwendet werden! Dazu kommen wir ja später noch.

Bis dahin: Happy Securing!

Problemstellung 1:
Das Active Directory Modul ist auf dem Server, auf dem der MSA eingesetzt werden soll, nicht vorhanden.

Das Active Directory Modul kann nachträglich eingerichtet werden. Hier die erforderlichen Schritte mittels GUI. Über den Server Manager den Assistenten zum Hinzufügen von Rollen und Features starten. Im Abschnitt Features unter Remoteserver-Verwaltungstools / Rollenverwaltungstools / AD DS- und AD LDS-Tools das Feature Active Directory-Modul für Windows PowerShell wählen. Die Installation erfordert keinen Neustart des Servers.

Problemstellung 2:
Fehlermeldung "Key does not exist" bei der Ausführung der Anweisung New-ADServiceAccount

Diese Fehlermeldung kann durch die folgende PowerShell Anweisung korrigiert werden (Ausführung einmal pro Domain):

Add-KdsRootKey –EffectiveTime ((get-date).addhours(-10));

Alternativ:

Add-KDSRootKey –EffectiveImmediately

Siehe hierzu auch: TechNet Forum: New-ADServiceAccount: Key does not exist