SSRS and Multi-Subnet Failover: The Connection String That Doesn’t Exist

SQL Server Reporting Services connects to its ReportServer database using a connection string buried in RSReportServer.config. When that database lives on an Availability Group behind a listener, and the AG spans a single subnet, everything works. You point SSRS at the listener, set up a SQL Agent alert to restart the SSRS service on failover, and you’re done. I ran this configuration for years.

Then we moved to Azure VMs, and the AG became multi-subnet.

Multi-subnet AGs require MultiSubnetFailover=True in connection strings. Without it, a client connecting to the listener after failover has to wait through DNS round-robin attempts to the old primary’s IP address — an IP that no longer responds. Depending on the DNS TTL and the client’s TCP timeout, this can mean 30 seconds to several minutes of connection failures. With MultiSubnetFailover=True, the client tries all listener IPs in parallel and connects to whichever one answers.

Every application I manage got MultiSubnetFailover=True in its connection string. Every application except SSRS.

SSRS has no mechanism to specify MultiSubnetFailover=True in its ReportServer database connection. Not in RSReportServer.config, not in the Reporting Services Configuration Manager, not in rsconfig.exe. The connection string is constructed internally and doesn’t expose additional connection properties.^[1]

This means that after an AG failover across subnets, SSRS sits there trying to connect to an IP address that isn’t listening anymore. Restarting the SSRS service doesn’t help — it reads the same config and tries the same listener, hitting the same DNS timeout problem. The only thing that does work is reconfiguring SSRS to point directly at the new primary node using rsconfig.exe.

So the question becomes: can we automate that reconfiguration?

Why We Can’t Just Use the Listener

In a single-subnet AG, the listener has one IP address. After failover, that IP floats to the new primary, and everything reconnects quickly. In a multi-subnet AG, the listener has multiple IP addresses — one per subnet. After failover, DNS returns all of them, and the client has to figure out which one is live.

Well-behaved applications set MultiSubnetFailover=True and try all IPs simultaneously. SSRS doesn’t — it tries them sequentially, waits for each TCP timeout, and eventually (maybe) connects. Or it doesn’t, and you get a sea of “cannot connect to ReportServer database” errors in the SSRS log.

The workaround is to bypass the listener entirely for SSRS and point it at the current primary node by name. We lose the abstraction of the listener, but we gain a connection that actually works.

The Solution: Dynamic rsconfig via SQL Agent

Here’s the approach:

1. A SQL Agent Alert fires when the AG role changes on this node
2. The alert triggers an Agent Job
3. Step 1 (T-SQL): Queries the AG DMVs to find the current primary replica name, then uses sp_update_jobstep to dynamically write that server name into Step 2’s command text
4. Step 2 (CmdExec): Runs rsconfig.exe to point SSRS at the new primary
5. Step 3 (CmdExec): Restarts the SSRS Windows service

The key insight — and the question I had to answer before building this — is whether sp_update_jobstep can modify a step that hasn’t executed yet within the current job run. I verified that yes, it works. Agent reads step definitions from msdb when it moves to the next step, not when the job starts. When Step 1 calls sp_update_jobstep to change Step 2’s command, Agent picks up the updated command when it reaches Step 2. Microsoft doesn’t explicitly document this as guaranteed behavior, but it’s been reliable in every version I’ve tested from 2016 through 2022.

rsconfig.exe Basics

rsconfig.exe reconfigures which SQL Server instance hosts the ReportServer database. The syntax:

• -c — configure (as opposed to -e for encrypt)
• -i — the SSRS instance name, only needed for named instances. For SQL Server 2017+ standalone installs this is typically SSRS. For older installs or default instances, you may not need -i at all. Check your Reporting Services Configuration Manager if you’re unsure
• -s — the SQL Server hosting the ReportServer database (this is what we’re changing dynamically)
• -d — the database name (usually ReportServer)
• -a Windows — use Windows authentication

The executable lives at a path like C:\Program Files\Microsoft SQL Server Reporting Services\Shared Tools\rsconfig.exe on newer installs, or C:\Program Files\Microsoft SQL Server\MSRS.\Reporting Services\RSConfig\rsconfig.exe on older versions. Find yours and adjust the job step accordingly.

rsconfig.exe requires the caller to be a member of the local Administrators group.^[2]

The Code

The Alert

Create an alert on error 1480 — this fires when an AG replica role changes. You could also use a WMI alert on the AG state change event, but error 1480 is simpler and reliable. Note that 1480 is a per-database message, so a single AG failover may fire it multiple times (once per database in the AG). The @delay_between_responses = 60 setting prevents the job from being triggered repeatedly.

The Job

The job has three steps. Adjust the paths, instance names, and service names for your environment.

Note the early exit in Step 1: if this node isn’t the new primary, Step 1 throws an error. But @on_fail_action = 1 tells Agent to quit the job with success — so secondary nodes get a clean, successful job execution in the history rather than a sea of red failures. Only the new primary proceeds to Steps 2 and 3.

Important: This job must exist on every AG node, because any node could become the primary.

The Proxy Account

Steps 2 and 3 run under a CmdExec proxy because they need privileges the SQL Agent service account shouldn’t have:

• Local Administrator membership (required by rsconfig.exe)
• Permission to stop/start the SSRS Windows service

Create a dedicated service account for this. Do not use a domain admin account. Do not reuse the SQL Agent service account. The principle here is simple: this account should be able to run rsconfig.exe and restart SSRS, and nothing else.

On each AG node, make sure the YOURDOMAIN\svc-ssrs-admin account:

1. Is a member of the local Administrators group
2. Has “Log on as a batch job” rights (usually granted automatically when added as a proxy credential)
3. Can stop/start the SSRS service (local Admin covers this, but verify)

Testing It

Don’t wait for a production failover to find out this doesn’t work. Test it deliberately.

Pre-test checklist

• SSRS is running and serving reports on the current primary
• The Agent job exists on all AG nodes
• The proxy credential is configured on all nodes
• The rsconfig.exe path in the job step is correct for your installation

Manual failover test

Then watch:

1. Error 1480 should appear in the SQL Server error log on the new primary
2. The Agent alert should fire (check msdb.dbo.sysalerts_performance_counters or just sp_help_alert)
3. The Agent job should run — check job history on the new primary
4. Step 1 should show the primary detection and sp_update_jobstep call
5. Step 2 should show rsconfig.exe completing successfully
6. Step 3 should show the SSRS service restarting
7. After 30-60 seconds, SSRS should serve reports from the new primary

If any step fails, the job history will tell you exactly which one and why. Common issues:

• Wrong rsconfig.exe path → Step 2 fails with “file not found”
• Proxy account not in local Administrators → Step 2 fails with an access denied error from rsconfig.exe
• Wrong SSRS service name → Step 3 fails to stop the service
• SSRS instance name wrong (e.g., MSSQLSERVER vs. SSRS) → rsconfig.exe completes but configures the wrong instance (or throws an error)

Fail back

After validating on the new primary, fail back to the original node and verify the same process works in reverse. Both directions need to work.

Caveats and Edge Cases

Brief service interruption. There’s a window between the AG failover completing and SSRS finishing its reconfiguration where reports won’t work. In my environment this is 15-30 seconds — the time for the alert to fire, Agent to start the job, rsconfig.exe to run, and the SSRS service to restart. Users hitting the report server during this window will get errors. If your SSRS handles time-sensitive requests, plan accordingly.

Encryption key backup. SSRS encrypts stored credentials and connection strings using an encryption key. Before any of this, make sure you’ve backed up the SSRS encryption key on every node. If rsconfig.exe reconfigures the database connection and the encryption keys don’t match, you’ll get decryption errors for every stored data source credential. The Reporting Services Configuration Manager has a “Backup” button on the Encryption Keys page. Use it. Store the backup somewhere that isn’t the server itself.

This is a workaround. I want to be explicit about this: we’re working around the fact that Microsoft hasn’t added MultiSubnetFailover support to SSRS’s internal database connection. If they ever do (and as of SQL Server 2022, they haven’t), this entire approach becomes unnecessary. Until then, this is what we’ve got.

AG name and instance name are hardcoded. The T-SQL in Step 1 has your AG name baked in. If you rename the AG or add a second one, update the job. Same for the SSRS instance name in the rsconfig.exe command.

Cross-database queries. I mentioned our line-of-business app runs cross-database queries between user databases and ReportServer. This is why ReportServer has to be in the AG in the first place — those queries need all databases on the same instance. If you don’t have this constraint, there are simpler architectures (like a standalone SSRS with its own local ReportServer database). But if you’re reading this post, you probably have the same constraint.

Timing with multiple AG databases. If your AG contains many databases, the role change event fires as soon as the AG starts transitioning. The databases may not all be online on the new primary yet. In practice, rsconfig.exe only needs the ReportServer database to be accessible, and it’s usually online by the time Step 2 runs (Step 1 provides a few seconds of delay). If you have a very large AG and see timing issues, add a WAITFOR DELAY '00:00:10' at the beginning of Step 1.

This is a workaround for a gap in SSRS that’s been open since multi-subnet AGs shipped in SQL Server 2012. Fourteen years. If you’re reading this from the future and SSRS finally supports MultiSubnetFailover=True natively — congratulations, you can delete this job. For the rest of us, this gets the job done.