Configuring SQL Server 2022 Reporting Services Using Automation

Configuring SQL Server 2022 Reporting Services Using Automation: Certain people have some queries and questions on how to configure the SSRS/PBIRS with PowerShell scripts.

In my recent post about silently installing SQL Server 2016 and later Reporting Services (SSRS 2022), I only covered the basics of configuration. Initially, SSRS 2016 and later won’t be set up at all. You can configure it with the Reporting Service Configuration Manager GUI, but I needed to automate this process, so I opted for PowerShell. I’ve shared my full PowerShell script for setting up SSRS 2016 and later, here’s a brief rundown of the steps involved:

1. Retrieve a WMI object containing the SSRS 2022 instance’s configuration settings.
2. Obtain a SQL script for creating the ReportServer and ReportServerTempDB databases.
3. Connect to the default SQL Server instance on the same machine.
4. Run the SQL script.
5. Acquire and execute another SQL script to configure permissions for the SSRS 2022 service account.
6. Update the SSRS database connection to use the newly created database.
7. Set up the web service’s virtual directory name and URL.
8. Set up the report manager web app’s virtual directory name and URL.
9. Initialize the report server, ensuring encryption for sensitive data.
10. Restart the service.
11. Display the updated configuration.

Configuring SQL Server 2022 Reporting Services Using Automation (PowerShell)

Step 1: Install the new SQL Server Reporting Services (2022) and once the installation is complete, please go to next step without touching configuration manager. If you look at below image you can see that there is no database configured, also the URL’s or not configured yet!

Step 2: Download the PowerShell script file from this link: Configure_SSRS_Using_PowerShell_Script. Once you download this script make sure you change the database name in the green highlighted marks.

Step 3: I have the script and now I will run this script in my PowerShell environment. Once you run the script it will take 2-3 minutes and then the SSRS will be configured as shown below.

Step 4: Now if you compare the and check the SSRS from step 1, you can see that the DB and URL are configured as given in the script.

Note: Please note that this script should be compatible with SQL Server 2016 Reporting Services as well. However, for it to work with versions prior to that, you’ll need to modify the name of the Report Manager web application to ReportManager.

Watch More!

TSG Links:

1. Kerberos Issue: NT AuthorityAnonymous Login Failed -Resolve (kingfishertechtips.in)
2. Port Number For Analysis Services – Find Ports For SSAS? (kingfishertechtips.in)
3. RS.exe Utility: Migrate RDL Reports To Another SSRS/PBIRS (kingfishertechtips.in)
4. Direct Query Mode ON In Tabular Model Issue – Visual Studio (kingfishertechtips.in)
5. SMTP Server In SSRS/PBIRS |Subscribe To Email Problem/Issue (kingfishertechtips.in)
6. SQL Server 2022 Download, Install, Connect Using SSMS Tool (kingfishertechtips.in)
7. Creating Tabular Project & Deploy Model To Analysis Service (kingfishertechtips.in)
8. Deploy Tabular Project To Azure Analysis Service – SSDT Tool (kingfishertechtips.in)
9. SSRS/PBIRS – Install And Configure The Report Server On-Prem (kingfishertechtips.in)
10. Could Not Load Folder Contents In Portal URLs – SSRSPBIRS (kingfishertechtips.in)
11. SSRS/PBIRS – Install And Configure The Report Server On-Prem (kingfishertechtips.in)
12. SQL Server 2022 Download, Install, Connect Using SSMS Tool (kingfishertechtips.in)
13. Capture Fiddler Trace for Debugging the Network Traffic (kingfishertechtips.in)
14. On-Premises Gateway: Configure/Map SQL DB To Power Bi (kingfishertechtips.in)
15. Fixed Column/Row Header Make Visible In Any Report (kingfishertechtips.in)
16. Self-Signed Certificate: Configure SSRS/PBIRS With SSL (kingfishertechtips.in)
17. Create Local User Account/Local Admin User In Windows 10/11 (kingfishertechtips.in)
18. Login With AAD/Domain/Microsoft Account To Azure VM’s/RDP (kingfishertechtips.in)