December 10, 2021 at 10:50 pm
We upgraded from SQL Server 2016 to 2019, and our reports on the report server now say:
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'DataSource1'. (rsErrorOpeningConnection)
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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
The network path was not found
If I download the report and/or run the original from my box via Visual Studio, the report works.
December 11, 2021 at 11:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
December 11, 2021 at 11:48 pm
Did you update your SSRS and migrate your reports, as well?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 13, 2021 at 5:55 pm
We upgraded from SQL Server 2016 to 2019, and our reports on the report server now say:
An error has occurred during report processing. (rsProcessingAborted) Cannot create a connection to data source 'DataSource1'. (rsErrorOpeningConnection) 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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) The network path was not found
If I download the report and/or run the original from my box via Visual Studio, the report works.
It's running under your credentials when you run it locally, I'm betting.
Have you verified that the network settings of the new server are set properly? Is it in a different subnet, and possibly you have a firewall issue that is blocking it? Is the new server the same name as the old server? Have you attempted to create a new connection? Have you logged into the SSRS server and tried to ping, or connect using SSMS from that to to the new server? Are you using aliases that need to be changed?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
December 13, 2021 at 6:46 pm
For the credentials, we have the datasource to use a specific username/pwd...see attached.
Ok, have you verified that the network settings of the new server are set properly? Is it in a different subnet, and possibly you have a firewall issue that is blocking it? Is the new server the same name as the old server? Have you attempted to create a new connection? Have you logged into the SSRS server and tried to ping, or connect using SSMS from that to to the new server? Are you using aliases that need to be changed?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
December 13, 2021 at 6:58 pm
When you say you upgraded SQL Server - what exactly was upgraded? Did you upgrade SSRS only - or also the database engine? Is this an all-in-one SSRS instance where both the database engine and the web components have been installed, or are the databases hosted on a different server from the reporting services?
Was this an in-place upgrade, or did you create a new server and migrate to the new server?
And finally, did you backup the encryption keys and restore them after the upgrade?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 13, 2021 at 7:33 pm
** Answer per our admin:
When you say you upgraded SQL Server - what exactly was upgraded?
--Everything was upgraded. SQL Server itself and SSRS. They are stand alone installations now so they had to be upgraded separately.
Did you upgrade SSRS only - or also the database engine?
--SAME ANSWER AS ABOVE
Is this an all-in-one SSRS instance where both the database engine and the web components have been installed, or are the databases hosted on a different server from the reporting services?
--The databases we are connecting to with our web app reports sit on a different server from the SSRS server. Creating a local datasource connection to the same databases for web app reports works just fine.
Was this an in-place upgrade, or did you create a new server and migrate to the new server?
--In place upgrade.
And finally, did you backup the encryption keys and restore them after the upgrade?
--Yes I went throught he steps outlined in the upgrade procedures to backup all encryption keys and restore them. I also backed up the reportserver database before the migration. The new version of SSRS points back to the original 2016 database that we were using.
December 13, 2021 at 8:04 pm
There was a migration? Or just an in-place upgrade of everything? It isn't clear - but it sounds like you have a new server and that server may be blocked.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 13, 2021 at 9:39 pm
in place upgrade of everything
December 15, 2021 at 2:04 am
We got the report to work by changing our datasource connection string InStr() to uppercase for the first parameter, and the new server URL is uppercased.
Our expression works now after updated to the following, but problem is we have 50+ report to now do this for. Looks like this was due to a fix:
KB4535706 makes SSRS Report URL's Case Sensitive
="Data Source=server-" &
IIF(IsNothing(Globals!ReportServerUrl), "app",
Switch(
InStr(Globals!ReportServerUrl, "-BBSQL-"),"app",
InStr(Globals!ReportServerUrl, "-CCSQL-"),"app2"
))
& "-" &
IIF(IsNothing(Globals!ReportServerUrl),"devl",
Switch(
InStr(Globals!ReportServerUrl, "-DEVL"),"devl",
InStr(Globals!ReportServerUrl, "-BETA"),"beta",
InStr(Globals!ReportServerUrl, "-PROD"),"prod"
)
) & ";Initial Catalog=" & Parameters!DB.Value
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply