Public/Set-mssSsrsConfiguration.TempPoint.ps1
|
<#
.SYNOPSIS Konfiguriert SQL Server Reporting Services (SSRS) vollautomatisch. Unterstützt lokale und Remote-Installation sowie AlwaysOn-Umgebungen. .DESCRIPTION Führt eine vollständige SSRS-Erst- oder Neukonfiguration durch. Unterstützt Native Mode und SharePoint Integrated Mode (automatische Erkennung). Konfigurierte Bereiche (einzeln deaktivierbar): - Dienstkonto (SetWindowsServiceIdentity) - Datenbank (anlegen, Rechte vergeben, Verbindung setzen) - URLs (ReportServer Web Service + Portal, nur Native Mode) - Verschlüsselungsschlüssel (BackupEncryptionKey) Bei AlwaysOn Availability Groups (AG) wird der Datenbankserver automatisch als Listener erkannt; die DB-Erstellung erfolgt auf dem Primary-Replikat, die Verbindung wird auf den Listener konfiguriert. .PARAMETER ComputerName SSRS-Server (lokal oder remote). Standard: $env:COMPUTERNAME. .PARAMETER InstanceName SSRS-Instanzname. Standard: 'MSSQLSERVER'. .PARAMETER DatabaseServer SQL Server-Instanz oder AG-Listener für die ReportServer-Datenbank. Standard: $ComputerName. .PARAMETER DatabaseName Name der ReportServer-Hauptdatenbank. Standard: 'ReportServer'. .PARAMETER ReportServerUrl URL für den ReportServer Web Service. Standard: 'http://+:80/ReportServer' .PARAMETER ReportsUrl URL für den Reports-Manager / Web Portal. Standard: 'http://+:80/Reports' .PARAMETER ServiceAccount Windows-Dienstkonto für SSRS (z.B. 'DOMAIN\user' oder 'NT SERVICE\...'). .PARAMETER ServiceAccountPassword Kennwort für -ServiceAccount (SecureString). Nicht nötig bei Dienstkonten. .PARAMETER DatabaseAuthType Authentifizierung für die DB-Verbindung: 'Windows' (Standard) oder 'SQL'. .PARAMETER DatabaseCredential PSCredential für SQL-Authentifizierung (nur bei -DatabaseAuthType SQL). .PARAMETER EncryptionKeyFile Pfad für das Encryption Key Backup (.snk). Ohne Angabe wird die Datei im OutputPath mit Namen 'SsrsEncryptionKey_<Instanz>_<Datum>.snk' abgelegt. .PARAMETER EncryptionKeyPassword Kennwort zum Schutz der Schlüsseldatei (SecureString). Pflicht wenn ein Backup erstellt werden soll. .PARAMETER SkipDatabase Datenbankkonfiguration überspringen. .PARAMETER SkipUrls URL-Konfiguration überspringen (nur Native Mode). .PARAMETER SkipServiceAccount Dienstkonto-Konfiguration überspringen. .PARAMETER SkipEncryptionKeyBackup Encryption Key Backup überspringen. .PARAMETER Credential PSCredential für die WinRM-Verbindung (nur bei Remote-Betrieb). .PARAMETER OutputPath Ausgabeverzeichnis für den Konfigurationsbericht und ggf. Schlüsseldatei. Standard: Get-mssDefaultOutputPath. .PARAMETER ContinueOnError Bei Fehler mit nächstem Schritt fortfahren (selten verwendet). .PARAMETER EnableException Ausnahmen sofort auslösen. .PARAMETER Confirm Bestätigung vor der Ausführung anfordern. .PARAMETER WhatIf Zeigt, was passieren würde, ohne Änderungen vorzunehmen. .EXAMPLE Set-mssSsrsConfiguration .EXAMPLE Set-mssSsrsConfiguration -ComputerName "SSRS01" -DatabaseServer "AG_Listener" .NOTES Voraussetzungen: dbatools, Invoke-mssLogging, Get-mssDefaultOutputPath WMI-Namespace: root\Microsoft\SqlServer\ReportServer\<Instanz>\v<Version>\Admin #> function Set-mssSsrsConfiguration { [CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'None')] [OutputType([PSCustomObject])] param ( [Parameter(Mandatory = $false)] [string]$ComputerName = $env:COMPUTERNAME, [Parameter(Mandatory = $false)] [string]$InstanceName = 'MSSQLSERVER', [Parameter(Mandatory = $false)] [string]$DatabaseServer, [Parameter(Mandatory = $false)] [string]$DatabaseName = 'ReportServer', [Parameter(Mandatory = $false)] [string]$ReportServerUrl = 'http://+:80/ReportServer', [Parameter(Mandatory = $false)] [string]$ReportsUrl = 'http://+:80/Reports', [Parameter(Mandatory = $false)] [string]$ServiceAccount, [Parameter(Mandatory = $false)] [System.Security.SecureString]$ServiceAccountPassword, [Parameter(Mandatory = $false)] [ValidateSet('Windows', 'SQL')] [string]$DatabaseAuthType = 'Windows', [Parameter(Mandatory = $false)] [System.Management.Automation.PSCredential]$DatabaseCredential, [Parameter(Mandatory = $false)] [string]$EncryptionKeyFile, [Parameter(Mandatory = $false)] [System.Security.SecureString]$EncryptionKeyPassword, [Parameter(Mandatory = $false)] [switch]$SkipDatabase, [Parameter(Mandatory = $false)] [switch]$SkipUrls, [Parameter(Mandatory = $false)] [switch]$SkipServiceAccount, [Parameter(Mandatory = $false)] [switch]$SkipEncryptionKeyBackup, [Parameter(Mandatory = $false)] [System.Management.Automation.PSCredential]$Credential, [Parameter(Mandatory = $false)] [string]$OutputPath = (Get-mssDefaultOutputPath), [Parameter(Mandatory = $false)] [switch]$ContinueOnError, [Parameter(Mandatory = $false)] [switch]$EnableException ) begin { $functionName = $MyInvocation.MyCommand.Name if (-not (Get-Module -ListAvailable -Name dbatools)) { $errMsg = "dbatools-Modul nicht gefunden." Invoke-mssLogging -Message $errMsg -FunctionName $functionName -Level "ERROR" throw $errMsg } Invoke-mssLogging -Message "Starte $functionName auf $ComputerName" -FunctionName $functionName -Level "INFO" $result = [PSCustomObject]@{ ComputerName = $ComputerName InstanceName = $InstanceName SsrsMode = $null SsrsVersion = $null DatabaseServer = $DatabaseServer IsAgListener = $false AgPrimaryNode = $null ServiceAccountResult = 'Skipped' DatabaseResult = 'Skipped' UrlResult = 'Skipped' EncryptionKeyResult = 'Skipped' OverallStatus = 'Unknown' Message = $null ReportPath = $null } if (-not $DatabaseServer) { $result.DatabaseServer = $ComputerName } $timestamp = Get-Date -Format 'yyyy-MM-dd HH:mm:ss' $datestamp = Get-Date -Format 'yyyy-MM-dd' $safeComp = $ComputerName -replace '[\\/:*?"<>|]', '_' $safeInst = $InstanceName -replace '[\\/:*?"<>|]', '_' $isLocal = $ComputerName -in @($env:COMPUTERNAME, 'localhost', '127.0.0.1', '.') function _SecureToPlain([System.Security.SecureString]$s) { if (-not $s) { return '' } [System.Runtime.InteropServices.Marshal]::PtrToStringAuto( [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($s)) } $logLines = [System.Collections.Generic.List[string]]::new() function _Log($msg, $sev = 'INFO') { # Korrektur 1: Fehlende schließende Klammer ergänzt $logLines.Add("[$(Get-Date -Format 'HH:mm:ss')] [$sev] $msg") switch ($sev) { 'ERROR' { Invoke-mssLogging -Message $msg -FunctionName $functionName -Level "ERROR" } 'WARNING' { Invoke-mssLogging -Message $msg -FunctionName $functionName -Level "WARNING" } default { Invoke-mssLogging -Message $msg -FunctionName $functionName -Level "VERBOSE" } } } } process { try { # CIM-Session $cimBase = @{ ErrorAction = 'Stop' } $cimSession = $null if ($isLocal) { _Log "Lokaler Betrieb - kein WinRM erforderlich." } else { _Log "Remote-Betrieb: Verbinde zu '$ComputerName' via WsMan..." $sessionOpts = New-CimSessionOption -Protocol Wsman $cimParams = @{ ComputerName = $ComputerName; SessionOption = $sessionOpts } if ($Credential) { $cimParams['Credential'] = $Credential } $cimSession = New-CimSession @cimParams -ErrorAction Stop $cimBase['CimSession'] = $cimSession _Log "CIM-Session hergestellt." } # SSRS WMI-Namespace $wmiConfig = $null $wmiVersion = $null $rsNamespace = 'root\Microsoft\SqlServer\ReportServer' $instNs = "$rsNamespace\$InstanceName" $versionNs = Get-CimInstance @cimBase -Namespace $instNs -ClassName '__NAMESPACE' -ErrorAction Stop | Where-Object { $_.Name -like 'v*' } | Sort-Object Name -Descending | Select-Object -First 1 -ExpandProperty Name if (-not $versionNs) { throw "Kein SSRS-Namespace unter '$instNs' gefunden." } $wmiVersion = $versionNs $adminNs = "$instNs\$versionNs\Admin" _Log "WMI-Namespace: $adminNs" $wmiConfig = Get-CimInstance @cimBase -Namespace $adminNs -ClassName 'MSReportServer_ConfigurationSetting' -ErrorAction Stop | Select-Object -First 1 if (-not $wmiConfig) { throw "MSReportServer_ConfigurationSetting nicht gefunden." } $isSharePoint = [bool]$wmiConfig.IsSharePointIntegrated $result.SsrsMode = if ($isSharePoint) { 'SharePointIntegrated' } else { 'NativeMode' } $result.SsrsVersion = $wmiVersion $result.InstanceName = $wmiConfig.InstanceName Write-Host "[$ComputerName] SSRS $wmiVersion | $($result.SsrsMode) | Instanz: $($result.InstanceName)" -ForegroundColor Cyan _Log "SSRS $wmiVersion | Modus: $($result.SsrsMode) | Dienstkonto: $($wmiConfig.WindowsServiceIdentityActual)" function _InvokeCim($method, [hashtable]$arguments, $desc) { _Log "WMI ▶ $method - $desc" $r = Invoke-CimMethod @cimBase -Namespace $adminNs -ClassName 'MSReportServer_ConfigurationSetting' -MethodName $method -Arguments $arguments -ErrorAction Stop if ($r.HRESULT -ne 0) { throw "$method (HRESULT 0x$($r.HRESULT.ToString('X8'))): $($r.Error)" } return $r } # AG-Listener-Erkennung $dbCreateServer = $result.DatabaseServer $isAgListener = $false $agPrimaryNode = $null if (-not $SkipDatabase) { try { $listenerCheck = Invoke-DbaQuery -SqlInstance $result.DatabaseServer -ErrorAction SilentlyContinue -Query @" SELECT ag.name AS AgName, agl.dns_name AS ListenerName, ar.replica_server_name AS PrimaryReplica FROM sys.availability_group_listeners agl JOIN sys.availability_groups ag ON ag.group_id = agl.group_id JOIN sys.dm_hadr_availability_group_states ags ON ags.group_id = ag.group_id JOIN sys.availability_replicas ar ON ar.group_id = ag.group_id AND ar.replica_id = ags.primary_replica WHERE agl.dns_name = @@SERVERNAME OR UPPER(agl.dns_name) = UPPER('$($result.DatabaseServer.Split('\')[0])') "@ if ($listenerCheck) { $isAgListener = $true $agPrimaryNode = $listenerCheck.PrimaryReplica | Select-Object -First 1 $result.IsAgListener = $true $result.AgPrimaryNode = $agPrimaryNode $dbCreateServer = if ($result.DatabaseServer -match '\\') { "$agPrimaryNode\$($result.DatabaseServer.Split('\')[1])" } else { $agPrimaryNode } Write-Host " ℹ AG-Listener erkannt: '$($result.DatabaseServer)' → Primary: '$agPrimaryNode'" -ForegroundColor Cyan _Log "AG-Listener '$($result.DatabaseServer)' → Primary '$agPrimaryNode'. DB-Erstellung auf: '$dbCreateServer'" } } catch { _Log "AG-Listener-Prüfung: $($_.Exception.Message) (kein Listener - wird als normaler Server behandelt)" } } $errorsOccurred = $false # 1. Dienstkonto if ($ServiceAccount -and -not $SkipServiceAccount) { Write-Host " [1/4] Dienstkonto konfigurieren: $ServiceAccount" -ForegroundColor Gray if ($PSCmdlet.ShouldProcess($ComputerName, "SSRS-Dienstkonto auf '$ServiceAccount' setzen")) { try { $useBuiltIn = $ServiceAccount -like 'NT SERVICE\*' -or $ServiceAccount -like 'NT AUTHORITY\*' -or $ServiceAccount -in @('LocalSystem', 'LocalService', 'NetworkService') $null = _InvokeCim 'SetWindowsServiceIdentity' @{ UseBuiltInAccount = $useBuiltIn Account = $ServiceAccount Password = (_SecureToPlain $ServiceAccountPassword) } "Dienstkonto '$ServiceAccount'" $result.ServiceAccountResult = 'OK' Write-Host " ✔ Dienstkonto gesetzt." -ForegroundColor Green _Log "Dienstkonto '$ServiceAccount' gesetzt." $wmiConfig = Get-CimInstance @cimBase -Namespace $adminNs -ClassName 'MSReportServer_ConfigurationSetting' -ErrorAction SilentlyContinue | Select-Object -First 1 } catch { _Log "Dienstkonto-Fehler: $($_.Exception.Message)" 'ERROR' $result.ServiceAccountResult = 'Failed' $errorsOccurred = $true if (-not $ContinueOnError -and $EnableException) { throw } } } else { $result.ServiceAccountResult = 'WhatIf' } } # 2. Datenbank if (-not $SkipDatabase) { $dbServerDisplay = if ($isAgListener) { "$($result.DatabaseServer) (Listener) → Primary: $agPrimaryNode" } else { $result.DatabaseServer } Write-Host " [2/4] Datenbank konfigurieren: $DatabaseName auf $dbServerDisplay" -ForegroundColor Gray if ($PSCmdlet.ShouldProcess($ComputerName, "ReportServer-DB '$DatabaseName' konfigurieren")) { try { $dbAuthTypeInt = if ($DatabaseAuthType -eq 'SQL') { 1 } else { 2 } $dbUser = if ($DatabaseAuthType -eq 'SQL' -and $DatabaseCredential) { $DatabaseCredential.UserName } else { '' } $dbPwd = if ($DatabaseAuthType -eq 'SQL' -and $DatabaseCredential) { _SecureToPlain $DatabaseCredential.Password } else { '' } $ssrsServiceAccount = if ($wmiConfig) { $wmiConfig.WindowsServiceIdentityActual } else { $ServiceAccount } $dbExists = $false try { $dbCheck = Invoke-DbaQuery -SqlInstance $dbCreateServer -Query "SELECT name FROM sys.databases WHERE name = N'$DatabaseName'" -ErrorAction SilentlyContinue $dbExists = $null -ne $dbCheck } catch { $dbExists = $false } if (-not $dbExists) { _Log "Datenbank '$DatabaseName' nicht vorhanden auf '$dbCreateServer' - lege an..." $genResult = _InvokeCim 'GenerateDatabaseCreationScript' @{ DatabaseName = $DatabaseName Lcid = 1033 IsSharePoint = $isSharePoint } "DB-Erstellungs-Skript generieren" Invoke-DbaQuery -SqlInstance $dbCreateServer -Query $genResult.Script -EnableException -ErrorAction Stop | Out-Null _Log "Datenbank '$DatabaseName' + '${DatabaseName}TempDB' auf '$dbCreateServer' angelegt." Write-Host " Datenbank angelegt auf: $dbCreateServer" -ForegroundColor Gray } else { _Log "Datenbank '$DatabaseName' bereits vorhanden auf '$dbCreateServer'." } $isRemoteDb = ($ComputerName.Split('.')[0].ToUpper() -ne $dbCreateServer.Split('\\')[0].Split('.')[0].ToUpper()) -or $isAgListener $rightsResult = _InvokeCim 'GenerateDatabaseRightsScript' @{ DatabaseName = $DatabaseName AccountName = $ssrsServiceAccount IsRemote = $isRemoteDb IsWindowsAccount = ($dbAuthTypeInt -eq 2) } "Rechte-Skript generieren" if ($rightsResult.Script) { Invoke-DbaQuery -SqlInstance $dbCreateServer -Query $rightsResult.Script -EnableException -ErrorAction SilentlyContinue | Out-Null _Log "DB-Rechte für '$ssrsServiceAccount' auf '$dbCreateServer' gesetzt." if ($isAgListener) { _Log "AG-Info: DB-Rechte werden durch Synchronisation auf alle Secondaries repliziert." Write-Host " ℹ AG: Rechte werden auf Secondaries synchronisiert." -ForegroundColor Gray } } $null = _InvokeCim 'SetDatabaseConnection' @{ Server = $result.DatabaseServer DatabaseName = $DatabaseName CredentialsType = $dbAuthTypeInt Username = $dbUser Password = $dbPwd } "DB-Verbindung konfigurieren (Server: $($result.DatabaseServer))" $result.DatabaseResult = 'OK' Write-Host " ✔ Datenbank konfiguriert." -ForegroundColor Green _Log "SetDatabaseConnection: Server=$($result.DatabaseServer) / DB=$DatabaseName / Auth=$DatabaseAuthType" } catch { _Log "Datenbank-Fehler: $($_.Exception.Message)" 'ERROR' $result.DatabaseResult = 'Failed' $errorsOccurred = $true if (-not $ContinueOnError -and $EnableException) { throw } } } else { $result.DatabaseResult = 'WhatIf' } } # 3. URLs (nur Native Mode) if (-not $SkipUrls) { if ($isSharePoint) { _Log "SharePoint Integrated Mode - URL-Konfiguration entfällt." 'WARNING' $result.UrlResult = 'NotApplicable' } else { Write-Host " [3/4] URLs konfigurieren..." -ForegroundColor Gray if ($PSCmdlet.ShouldProcess($ComputerName, "URLs setzen: $ReportServerUrl | $ReportsUrl")) { try { $null = _InvokeCim 'ReserveURL' @{ Application = 'ReportServerWebService' UrlString = $ReportServerUrl Lcid = 1033 } "ReportServerWebService: $ReportServerUrl" $reportsApp = if ([int]($wmiVersion -replace 'v', '') -ge 14) { 'ReportServerWebApp' } else { 'ReportManager' } $null = _InvokeCim 'ReserveURL' @{ Application = $reportsApp UrlString = $ReportsUrl Lcid = 1033 } "${reportsApp}: $ReportsUrl" # Korrektur 2: Variable mit ${} begrenzen $result.UrlResult = 'OK' Write-Host " ✔ URLs konfiguriert: $ReportServerUrl | $ReportsUrl" -ForegroundColor Green _Log "URLs: $ReportServerUrl + $ReportsUrl ($reportsApp)" } catch { _Log "URL-Fehler: $($_.Exception.Message)" 'ERROR' $result.UrlResult = 'Failed' $errorsOccurred = $true if (-not $ContinueOnError -and $EnableException) { throw } } } else { $result.UrlResult = 'WhatIf' } } } # 4. Encryption Key Backup $effectiveKeyFile = $EncryptionKeyFile if (-not $SkipEncryptionKeyBackup -and $EncryptionKeyPassword) { if (-not $effectiveKeyFile) { $effectiveKeyFile = Join-Path $OutputPath "SsrsEncryptionKey_${safeInst}_${datestamp}.snk" if (-not (Test-Path $OutputPath)) { New-Item -ItemType Directory -Path $OutputPath -Force | Out-Null } } Write-Host " [4/4] Encryption Key sichern..." -ForegroundColor Gray if ($PSCmdlet.ShouldProcess($ComputerName, "Encryption Key sichern nach '$effectiveKeyFile'")) { try { $keyResult = _InvokeCim 'BackupEncryptionKey' @{ Password = (_SecureToPlain $EncryptionKeyPassword) } "BackupEncryptionKey" $keyDir = Split-Path $effectiveKeyFile -Parent if ($keyDir -and -not (Test-Path $keyDir)) { New-Item -ItemType Directory -Path $keyDir -Force | Out-Null } [System.IO.File]::WriteAllBytes($effectiveKeyFile, $keyResult.KeyFile) $result.EncryptionKeyResult = 'OK' Write-Host " ✔ Key gesichert: $effectiveKeyFile" -ForegroundColor Green _Log "Encryption Key gesichert: '$effectiveKeyFile'" } catch { _Log "Key-Backup-Fehler: $($_.Exception.Message)" 'ERROR' $result.EncryptionKeyResult = 'Failed' $errorsOccurred = $true if (-not $ContinueOnError -and $EnableException) { throw } } } else { $result.EncryptionKeyResult = 'WhatIf' } } # Bericht schreiben if (-not (Test-Path $OutputPath)) { New-Item -ItemType Directory -Path $OutputPath -Force | Out-Null } $reportFile = Join-Path $OutputPath "SsrsConfiguration_${safeComp}_${safeInst}_${datestamp}.txt" $result.ReportPath = $reportFile $agInfo = if ($isAgListener) { "AG-Listener: $($result.DatabaseServer) → Primary: $agPrimaryNode" } else { 'Kein AG-Listener' } $lines = @( "# ================================================================" "# MSSQLTools - SSRS Konfigurationsbericht" "# Server : $ComputerName ($(if ($isLocal) { 'lokal' } else { 'remote' }))" "# Instanz : $InstanceName" "# SSRS-Modus : $($result.SsrsMode) | Version: $wmiVersion" "# DB-Server : $($result.DatabaseServer) | $agInfo" "# Erstellt : $timestamp" "# ================================================================" "" "Ergebnisse:" " Dienstkonto : $($result.ServiceAccountResult)" " Datenbank : $($result.DatabaseResult)" " URLs : $($result.UrlResult)" " Encryption Key : $($result.EncryptionKeyResult)" "" "Detail-Log:" ) + $logLines $lines | Out-File -FilePath $reportFile -Encoding UTF8 -Force $centralPath = Get-mssConfig -Key 'CentralPath' if ($centralPath) { $centralFile = Join-Path $centralPath (Split-Path $reportFile -Leaf) if (-not (Test-Path $centralPath)) { New-Item -ItemType Directory -Path $centralPath -Force | Out-Null } Copy-Item -Path $reportFile -Destination $centralFile -Force -ErrorAction SilentlyContinue } $result.OverallStatus = if ($errorsOccurred) { 'PartialSuccess' } else { 'Success' } $result.Message = "Dienstkonto: $($result.ServiceAccountResult) | Datenbank: $($result.DatabaseResult) | URLs: $($result.UrlResult) | Key: $($result.EncryptionKeyResult)" Write-Host "" Write-Host "[$ComputerName] SSRS-Konfiguration: $($result.OverallStatus)" -ForegroundColor $(if ($errorsOccurred) { 'Yellow' } else { 'Green' }) Write-Host " Bericht: $reportFile" -ForegroundColor Gray } catch { $errMsg = "Schwerer Fehler auf $ComputerName : $($_.Exception.Message)" Invoke-mssLogging -Message $errMsg -FunctionName $functionName -Level "ERROR" if ($EnableException) { throw } $result.OverallStatus = 'Failed' $result.Message = $errMsg } finally { if ($cimSession) { Remove-CimSession $cimSession -ErrorAction SilentlyContinue } } return $result } } |