I have set up a 2-node SQL AlwaysOn cluster and configured read-only routing for requests to be answered by the secondary replica server.
I want to use this setup to connect from a shared data source in SQL report and configured it with a connection string specifying the server name, initial catalog and readonly application intent.
But when I try to save the data source as a shared report part on the reporting server, I encountered the following error:
Cannot open database "servicedesk" requested by the login. The login failed. Login failed for the user 'report'.
The user I am trying to use to connect to the data source is granted with public permission on the server and db_datareader permission on the destination database. I also tried using a sysadmin user and the connection was established successfully and the queries were routed to secondary replica. but the point is that I prefer not to set a sysadmin user on the reporting server.
Create the "report" user on the primary and give it the needed permissions to the databases in question.
Then go grab a copy of sp_help_revlogin from Microsoft's documentation and create this on the primary.
Run sp_help_revlogin and then copy the line which will create the "report" user and then run this on all the secondaries.
Once done you should be good to use minimum permissions.
This is a common thing with SQL logins on AOAGs, you need to make sure the SIDs match between the replicas to ensure you can use the logins on all the possible servers otherwise you end up with orphaned logins.
December 7, 2022 at 11:11 am
Thanks @SSC Guru for your fast and accurate reply.
It worked liked a charm and saved me from looking further on the issue.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply