Setting up PowerBI Report Server SPN

Ah - 'Setting up PowerBI Report Server SPN's for PowerBI and SQL to help securely communicate and authenticate.
Ah – ‘Setting up PowerBI Report Server SPN’s for PowerBI and SQL to help securely communicate and authenticate.

‘Setting up PowerBI Report Server SPN’ in hybrid environments when the PowerBI cloud service is not <yet> an option in an organization.  This article will go through SPN commands, to secure via Kerberos authentication and/or smart card usage for Security requirements (i.e. STIG, CCRI, SOX, HIPAA, PCI, Security Scans, <insert other regulatory requirements here>).  Lastly, PowerBI Report Server can be setup to run parallel to SSRS SQL instance.  Refer to SPN commands below which helped me setup SmartCards authentication based on SPN setup.

 

Find/replace

DOMAIN

POWERBIREPORTSERVER

FQDN

svc.PowerBI.scomda

svc.PowerBI.scomdr

 

 

SPN commands to set up SQL & PowerBI

Create SPN for PowerBI Report Server

# RE: PBIRS SPN’s
SetSPN -s “MSSQLSvc/POWERBIREPORTSERVER.FQDN” “DOMAIN\svc.PowerBI.scomda”
SetSPN -s “MSSQLSvc/POWERBIREPORTSERVER” “DOMAIN\svc.PowerBI.scomda”

 

Create PowerBi Report Server SPN’s for OLAP

# PBIRS & MSSQL
# Remove the SPN’s for SQL on Report Server
setspn -d MSOLAPSvc.3/POWERBIREPORTSERVER POWERBIREPORTSERVER
setspn -d MSOLAPSvc.3/POWERBIREPORTSERVER.FQDN POWERBIREPORTSERVER

 

Create PowerBI Report Server SPN for service/gMSA account

setspn -d HTTP/POWERBIREPORTSERVER.FQDN:443 DOMAIN\svc.PowerBI.scomdr
setspn -d HTTP/POWERBIREPORTSERVER:443 DOMAIN\svc.PowerBI.scomdr

 

Create SQL SPN’s for SSRS reporting

SetSPN -s “MSSQLSvc/POWERBIREPORTSERVER.FQDN” “DOMAIN\svc.PowerBI.scomda”
SetSPN -s “MSSQLSvc/POWERBIREPORTSERVER” “DOMAIN\svc.PowerBI.scomda”

 

Create SQL HTTP SPN’s for SSRS reporting

setspn -s HTTP/reports.FQDN DOMAIN\svc.PowerBI.scomdr
setspn -s HTTP/reports DOMAIN\svc.PowerBI.scomdr

 

Lastly, test authentications to PowerBI server…

Verify PBIRS (PowerBI Report Server) log file for ReportServerService_HTTP_ entries after successful auth

File PATH = D:\Program Files\Microsoft Power BI Report Server\PBIRS\LogFiles

 

Documentation

PowerBI with Service Principal https://powerbi.microsoft.com/en-us/blog/use-power-bi-api-with-service-principal-preview/

Configure Kerberos SSO https://learn.microsoft.com/en-us/power-bi/connect-data/service-gateway-sso-kerberos

Security – ODBC Vuln 175441

Security – ODBC Vuln 175441

Time to make the donuts!
Time to make the donuts!

Time to make the doughnuts again, new Security ODBC Vuln 175441 that needs to be mitigated.  Not sure if you ever saw the commercials, but this is where my mind goes sarcastic humor and all.  Whether you’re using ACAS/Tenable/Nessus for security scans, this may show up with your SCOM servers (MS, DB), and PowerBI Report Servers.

 

 

Let’s get started to upgrade ODBC

Action:  Security scan shows a new ODBC Vuln 175441, that may impact SCOM or PowerBI Report Server talking with SQL servers.

Start with some documentation, to understand what and why…

Tenable/Nessus Link to vulnerability

Download ODBC v18 here, v17 here

Outline of mitigation steps

What servers are vulnerable

Mitigate vulnerability on affected servers

Verify server Control Panel shows update

Have Security run additional scan to verify resolved

 

 

What servers are vulnerable?

