Überwachen von SQL Server-Anmeldungen und Serverrollen mit PowerShell

PROBLEM

Eine der vielen Rollen, die SQL Server-Datenbankadministratoren spielen müssen, ist die Rolle der “Sicherheitspolizei” für die zur Verfügung stehenden Instanzen. Wenn wir dutzende oder hunderte von Instanzen betreuen, wäre es dann nicht hilfreich zu wissen, welche Anmeldungen in unseren Instanzen erstellt werden und welche Serverrollen sie derzeit haben? Auf diese Weise können wir einen ersten Blick auf ein bestimmtes Login werfen, das sich entweder “nicht richtig anfühlt”, einfach nicht in der Instanz sein soll oder dem eine bestimmte Rolle zugewiesen ist. Einer der häufigsten Fehler besteht darin, sich für einzelne Benutzer mit der Sysadmin-Serverrolle anzumelden.
Mit dem hier vorgestellten PowerShell-Skript können diese Informationen für die Analyse an einem Ort konzentriert werden.

LÖSUNG

Dieses Modul sammelt Informationen zu den Anmeldungen aller angegebenen Instanzen. Es stellt eine Verbindung zu jedem Server in der inventory.MasterServerList her und erfasst die Daten für jede dieser Instanzen. Die Verbindung zu jeder SQL Server-Instanz basiert auf den Einstellungen in der inventory.MasterServerList– Tabelle.

VORAUSSETZUNGEN

Um dieses Modul verwenden zu können, müssen Sie die Kernobjekte erstellen, die Sie hier finden können.

DATENBANKOBJEKTE

Für dieses spezielle Modul werden zwei Tabellen erstellt. In der ersten Tabelle werden die Informationen zu den in der Instanz gefundenen Anmeldungen gespeichert und in der zweiten Tabelle werden die verschiedenen Rollen gespeichert, die jeder bestimmten Anmeldung zugewiesen wurden.

TABELLEN

inventory.Logins – Speichert die Informationen der Anmeldungen, die in jeder SQL Server-Instanz gefunden wurden.

loginId: Primärschlüssel der Tabelle
serverId: Die ServerId ist wieder mit der inventory.MasterServerList verknüpft
name: Anmeldename
type: Art der Anmeldung [SQL- oder Windows-Anmeldung]
create_date: Datum an dem die Anmeldung erstellt wurde
modify_date: Datum an dem die Anmeldung zuletzt geändert wurde
default_database_name: Name der Datenbank, zu der der Benutzer eine Verbindung herstellen wird, wenn in einer Verbindungszeichenfolge kein Datenbankname angegeben wird
is_disabled: Gibt an, ob der Benutzer deaktiviert ist oder nicht.
data_colletion_timestamp: Gibt den Zeitpunkt an, zu dem das PowerShell-Skript ausgeführt wurde, um die Tabelle zu füllen

inventory.LoginsRoles – Speichert die Serverrollen, die jedem bestimmten Login zugewiesen sind.

loginId: Referenzwert, der auf den Primärschlüssel der Tabelle inventory.Logins verweist
server_role: Name der Rolle, die dem Login zugewiesen ist
data_collection_timestamp: Gibt den Zeitpunkt an, zu dem das PowerShell-Skript ausgeführt wurde, um die Tabelle zu füllen

POWERSHELL-SKRIPT

Das PowerShell-Skript, das die oben genannten Objekte erstellt und Daten in die Tabellen inventory.Logins und inventory.LoginsRoles einfügt, heißt:

Get-MSSQL-Instance-Logins.ps1

Das Skript verfügt über einige Möglichkeiten, mit denen Sie überprüfen können, ob einige Schlüsselelemente fehlen, damit das Skript erfolgreich ausgeführt werden kann. Beispielsweise wird bestätigt, dass die Tabelle “inventory.MasterServerList” vorhanden ist und mindestens eine aktive Instanz registriert ist, um mit etwas arbeiten zu können.

Wir haben uns zum Speichern unserer Objekte für “C:\temp\monitoringTest” entschieden. Sie können allerdings auch jeden gewünschten anderen Ordner verwenden. Wenn Sie den Speicherort des zentralen Ordners ändern, muss die erste Zeile im folgenden PowerShell-Skript bearbeitet werden, um den neuen Speicherort des Ordners anzugeben.

Dein PowerShell-Skript sollte wie folgt aussehen:

Get-Content "C:\temp\monitoringTest\Settings.ini" | foreach-object -begin {$h=@{}} -process { $k = [regex]::split($_,'='); if(($k[0].CompareTo("") -ne 0) -and ($k[0].StartsWith("[") -ne $True)) { $h.Add($k[0], $k[1]) } }
$server = $h.Get_Item("centralServer")
$inventoryDB = $h.Get_Item("inventoryDB")

