Datenbanken erstellen und SQL-Befehle absetzen mit PowerShell

Dazu führt man in der PowerShell-Konsole folgenden Befehl aus:

Install-Module -Name SqlServer

Sollte bereits eine ältere Version vorhanden sein, so lässt sich diese mit

Install-Module -Name SqlServer -AllowClobber

aktualisieren.

Falls man über keine administrativen Rechte verfügt, kann man das Modul SqlServer auch im Kontext des aktuellen Benutzers hinzufügen:

Install-Module -Name SqlServer -Scope CurrentUser

Praktische Anwendung: Temporäre AD-Gruppenmitgliedschaft

Um einen Eindruck zu vermitteln, für welche Aufgaben der SQL-Zugriff in PowerShell geeignet sein könnte, setze ich hier eine einfache Lösung für die temporäre Mitglied­schaft von Benutzern in Gruppen des Active Directory um.

Windows Server bietet ein solches Feature bekanntlich seit der Version 2016 selbst (siehe dazu mein Script für eine PowerShell-GUI, um Benutzer vorübergehend in eine AD-Gruppe aufzunehmen). Wenn Unter­nehmen aber noch nicht bereit sind, die Gesamt- und Funktions­ebene einer Domäne auf 2016 anzuheben, dann könnte man stattdessen vorgehen wie im Folgenden beschrieben.

Microsoft SQL Server speichert dabei die Daten der User, welche einer Gruppe temporär zugewiesen werden, sowie den dafür vorgesehenen Zeitraum. Nach Ablauf dieser Frist werden die Konten wieder aus der Datenbank entfernt.

Mit SQL Server verbinden, DB erstellen

Bevor man loslegen kann, muss man das Modul mit

Import-Module SqlServer

importieren.

Im ersten Schritt verbindet man sich zum SQL Server und legt eine Datenbank sowie die erforderliche Tabelle an.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") $serverObj = new-object Microsoft.SqlServer.Management.Smo.Server("EX01.firma.local") $databaseObj = new-object Microsoft.SqlServer.Management.Smo.Database ($serverObj, "powershellDB") $databaseObj.Create()

Die beiden mittleren Zeilen erzeugen ein Server- bzw. ein Database-Objekt, als Parameter werden die Namen des SQL Servers und der Datenbank mitgegeben. Anschließend wird die Datenbank erstellt.

Damit nun Daten in die Datenbank geschrieben werden können, lege ich eine entsprechende Tabelle mit den benötigten Attributen an. Dazu formuliere ich ein SQL-Statement und übergebe dieses neben Username und Passwort an Invoke-SQLcmd.

$insertquery=" CREATE TABLE Users ( [Zeit] varchar(MAX) not null, [Gruppe] varchar(MAX) not null, [Benutzername] varchar(MAX) not null, ) GO " Invoke-SQLcmd -ServerInstance 'EX01.firma.local' -query $insertquery ` -U sa -P Hallo123 -Database powershellDB

Je nach Anforderung kann man unter CREATE TABLE natürlich beliebige Spalten anlegen. Nun lassen sich Daten in die Datenbank schreiben und wieder auslesen. Dies kann wie folgt geschehen:

#Zeit auslesen + 2 Stunden als Test $timetolive = (Get-Date).AddHours(2).ToString("MM/dd/yyyy HH:mm") $Tag = (Get-Date).ToString("MM/dd/yyyy HH:mm") #Parameter angeben $Groupmember = 'BENUTZERNAME' $Group = "GRUPPENNAME“ #Gruppenmitgliedschaft setzen Add-ADGroupMember -Identity $Group -Members $Groupmember #Daten an SQL-Datenbank weitergeben $insertquery=" INSERT INTO [dbo].[Users] ([Zeit] ,[Gruppe] ,[Benutzername]) VALUES ('$timetolive' ,'$Group' ,'$Groupmember') GO " Invoke-SQLcmd -ServerInstance 'EX01.firma.local' -query $insertquery ` -U sa -P Hallo123 -Database powershellDB

In meinem Beispiel bekommt der Benutzer mit der Variable $timetolive ein Zeitfenster von zwei Stunden ($timetolive), währenddessen er die ent­sprechenden Gruppen­berechtigungen nutzen darf. Dieser Wert ist natürlich beliebig anpassbar. Ich würde aber immer empfehlen, diesen auf Stundenbasis zu vergeben, auch dann, wenn der User diese Berechtigung vielleicht auch für Tage oder Woche behalten darf.

Add-ADGroupMember nimmt dann den User in die gewünschte Gruppe auf. Gleichzeitig über­nehmen wir diesen Vorgang in unsere SQL-Datenbank. Wenn man den Beispiel-Code in ein produktiv genutztes Script aufnimmt, dann sollte man natürlich den Erfolg von Add-ADGroupMember prüfen, bevor man in die Datenbank schreibt.

Möchten wir nun sehen, was wir mit unserem vorherigen Befehl in die Datenbank geschrieben haben, dann ist dies mit folgendem Befehl möglich:

$SQLOutput = read-sqltabledata -ServerInstance 'EX01.firma.local' `
-Databasename powershellDB -schemaname "dbo" -tablename "Users" `
-columnname Benutzername,Zeit,Gruppe -outputas datatable

Wir erkennen, dass unser Beispiel­benutzer die Berechtigungen einer Gruppe erhalten hat und wir sehen den Zeitstempel, für wie lange die Mitgliedschaft der Gruppe gültig wäre.

Eintrag aus der Datenbank löschen

Nun wird der User aber nicht wie unter Server 2016 automatisch aus der Gruppe entfernt, wenn die gesetzte Zeit verstrichen ist. Hierfür kann der Administrator sich jedoch eine geplante Aufgabe bauen, um eine gewisse Automatisierung zu erzielen.

foreach($SQLOutputs in $SQLOutput){ if($Tag -gt $SQLOutputs.Zeit){ Remove-ADGroupMember -Identity $SQLOutputs.Gruppe ` -Members $SQLOutputs.Benutzername $insertquery2=" DELETE FROM Users WHERE Benutzername='$SQLOutputs.Benutzername' GO " Invoke-SQLcmd -ServerInstance 'EX01.firma.local' -query $insertquery2 ` -U sa -P Hallo123 -Database powershellDB } }

Für solche Fälle eignet sich eine Foreach-Schleife, weil davon auszugehen ist, dass die Tabelle später im praktischen Einsatz mehrere Benutzer enthalten wird, die irgendwelche Gruppen­berechtigung mit unter­schiedlichen Zeit­stempeln bekommen haben.

Auch hier sind wieder 2 Aktionen erforderlich. Zum einen muss die Mitglied­schaft in der Gruppe des Active Direktory beendet werden, zum anderen muss der Eintrag in der SQL-Datenbank diese Aktion widerspiegeln. Ich habe mich in diesem Beispiel dazu entschieden, den Benutzer aus der Datenbank zu löschen.