Report Server database migration fails at "Setting DSN" step

  • Article

Hello all,

Recently I worked on a scenario where the requirement was to migrate the ReportServer database from SQL 2008 R2 instance to SQL 2014. In this blog, I would like to share the steps used to fix the issue.

To illustrate the issue, I have installed SQL 2008 R2 Reporting Server “SSRSServer” on Windows R2 SP1 server and “ReportServerDB” to host the report Server database. SQL Instance names used are :SQL2008R2 and SQL2014.

Problem Description:

Migration of ReportServer database from SQL Server 2008 R2 to SQL 2014 instance fails while “Setting DSN” with following error:

System.UnauthorizedAccessException

   at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)

   at System.Management.ManagementObject.InvokeMethod(String methodName, ManagementBaseObject inParameters, InvokeMethodOptions options)

   at Microsoft.ReportingServices.WmiProvider.RSWmiAdmin.SetDatabaseConnection(String server, String database, Int32 credsType, String userName, String password)

   at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.SetDatabaseConnection(String server, String database, ConfigurationCredentialsType credsType, String userName, String password)

 

Steps to reproduce the issue:

1. Existing SQL Server reporting Server is pointing to “ReportServerDB\SQL2008R2” instance and “sa” account is used to connect to the report server database.

2. Took the backup of the ReportServer and ReportServerTempDB database.

3. Installed SQL 2014 instance with Server name “Reportserverdb\SQL2014” and restored the backups of ReportServerDB and ReportServerTempDB taken on SQL 2008 R2 instance.

4. To migrate the ReportServer database to SQL 2014 instance, launched Report Server Database Configuration Wizard.

5. Report Server database configuration Wizard failed while “Setting DSN”

The error was not because of lack of permissions on the 2014 instance/database.

6. Concluded the above by connecting to the SQL 2014 instance using “sa” account.

7. I also concluded that the issue was not related to connectivity as I was able to successfully create User DSN and test the connectivity to SQL2014 instance.

 

Cause:

To isolate the issue, I collected Process Monitor data while reproducing the issue. Process Monitor tool can be downloaded from: https://technet.microsoft.com/en-us/library/bb896645.aspx

From the error stack:

System.UnauthorizedAccessException

  at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)

  at System.Management.ManagementObject.InvokeMethod(String methodName, ManagementBaseObject inParameters, InvokeMethodOptions options)

  at Microsoft.ReportingServices.WmiProvider.RSWmiAdmin.SetDatabaseConnection(String server, String database, Int32 credsType, String userName, String password)

  at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.SetDatabaseConnection(String server, String database, ConfigurationCredentialsType credsType, String userName, String password)

To set the database connection, WMI call is made. I filtered the Process Monitor data for WMI process (wmiprvse.exe)

  • From Process Monitor output, we are seeing “ACCESS DENIED” for “CreateFile” operation for the path: C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config
  • ReadFile operation is successful, indicating that rereportserver.config file exists.
  • RSreportserver.config file stores the settings that are used by Report Manager, the Report Server Web service, and background processing. From the properties of “rsreportserver.config” file, “Read-only” attribute is set.

Resolution:

I unchecked “Read-only” attribute and post which report Server database migration was successful.

 

I hope that the steps and the resolution in this blog were useful and saved you time.

 

Please share your feedback, questions and/or suggestions.

Thanks,

Don Castelino | SQL Server Support Team | CSS – Microsoft

 

Disclaimer: All posts are provided AS IS with no warranties and confer no rights. Additionally, views expressed here are my own and not those of my employer, Microsoft.