I have a SQL Server Availability Group for HA and a Distributed Availability Group for DR.
I also have SQL Server Reporting Services running on all Availability Group nodes.
For various historical reasons, we have connections between user databases in the Availability Groups, and the ReportServer database. Yes, I know, cross database queries are bad; suffice to say there is no appetite to change the line of business app that uses the ReportServer database directly. Our applications also rely on SSRS reports for generating customer-facing documents in real time, making SSRS a single point of failure if the ReportServer database was hosted on a single machine and that machine was down.
This has been working perfectly in our on-premises setup where the two node AG exists on a single subnet. The Reporting Services services on each node connects to the ReportServer database via the AG listener. When a failover occurs, a SQL Server Alert fires a SQL Server Agent Job which restarts SSRS via a CmdExec step via a proxy from a local account that has zero access to the machine aside from the ability to stop and start the SSRS Service.
We're moving our entire infrastructure into Azure (on Azure VMs since that is the only way to get FileStream support). This necessitates running our Availability Group SQL Servers on different IP subnets, mandating the use of MultiSubnetFailover=True in connection strings.
First, the unanswerable question; why doesn't Microsoft provide a mechanism for SSRS to use MultiSubnetFailover=True in its connection to the ReportServer database? That would solve the problem entirely. I'm assuming SQL Server Reporting Services 2022 still doesn't have that capability (I know SSRS 2019 doesn't have it).
Since Microsoft doesn't support MultiSubnetFailover=True for SSRS ReportServer database connectivity, I need to reconfigure the SQL Server Reporting Service service on each node when a failover occurs, so that I can point each SSRS service at the then-primary node. This is trivial to accomplish via the command line, using the rsconfig.exe command:
C:\Program Files\...\rsconfig.exe -c -i SSRS -s <primary_sql_server_node> -d ReportServer -a Windows
The account running the above command is required to be a member of the local Administrators group. For testing, I added the proxy account to the local Administrators group on our dev server, however the rsconfig command above still fails because there is a security policy that prevents members of the local Administrator group logging in to the machine over the network. When I manually run the command out of a cmd shell on the VM, it works fine, however when the proxy runs the command it returns the following error:
system error: Logon failure: the user has not been granted the requested logon type at this computer. ('Access this computer from network')). The step failed.
I can configure the server to allow the given user to access the computer from the network however that goes against our policy. If the account that runs RSConfig could be given the granular permissions it needs, instead of membership in the local Administrators group, that would also solve the problem, however I don't know the granular permissions it needs, and I'd bet rsconfig.exe checks it has membership in the local Administrators group at startup anyway.
Does anyone have any smart ideas about how I should get SSRS to work in Azure considering the constraints:
MultiSubnetFailover=Trueis required for Distributed Availability Groups in Azure (see here and here).- Members of the local "Administrators" group cannot log in over the network by policy.
- The
ReportServerdatabase needs to be co-located with certain application databases in a high-availability configuration.
If there was a way to modify the connection string stored in the DSN in the rsreportserver.config file, and encrypt it in a way that SSRS could decrypt it, that would solve the problem, but obviously the format of the DSN, and the encryption SSRS is using is a black box at this point.