if($server.length -eq 0){
Write-Host "You must provide a value for the 'centralServer' in your Settings.ini file!!!" -BackgroundColor Red
exit
}
if($inventoryDB.length -eq 0){
Write-Host "You must provide a value for the 'inventoryDB' in your Settings.ini file!!!" -BackgroundColor Red
exit
}

$mslExistenceQuery = "
SELECT Count(*) FROM dbo.sysobjects where id = object_id(N'[inventory].[MasterServerList]') and OBJECTPROPERTY(id, N'IsTable') = 1
"
$result = Invoke-Sqlcmd -Query $mslExistenceQuery -Database $inventoryDB -ServerInstance $server -ErrorAction Stop

if($result[0] -eq 0){
Write-Host "The table [inventory].[MasterServerList] wasn't found!!!" -BackgroundColor Red 
exit
}

$enoughInstancesInMSLQuery = "
SELECT COUNT(*) FROM inventory.MasterServerList WHERE is_active = 1
"
$result = Invoke-Sqlcmd -Query $enoughInstancesInMSLQuery -Database $inventoryDB -ServerInstance $server -ErrorAction Stop

if($result[0] -eq 0){
Write-Host "There are no active instances registered to work with!!!" -BackgroundColor Red 
exit
}

if ($h.Get_Item("username").length -gt 0 -and $h.Get_Item("password").length -gt 0) {
$username = $h.Get_Item("username")
$password = $h.Get_Item("password")
}

#Function to execute queries (depending on if the user will be using specific credentials or not)
function Execute-Query([string]$query,[string]$database,[string]$instance,[int]$trusted){
if($trusted -eq 1){ 
try{
Invoke-Sqlcmd -Query $query -Database $database -ServerInstance $instance -ErrorAction Stop
}
catch{
[string]$message = $_
$errorQuery = "INSERT INTO monitoring.ErrorLog VALUES((SELECT serverId FROM inventory.MasterServerList WHERE CASE instance WHEN 'MSSQLSERVER' THEN server_name ELSE CONCAT(server_name,'\',instance) END = '$($instance)'),'Get-MSSQL-Instance-Logins','"+$message.replace("'","''")+"',GETDATE())"
Invoke-Sqlcmd -Query $errorQuery -Database $inventoryDB -ServerInstance $server -ErrorAction Stop
}
}
else{
try{
Invoke-Sqlcmd -Query $query -Database $database -ServerInstance $instance -Username $username -Password $password -ErrorAction Stop
}
catch{
[string]$message = $_
$errorQuery = "INSERT INTO monitoring.ErrorLog VALUES((SELECT serverId FROM inventory.MasterServerList WHERE CASE instance WHEN 'MSSQLSERVER' THEN server_name ELSE CONCAT(server_name,'\',instance) END = '$($instance)'),'Get-MSSQL-Instance-Logins','"+$message.replace("'","''")+"',GETDATE())"
Invoke-Sqlcmd -Query $errorQuery -Database $inventoryDB -ServerInstance $server -ErrorAction Stop
}
}
}

#################################
#Logins inventory table creation#
#################################
$loginsInventoryTableQuery = "
IF NOT EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'[inventory].[Logins]') and OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN
CREATE TABLE [inventory].[Logins](
[loginId] [INT] NOT NULL PRIMARY KEY IDENTITY,
[serverId] [INT]NOT NULL,
[name] [VARCHAR](128) NOT NULL,
[type] [VARCHAR](32) NOT NULL,
[create_date] [DATETIME] NOT NULL,
[modify_date] [DATETIME] NOT NULL,
[default_database_name] [VARCHAR](128) NOT NULL,
[is_disabled] [BIT] NOT NULL,
[data_collection_timestamp] [DATETIME] NOT NULL

CONSTRAINT UQ_LoginsInventory UNIQUE (serverId,name),
CONSTRAINT FK_LoginsInventory_MasterServerList FOREIGN KEY (serverId) REFERENCES inventory.MasterServerList(serverId) ON DELETE NO ACTION ON UPDATE NO ACTION,

) ON [PRIMARY]
END
"
Execute-Query $loginsInventoryTableQuery $inventoryDB $server 1


#############################
#Logins roles table creation#
#############################
$loginsRolesTableQuery = "
IF NOT EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'[inventory].[LoginsRoles]') and OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN
CREATE TABLE [inventory].[LoginsRoles](
[loginId] [INT] NOT NULL,
[server_role] [VARCHAR](128) NOT NULL,
[data_collection_timestamp] [DATETIME] NOT NULL
) ON [PRIMARY]
END
"
Execute-Query $loginsRolesTableQuery $inventoryDB $server 1

