Link to home
Start Free TrialLog in
Avatar of Adrian
AdrianFlag for United States of America

asked on

Valid DSN Error and connection/compatibility error due to ReportServer database when attempting in-place upgrade from sql Server 2014 to sql Server 2016

User generated image
The following error details are in the upgrade log:

The report server database is not a supported compatibility level or a connection cannot be established. Use Reporting Services Configuration Manager to verify the report server configuration and SQL Server management tools to verify the compatibility level.

The report server configuration is not complete or is invalid. Use Reporting Services Configuration Manager to verify the report server configuration.

The report server has already been upgraded to 2016.  The report config app on the report server looks normal.  REPORTS in this database continue to WORK properly before and after the report server upgrade to 2016.

We've deleted the account on the database and report server and re-added it.  We've re-selected the "ReportServer" database from the report config app and ran "Test Connection" with success. We're using the same domain account that was in use for 2014.

The report server has 2 instances.  We've applied the above attempts to both.

The last step will be to take out the existing ReportServer database and use the report server config app to "Create New" ReportServer database.  However, we would like to avoid this as we would have to manually re-deploy hundreds of reports to the server. ( And it might not solve the problem. )

Any ideas would be appreciated. Thanks.
Avatar of lcohan
Flag of Canada image

"The report server database is not a supported compatibility level " - did you checked via the SSMS for instance what compatibility level your database has? Because this was a in-place upgrade my guess is that the compatibility level was set too low on SQL 2014 - like to SQL 2000 or "80" in compat level therefore that cannot be used in newer version of SQL like 2016 where min compat level is higher than that
Please see this link for different compat levels and I suggest you set your ReportServer to the same compat level as the master DB on each SQL instance.

For future upgrades my advice is to always run the upgrade adviser/assistant as this will show you prior to upgrading all issues from critical to trivial
Avatar of Adrian


The versions of the ReportServer databases match the master and are all 2014.

The link is a data migration assistant.  We looked at that at the beginning of our process.  When you run it, it prompts you for server A, server B, etc.  It doesn't seem to be geared toward an in-place upgrade.  Do you have experience using it in that scenario?
"The versions of the ReportServer databases match the master and are all 2014." - sorry if I wasn't clear enough however I though the screen shots from the link above about "understanding-sql-server-compatibility-levels" will make it clear to you - is not the database version but it "Compatibility Level" that must be set to a supported version like 120 or above.

The link refers to the DMA tool indeed but that will be used on the Server you need to upgrade from and did you installed it and to actually run it? Of course you can use it to asses an upgrade and you will select your existing as source and new version as destination but you don't need to actually have bot versions/instances on the same or separate servers. Just select the "Assessment" under  the new project not a actual migration or upgrade.

"Data Migration Assistant (DMA) enables you to upgrade to a modern data platform by detecting compatibility issues that can impact database functionality on your new version of SQL Server."
Avatar of Adrian


Thanks for your attention, by the way.

Yes, sorry for the imprecise term, the compatibility levels are all 2014 120.

We're going to give the DMA a try to see what it reveals. Thanks for the "assessment" tip, we probably missed that the first time. if the ReportServer and ReportServerTempDB databases are already upgraded and compatibility mode is set to 120 as you mentioned is likely the DMA would not help much after the upgrade was already done. My advice was for the future upgrades - whether in place or migration/upgrade to use the DMA prior to the upgrades to reveal any potential issues. Let's try think something else prior to recreating the ReportServer database from scratch.

Do you have n older - prior to the upgrade backup of both ReportServer and ReportServerTempDB databases?
Do you have a backup of the original source encryption key?

If you do, I would stop SSRS, rename exisitng ReportServer database just in case!! to have it on the server as you should be still able to use it to extract all the reports, data sources, ETC from it.

And try see if the steps under "Conclusion" from link below can help you instead of creating a brand new empty SSRS ReportServer db.

Other useful resources that you most likely have already but if not..I would save some of those scripts as who knows when they'll be gone:
Avatar of Adrian


The DMA returned a bunch of stuff to clean up for the future regarding stored proc stuff like don't use ntext, etc. but only information and warnings, no show stoppers.

We'll look at your additional suggestions next. Thanks.
Avatar of Adrian
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial