Thursday, March 13, 2025

SSRS Cert Auto-Renew with PowerShell

✅ What We'll Build:

A complete automated PowerShell script to:

  1. Auto-detect expiring SSRS certs (based on threshold, e.g. 30 days)
  2. Find a new certificate automatically (newer version or renewed cert)
  3. Rebind the new cert to SSRS's HTTPS listener (port 443)
  4. Restart SSRS and optionally log the whole operation
  5. Include logging, email alerts, and support for scheduled tasks

✅ Assumptions

  1. Certificates are auto-renewed or manually renewed in LocalMachine\My
  2. SSRS uses port 443 and HTTP.SYS
  3. You can run this as a scheduled task or manually
  4. You'll provide SMTP settings for email alerts (optional)

✅ Full Script: SSRS Cert Auto-Renew + Rebind + Logging + Email

# SSRS SSL Auto-Renew + Rebind Script
# Author: PowerShell Engineer GPT 
# Version: 2.0
# Purpose: Automatically detect and rebind new SSL certificates to SSRS, with logging and optional email alerts

# -----------------------
# CONFIGURATION
# -----------------------

# General settings
$ipport = "0.0.0.0:443"
$ssrsServiceName = "SQLServerReportingServices"
$certFriendlyName = "mycompany.com"          # Match partial Subject Name of the SSRS SSL cert
$daysToExpireThreshold = 30                  # Days before expiration to trigger rebind

# Logging settings
$logFilePath = "C:\Logs\SSRS_SSL_AutoRebind.log"
$logRetentionDays = 90

# Email alert settings (optional)
$sendEmailAlerts = $false
$smtpServer = "smtp.yourdomain.com"
$smtpPort = 587
$smtpFrom = "ssrs-monitor@yourdomain.com"
$smtpTo = "admin@yourdomain.com"
$smtpSubject = "SSRS SSL Rebind Performed"
$smtpCredential = Get-Credential -Message "Enter SMTP Credentials" -UserName "smtpuser"

# -----------------------
# LOGGING FUNCTION
# -----------------------
function Write-Log {
   param (
       [string]$message,
       [string]$level = "INFO"
   )
   $timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
   $entry = "$timestamp [$level] $message"
   Write-Host $entry
   Add-Content -Path $logFilePath -Value $entry
}

# -----------------------
# EMAIL ALERT FUNCTION
# -----------------------
function Send-EmailAlert {
   param ([string]$message)
   if (-not $sendEmailAlerts) { return }

   Send-MailMessage -From $smtpFrom -To $smtpTo -Subject $smtpSubject `
       -Body $message -SmtpServer $smtpServer -Port $smtpPort `
       -Credential $smtpCredential -UseSsl
}

# -----------------------
# MAIN SCRIPT
# -----------------------
Write-Log "========= Starting SSRS SSL Auto-Rebind Script ========="

# Clean up old logs
Get-ChildItem -Path (Split-Path $logFilePath) -Filter *.log |
   Where-Object { $_.LastWriteTime -lt (Get-Date).AddDays(-$logRetentionDays) } |
   Remove-Item -Force

# Step 1: Get current binding and appid
Write-Log "Checking existing SSL bindings..."
$bindings = netsh http show sslcert
$appidRegex = '{[0-9a-fA-F-]+}'
if ($bindings -match $appidRegex) {
   $appid = $Matches[0]
   Write-Log "Existing AppID found: $appid"
} else {
   $appid = [guid]::NewGuid().Guid
   Write-Log "No existing AppID found. Generated new AppID: $appid"
}

# Step 2: Find the current SSRS SSL certificate
Write-Log "Searching for current certificate with [$certFriendlyName]..."
$currentCert = Get-ChildItem -Path Cert:\LocalMachine\My | Where-Object { $_.Subject -like "*$certFriendlyName*" } | Sort-Object NotAfter -Descending | Select-Object -First 1

if ($null -eq $currentCert) {
   Write-Log "No current certificate found! Exiting." "ERROR"
   Send-EmailAlert "SSRS SSL Auto-Rebind FAILED! No current certificate found!"
   exit 1
}

