Technischer Tipp für SQL-Server-Admins
Automatische Übertragung von SQL-Server-Logins
1. Grundlagen
Zur Erinnerung. Die Berechtigungen im SQL Server werden in etwa so vergeben wie der Zugang zu einem Mehrfamilienhaus:
- SQL-Server-Logins - Schlüssel für die Haustür, in SQL Server anzulegen. Kann ein Datenbank- oder ein Windows-Login sein.
- Datenbank Benutzer - Schlüssel für die Wohnung, wird in NAV vergeben
- Rechte auf Tabellen - Schlüssel für die Zimmer, werden in NAV vergeben
Es gilt, ohne Haustürschlüssel kommt man nicht ins Haus und somit auch nicht in die Wohnung, also ohne SQL-Server-Login kommt man nicht an die Datenbank - selbst wenn man als Datenbank Benutzer angelegt ist.
Sichert man nun den Backup einer Datenbank von einem Server auf einen anderen Server zurück, dann werden zwar die Datenbank-Benutzer mit zurückgesichert, nicht aber die SQL-Server-Logins - die stehen in der Master-Datenbank, nicht in der Benutzer-Datenbank.
Damit kommt keiner der ursprünglichen Datenbank-Benutzer mehr an die Datenbank heran, außer er existiert auf dem anderen SQL Server zufällig ebenfalls als SQL-Server-Login.
Schlimmer noch, sichert man die Datenbank zurück und legt danach die darin enthaltenen Datenbank-Benutzer als SQL-Server-Logins an, wird keine Verbindung mehr zwischen dem Datenbank-Benutzer und dem neuen SQL-Server-Login hergestellt. Die Benutzer kommen also auch dann nicht an die Datenbank.
Dies gilt auch für die SQL-Server-eigenen Hochverfügbarkeitskonzepte beginnend bei Log Shipping bis hin zum Database Mirroring. Lediglich die Cluster-Lösung hat dieses Problem nicht, da sich hier die SQL Server im Cluster die Datenbanken teilen, somit auch die Master-Datenbank.
Es gibt aber von Microsoft SQL Scripte, die die SQL-Server-Logins von einem SQL Server auf einen anderen SQL Server übertragen. Diese sind hier zu finden:
SQL Server 2000 - http://support.microsoft.com/kb/246133
SQL Server 2005/2008/2010 - http://support.microsoft.com/kb/918992
Dabei wird eine gespeicherte Prozedur (Stored Procedure) "sp_hel_revlogin" in der Master-Datenbank erzeugt. Die Ausgabe dieser Stored Procedure ist wiederum ein SQL Script, das auf dem zweiten Server zur Anlage der Benutzer ausgeführt werden kann.
2. Automatisierung
Um nun die SQL-Server-Logins regelmäßig zu sichern, schreibt man eine kleine CMD-Datei zum Aufruf der Stored Procedure mittels SQLCMD.
Beispiel:
sqlcmd -E -S "PRODUKTIV" -d master -Q "exec sp_help_revlogin" -o "C:\SQL\Logins.sql"
Diese kann man dann per Scheduled Task regelmäßig aufrufen. Dabei bitte darauf achten, dass der im Scheduled Task hinterlegte ausführende Benutzer entsprechende Rechte hat.
In einer automatisierten Hochverfügbarkeitslösung müssen nun die SQL-Server-Logins auch auf der Standby Instanz wieder hergestellt werden. Dazu legt man die Ausgabedatei des sichernden Kommandos (Parameter "-o" im Programmaufruf, im obigen Beispiel "-o C:\SQL\Logins.sql") auf eine Netzwerkfreigabe (UNC-Angabe, nicht verbundenes Laufwerk). Das dadurch entstandene SQL Script zur Anlage der SQL-Server-Logins wird nun auf dem Standby Server ebenfalls in einem Scheduled Task mittels CMD Datei aufgerufen.
Beispiel:
sqlcmd -E -S "STANDBY" -d master -i "C:\SQL\Logins.sql" -o "C:\SQL\LoginFehler.txt"
Damit werden dann die SQL-Server-Logins angelegt. Dabei eventuell ausgegebene Fehler (z. B. Login existiert bereits - wird mit Sicherheit auftreten) werden dann in die Ausgabedatei, oben "C:\SQL\LoginFehler.txt" geschrieben.
Sollten Sie dabei Unterstützung benötigen, steht Ihnen unserer Telefon-Support gern zur Verfügung, in Berlin unter +49 30 435500-20 und in Karlsruhe unter +49 721 27662-550.