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.