Write-Log "Current Cert Subject: $($currentCert.Subject)"
Write-Log "Current Cert Thumbprint: $($currentCert.Thumbprint)"
Write-Log "Current Cert Expiry: $($currentCert.NotAfter)"

# Step 3: Check expiration
$daysRemaining = ($currentCert.NotAfter - (Get-Date)).Days
Write-Log "Days remaining until cert expires: $daysRemaining"

if ($daysRemaining -gt $daysToExpireThreshold) {
   Write-Log "Cert is valid for more than $daysToExpireThreshold days. No rebind needed."
   exit 0
}

Write-Log "Cert expiring soon (within $daysToExpireThreshold days)! Searching for newer cert..."

# Step 4: Find newer certificate (later expiration)
$newCert = Get-ChildItem -Path Cert:\LocalMachine\My | Where-Object {
   $_.Subject -like "*$certFriendlyName*" -and $_.NotAfter -gt $currentCert.NotAfter
} | Sort-Object NotAfter -Descending | Select-Object -First 1

if ($null -eq $newCert) {
   Write-Log "No newer certificate found. Manual intervention required." "ERROR"
   Send-EmailAlert "SSRS SSL Auto-Rebind FAILED! No newer certificate found!"
   exit 1
}

Write-Log "New Cert Found!"
Write-Log "Subject: $($newCert.Subject)"
Write-Log "Thumbprint: $($newCert.Thumbprint)"
Write-Log "Expires: $($newCert.NotAfter)"

# Step 5: Unbind old SSL binding
Write-Log "Removing existing binding on $ipport..."
& netsh http delete sslcert ipport=$ipport
Write-Log "Old binding removed (if it existed)."

# Step 6: Bind new SSL certificate
$newThumbprint = $newCert.Thumbprint -replace " ", ""
Write-Log "Binding new certificate to $ipport with thumbprint $newThumbprint..."
& netsh http add sslcert ipport=$ipport certhash=$newThumbprint appid=$appid
Write-Log "New binding added!"

# Step 7: Restart SSRS
Write-Log "Restarting SSRS service [$ssrsServiceName]..."
Restart-Service -Name $ssrsServiceName -Force
Write-Log "SSRS restarted successfully."

# Step 8: Verify binding
Write-Log "Verifying new SSL binding..."
$newBindings = netsh http show sslcert
Write-Log "New SSL bindings:`n$newBindings"

# Step 9: Send email alert
$summary = @"
SSRS SSL Auto-Rebind Completed!

New Certificate Bound:
Subject    : $($newCert.Subject)
Thumbprint : $($newCert.Thumbprint)
Expires    : $($newCert.NotAfter)

Port Bound : $ipport
AppID      : $appid

SSRS Service Restarted.
"@

Write-Log "Rebind completed successfully!"
Send-EmailAlert $summary

Write-Log "========= SSRS SSL Auto-Rebind Script COMPLETE ========="

✅ What This Script Does:

Task

Description

Audit

Checks the current SSL binding and finds the AppID

Detect Expiry

Checks if the current certificate is near expiration

Search New Cert

Finds the newest valid cert matching the domain (Subject)

Rebind

Unbinds the old cert, binds the new one to the port

Restart SSRS

Restarts the SSRS service to apply the change

Logging

Logs actions to a log file with rotation

Email Alerts

Sends success/failure notifications via email

✅ Customizing the Script

  1. Set $certFriendlyName to match your certificate
     Example: "ssrs.yourdomain.com"
  2. SMTP settings: Provide real values for:
  • $smtpServer
  • $smtpFrom / $smtpTo
  • $smtpCredential (optional, secured via Get-Credential)
  1. Change $ssrsServiceName if using a named instance
     Example: "SQL Server Reporting Services (MSSQLSERVER)"

✅ Scheduling It (Automation)

  • Save this script as SSRS_SSL_AutoRebind.ps1
  • Run it via Task Scheduler:
  • Action: powershell.exe -File "C:\Scripts\SSRS_SSL_AutoRebind.ps1"
  • Run as: Administrator
  • Trigger: Daily, or Weekly
  • Condition: Ensure it has access to cert store and SSRS service permissions

at No comments:
Labels: , , ,
Subscribe to: Posts (Atom)