#TRUNCATE both tables to always store a fresh copy of the information from all the instances
Execute-Query "TRUNCATE TABLE inventory.Logins" $inventoryDB $server 1
Execute-Query "TRUNCATE TABLE inventory.LoginsRoles" $inventoryDB $server 1

#Select the instances from the Master Server List that will be traversed
$instanceLookupQuery = "
SELECT
serverId,
trusted,
CASE instance 
WHEN 'MSSQLSERVER' THEN server_name 
ELSE CONCAT(server_name,'\',instance)
END AS 'instance',
CASE instance 
WHEN 'MSSQLSERVER' THEN ip 
ELSE CONCAT(ip,'\',instance)
END AS 'ip',
CONCAT(ip,',',port) AS 'port'
FROM inventory.MasterServerList
WHERE is_active = 1
"
$instances = Execute-Query $instanceLookupQuery $inventoryDB $server 1

#For each instance, fetch the desired information
$loginsInformationQuery = "
SELECT 
name,
type_desc AS 'type',
create_date,
modify_date,
default_database_name,
is_disabled
FROM sys.server_principals
WHERE type not in ('G','R','C')
AND name NOT LIKE '%##MS_Policy%'
"

foreach ($instance in $instances){
if($instance.trusted -eq 'True'){$trusted = 1}else{$trusted = 0}
$sqlInstance = $instance.instance

#Go grab the Logins information for the instance
Write-Host "Fetching Logins information from instance" $instance.instance

#Special logic for cases where the instance isn't reachable by name
try{
$results = Execute-Query $loginsInformationQuery "master" $sqlInstance $trusted
}
catch{
$sqlInstance = $instance.ip
[string]$message = $_
$query = "INSERT INTO monitoring.ErrorLog VALUES("+$instance.serverId+",'Get-MSSQL-Instance-Logins','"+$message.replace("'","''")+"',GETDATE())"
Execute-Query $query $inventoryDB $server 1

try{ 
$results = Execute-Query $loginsInformationQuery "master" $sqlInstance $trusted
}
catch{
$sqlInstance = $instance.port
[string]$message = $_
$query = "INSERT INTO monitoring.ErrorLog VALUES("+$instance.serverId+",'Get-MSSQL-Instance-Logins','"+$message.replace("'","''")+"',GETDATE())"
Execute-Query $query $inventoryDB $server 1

try{
$results = Execute-Query $loginsInformationQuery "master" $sqlInstance $trusted
}
catch{
[string]$message = $_
$query = "INSERT INTO monitoring.ErrorLog VALUES("+$instance.serverId+",'Get-MSSQL-Instance-Logins','"+$message.replace("'","''")+"',GETDATE())"
Execute-Query $query $inventoryDB $server 1
}
}
}

#Perform the INSERT in the inventory.Logins only if it returns information
if($results.Length -ne 0){
$rolesInsertQuery = @()

#Build the insert statement
$insert = "INSERT INTO inventory.Logins VALUES"
foreach($result in $results){ 
if($result['is_disabled'] -eq 'true'){$is_disabled = 1}else{$is_disabled = 0}

$insert += "
(
'"+$instance.serverId+"',
'"+$result['name']+"',
'"+$result['type']+"',
'"+$result['create_date']+"',
'"+$result['modify_date']+"',
'"+$result['default_database_name']+"',
"+$is_disabled+",
GETDATE()
),
"
#For each login returned, go grab its server roles from the instance
$loginRolesInformationQuery = "
SELECT 
ISNULL(SUSER_NAME(srm.role_principal_id),'public') AS 'server_role'
FROM sys.server_principals sp
LEFT JOIN sys.server_role_members srm ON sp.name = SUSER_NAME(srm.member_principal_id)
WHERE sp.name = '$($result['name'])'
"
$roles = Execute-Query $loginRolesInformationQuery "master" $sqlInstance 1

foreach($role in $roles){
$insertLoginsRolesQuery = "
INSERT INTO inventory.LoginsRoles VALUES((SELECT loginId FROM inventory.Logins WHERE serverId = $($instance.ServerId) AND name = '$($result['name'])'),'$($role.server_role)',GETDATE())
"
$rolesInsertQuery += $insertLoginsRolesQuery 
}
}
Execute-Query $insert.Substring(0,$insert.LastIndexOf(',')) $inventoryDB $server 1

#Perform the inserts of roles in the inventory.LoginsRoles table after the logins exist in the inventory.Logins table
for($i = 0; $i -lt $rolesInsertQuery.length; $i++){
Execute-Query $rolesInsertQuery[$i] $inventoryDB $server 1
}
}
}

Write-Host "Done!"

ANWENDUNG

Öffnen Sie zunächst den Ordner, in dem Sie die Dateien erstellt haben und führen das PowerShell-Skript wie folgt aus:

Option 1 - Klicken Sie mit der rechten Maustaste auf Get-MSSQL-Instance-Logins.ps1 und wählen PowerShell ausführen
Option 2 - Öffnen Sie die Kommandozeile und navigieren zu dem Ordner, in dem Sie die obige Datei gespeichert und ausgeführt haben

C:\temp\monitoringTest\Get-MSSQL-Instance-Logins.ps1

Überprüfen Sie die Erstellung von Datenbanken und Objekten

Nach dem Ausführen des PowerShell-Skripts können wir die erstellten Objekte anzeigen:

Mainzer Datenfabrik - Überwachen von SQL Server-Anmeldungen und Serverrollen mit PowerShell

Wenn wir die Tabellen inventory.Logins und inventory.LoginsRoles abfragen, können wir die gesammelten Daten sehen.

Auf Fehler prüfen

Um nach Fehlern zu suchen, benutzen Sie folgende Abfrage der Tabelle monitoring.ErrorLog:

SELECT *
FROM monitoring.ErrorLog
WHERE script = 'Get-MSSQL-Instance-Logins'

Wenn Sie wissen möchten welche SQL Server-Instanz die Fehler erhalten hat, müssen Sie die Abfrage wie folgt ausgeben:

SELECT 
   CASE WHEN msl.instance = 'MSSQLSERVER' 
      THEN msl.server_name 
      ELSE CONCAT(msl.server_name,'\',msl.instance) 
      END AS instance,
   e.script,
   e.message,
   e.error_timestamp
FROM monitoring.ErrorLog e
JOIN inventory.MasterServerList msl ON msl.serverId = e.serverId
WHERE e.script = 'Get-MSSQL-Instance-Logins

Weitere nützliche Abfragen

Welche Anmeldungen haben Sysadmin- oder Serveradmin-Serverrollen?

SELECT 
   CASE WHEN msl.instance = 'MSSQLSERVER' 
      THEN msl.server_name 
      ELSE CONCAT(msl.server_name,'\',msl.instance) 
      END AS instance, 
   l.name,
   lr.server_role
FROM inventory.Logins l
JOIN inventory.LoginsRoles lr ON l.loginId = lr.loginId
JOIN inventory.MasterServerList msl ON msl.serverId = l.serverId
WHERE server_role IN ('sysadmin','serveradmin')

Welche Serverrolle hat jeder Login?

SELECT 
   CASE WHEN msl.instance = 'MSSQLSERVER' 
      THEN msl.server_name 
      ELSE CONCAT(msl.server_name,'\',msl.instance) 
      END AS instance, 
   l.name AS 'login',
   ISNULL((SELECT 1 FROM inventory.LoginsRoles WHERE loginId = l.loginId AND server_role = 'sysadmin'),0) AS 'sysadmin',
   ISNULL((SELECT 1 FROM inventory.LoginsRoles WHERE loginId = l.loginId AND server_role = 'serveradmin'),0) AS 'serveradmin',
   ISNULL((SELECT 1 FROM inventory.LoginsRoles WHERE loginId = l.loginId AND server_role = 'securityadmin'),0) AS 'securityadmin',
   ISNULL((SELECT 1 FROM inventory.LoginsRoles WHERE loginId = l.loginId AND server_role = 'processadmin'),0) AS 'processadmin',
   ISNULL((SELECT 1 FROM inventory.LoginsRoles WHERE loginId = l.loginId AND server_role = 'setupadmin'),0) AS 'setupadmin',
   ISNULL((SELECT 1 FROM inventory.LoginsRoles WHERE loginId = l.loginId AND server_role = 'bulkadmin'),0) AS 'bulkadmin',
   ISNULL((SELECT 1 FROM inventory.LoginsRoles WHERE loginId = l.loginId AND server_role = 'dbcreator'),0) AS 'dbcreator'
FROM inventory.Logins l
JOIN inventory.MasterServerList msl ON msl.serverId = l.serverId

Sie sollten nun dazu in der Lage sein, SQL Server-Anmeldungen und ihre jeweiligen Serverrollen abrufen und überprüfen zu können.
Die hier besprochenen Inhalte bilden allerdings nur eine Basis und lassen noch viele weitere Möglichkeiten offen, sich darüber hinaus mit der Überwachung von SQL Server-Anmeldungen zu beschäftigen.