We’re focused on the ‘Security – ODBC Vuln 175441’

 

Begin by looking at your Security scanning tool output (PowerBI report pictured).  I am also showcasing the PowerBI report, as this streamlines what the Security Admin has to provide when System Administrators (sysAdmin) reach out for debug/details.

ACAS/Tenable/Nessus scan PowerBI Report
ACAS/Tenable/Nessus scan PowerBI Report

 

In my case, I wanted to see what servers are impacted.  The PowerBI Report has a built-in ‘Deep Dive’ tab to see the details from the scan/check.  Click on the Deep Dive Tab, enter the PlugIn ID (175441 for ODBC) and hit enter.  This breaks out what servers are vulnerable.   Assess what servers are yours (my output simplified to show what I own with SCOM and PowerBI 🙂  Looking at the ‘NetBIOS Name’ column.  Alternatively, the admin typically has the scan tool email XLS files.

Access your ACAS/Tenable/Nessus scan deep dive tab (or PowerBI Report) to see how many systems are vulnerable.
Access your ACAS/Tenable/Nessus scan deep dive tab (or PowerBI Report) to see how many systems are vulnerable.

 

 

Mitigate vulnerability on affected servers

Download ODBC v18 here, v17 here

Save to share or common path to put file on affected server(s).

Once moved, login to affected server(s), typically RDP with Local Administrator equivalent admin ID

Open Windows Explorer > Copy ODBC MSI to server

Open PowerShell (as Admin) window > Go to path > Run ODBCMSI

PowerShell as Administrator steps
PowerShell as Administrator steps

 

 

Now the ODBC popup window for install

Note the screenshots and progress prompts

 

Click ‘I accept’ radio button and then click ‘Next’

ODBC EULA splash screen
ODBC EULA splash screen

 

 

Click Next to move beyond the ODBC features screen

ODBC Features screen
ODBC Features screen

 

 

Click on Install

ODBC Install prompt
ODBC Install prompt

 

 

Watch progress bar  (maybe 1-2 minutes)

ODBC Install Progress bar
ODBC Install Progress bar

 

 

Click Finished

ODBC Install finished
ODBC Install finished

Once the MSI installer window closes, it’s time to verify server Control Panel.

 

Verify server Control Panel shows update

Click on Start > Control Panel > Programs > Programs and Features

In the top right search bar, type ‘ODBC’ and hit enter to filter results.

 

Snapshot of Control Panel before

Control Panel with ODBC as the search string
Control Panel with ODBC as the search string

 

Snapshot of Control Panel after

Hit F5 to refresh screen output

ODBC Control Panel after install
ODBC Control Panel after install

 

The one question is if version 17 has to be removed to clear vulnerability.  Ran into this scenario with Java, as the update left old versions.

I typically reboot the server to reinitialize server to assess any impacts, as well as boot on the new drivers.   For this instance, I coordinated my July server updates were installed to simplify my admin (as both require reboot!)

 

Have Security run additional scan to verify resolved

Typically SME has scheduled scans that run weekly, and can run scans on-demand.  Depending on urgency, you can decide whether or not waiting is relevant.

Enjoy!

 

Microsoft links

Learn article here

Download ODBC v18 here, v17 here

PowerBI May 2023 install

PowerBI time baby!

Time to update PowerBI Report Server to PowerBI May 2023 update/install for PowerBI Desktop and Report Server!

 

Do you use PowerBI to render monitoring insights from SCOM, SolarWinds, ACAS/Tenable, ForeScout or more?   In case you didn’t know, PowerBI Report Server is the on-premise solution where updates from the PowerBI Cloud Service make way to prem at least twice a year.  Time to update to ‘PowerBI May 2023’ when you’re air-gapped, or just NOT to the cloud.  This post is how to upgrade PowerBI Report Server and PowerBI Desktop to the latest version.  This has been a few iterations in progress, and I couldn’t find any blog showing how to update these components.  NOTE: MDE/Intune/MECM/EM tools can be used to package this easily enough, but it’s typically a very small subset of servers used.

 

Grab a snapshot of PowerBI Report Server and Desktop Before MSI update/install

Before we upgrade to ‘PowerBI May 2023 install’ MSI’s –

Open Control Panel > Programs and Features > Search for Report (and hit enter)

Windows Server, Control Panel, Programs and Features before install
Windows Server, Control Panel, Programs and Features before install

 

Check PowerBI Desktop (shows before and after!)

Open Control Panel > Programs and Features > Search for ‘power’ (and hit enter)

PowerBI Desktop Windows Server, Control Panel, Programs and Features before install
PowerBI Desktop Windows Server, Control Panel, Programs and Features before install

 

 

Begin PowerBI Desktop update

Assuming you’ve downloaded the PowerBI updates and saved to relevant servers.  Check PowerBI blog here, PowerBI May2023 details and MSI download

 

Open PowerShell (as Admin)

Type .\PBIDesktopSetupRS_X64.exe and hit enter

Note the Pop-up MSI installer

PowerBI Desktop and PowerBI Report Server from PowerShell, Windows Server, Control Panel, Programs and Features before install
PowerBI Desktop and PowerBI Report Server from PowerShell, Windows Server, Control Panel, Programs and Features before install

 

 

Confirm EULA

Click ‘I Accept’ check box and then Next to continue Desktop install

PowerBI Desktop EULA
PowerBI Desktop EULA

 

Confirm Desktop Path

I changed to secondary drive to NOT fill up C: boot disk

PowerBI Desktop May2023 update path
PowerBI Desktop May2023 update path

 

 

Click Next to begin install

Click Next to begin install

PowerBI Desktop May2023 Next

PowerBI Desktop May2023 Next

 

Click Finish

Click Finish to complete update

PowerBI Desktop May2023 Finish
PowerBI Desktop May2023 Finish

 

 

 

PowerBI Desktop Reboot required prompts

PowerBI desktop prompted twice for reboot required

Click OK

PowerBI Desktop required reboot prompt first time
PowerBI Desktop required reboot prompt first time

Prompted again for reboot

PowerBI Desktop required reboot prompt
PowerBI Desktop required reboot prompt

Click OK

 

 

PowerBI Report Server update

Begin PowerShell window for PowerBI Report Server exe update

 

Check Version prior to install

Click on Start > Control Panel > Programs > Programs and Features

Type Report (and hit enter)

Verify version

PowerBI Report Server before update
PowerBI Report Server before update

 

 

PowerBI Report Server update

Check what’s installed before update

Check Control Panel > Programs > Programs and Features > Report (hit enter)

PowerBI Report Server updated
PowerBI Report Server updated

 

 

Begin Report Server install/update

From PowerShell as Administrator window > Type .\PowerBIReportServer.exe

Hit enter

NOTE: Similar popup output to PowerBI desktop pictured below

PowerBI Desktop and PowerBI Report Server from PowerShell, Windows Server, Control Panel, Programs and Features before install

PowerBI Desktop and PowerBI Report Server from PowerShell, Windows Server, Control Panel, Programs and Features before install

Choose Upgrade/Install PowerBI Report Server

PowerBI Report Server Upgrade/Install prompt

PowerBI Report Server Upgrade/Install prompt

Accept EULA

Click on ‘I accept’ radio checkbox

PowerBI Report Server EULA prompt
PowerBI Report Server EULA prompt

 

Report Server update installing

Watch while PowerBI Report Server updates

PowerBI Report Server Install progress
PowerBI Report Server Install progress

 

 

PowerBI Report Server reboot required

PowerBI Report Server prompts for reboot – ‘Restart required’

Click Close to reboot server

NOTE:  Optionally click on Restart.   Validate PowerBI Report server service is running via services.msc, and then check the PowerBI Report Server URL specified is functional.  This may still require server reboot! 

PowerBI Report Server Restart Required
PowerBI Report Server Restart Required

 

 

Additional verification of PowerBI Report Server install

Verify PowerBI Report Server updated from Windows Control Panel

Click on Start > Control Panel > Programs > Programs and Features

Type Power (and hit enter)

Verify the version number matches (unfortunately, Report Server does NOT list the version in the title)

PowerBI Report Server update complete
PowerBI Report Server update complete