Hi Friends,
Today I will share an issue which ideally started at higher level however after troubleshooting it landed on Basics.
Let me start of the issue we configured Distributed Availability Groups and as part of it we requested the application team to make use of ApplicationIntent=Readonly in their reporting services Data Source.
When they checked they said it is not working and below is the error message
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)
The message is not popping up when we removed the Application Intent from the connection String.
I wrote an article on how to configure Routing from GUI below is the link
https://www.chiranjeevivamsydba.com/2019/03/always-on-read-only-routing-from-sql.html
when checked the routing was working perfectly fine. Now where lies the problem is it something with reporting services? Not at all whenever I have such issues I always make use of UDL file to test the connectivity.
By Default UDL file will have it's Application Intent as READWRITE but I changed that to READONLY as the issue is happening only with that. I created this UDL file from my jump server and it is working fine.
Again I got struck and this time I created the same file on the server where Reporting services are deployed(Installed) but this time the UDL file has thrown the below error
Now I can say it is not anything with reporting services. So why it is working with Readwrite why not with Readonly? Because at the time of requesting my colleague has requested with Listener Name and port no but he has not provided the server names hence we have this issue
When I just passed the primary Replica name instead of Listener name it too is getting failed with the same error message.
My Servers have sensitive information hence they are placed behind the firewall so to overcome this we raised change requested this time by passing the server names as well with port no's.
The same can be done with SQLCMD too but on reporting servers we don't have that. Hence I made use of UDL to test the connectivity.
I always feel that most of the solutions can be obtained easily if we have our basics in place.
Comments