2005 Reporting Services Configuration Issues

Hi All -

I am trying to configure reporting services on virtual machine but unable to succeed. I never touched this topic before SQL Server 2005. The left hand side options in Reporting Services Configuration Manager such as Server Status, Virtual Directories, Web Identity works fine but it fails in Database Setup section

Here are the details:

AppServer: SQL Server 2005 Reporting Services & Client components build# 2153 (with SP1 & SP1 hotfix)

going to apply SP2 and Add-on component after the configuration

DBServer: SQL Server 2005 Database Services & Client components build# 2153 (with SP1 & SP1 hotfix)

OS: Windows 2003 Server with SP1

IIS version on AppServer: 6

I have SQL 2005 Reporting services and 2005 Database Engine on 2 different machines.

Here is the error message I am receiving when I try to apply the script in "Database Setup" section of Reporting Services Configuration Manager

It did not log any errors in SQL Server however it shows a warning message in Event Log

Event Log message - "The RSReportServer.config file has been modified."

Configuration Manager error message - It shows me "Create a Grants Rights Script" task failed

"System.UnauthorizedAccessException: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(Object o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementObjectSearcher.Initialize()
at System.Management.ManagementObjectSearcher.Get()
at Microsoft.ReportingServices.Diagnostics.WebUtil.GetIPAddresses(String machine)
at ReportServicesConfigUI.Panels.DatabaseSetupPanel.IsLocalDbServer(String dbServer)
at ReportServicesConfigUI.Panels.DatabaseSetupPanel.SetDatabaseConnectionTask(String connectionString, String server, String database, ConfigurationCredentialsType credsType, String account, String password, Boolean upgrade, String dbVersion)"

I have captured the screenshot, let me know if I can send this.

One of my friends suggested the following and I have tried the first option but could not work - Option I)

Our account that is used for database connection should have

1) public and RSExecRole roles for the ReportServerDatabase.

2) RSExecRole roles for the master, msdb , and ReportServerTempDB databases.

Else - Option II)

we can run the Reporting Service Windows Service under the local System Account and then the credentials type for the database set up would be Service credentials. This way we would have more permission.

Did anyone face this problem? how should I resolve this?

Anyone have steps to follow as I feel of missing something in the configuration steps?

Any information/ suggestions/ pointer on this are higly appreciated.

Thanks in advance !!

-Satya

We were getting this error and found that the dbo.role was not the owner of the dbo.schema. When we made that change we were able to update the database. When we originally installed and had problems with the remote database we had to make sure that database had windows authentication only.|||

TWe also escalated this issue to Microsoft Product Support Service and they suggested the following:

The account that is used for database connection should have

1) public and RSExecRole roles for the ReportServerDatabase.

2) RSExecRole roles for the master, msdb , and ReportServerTempDB databases.

Else we can run the Reporting Service Windows Service under the local System Account and then the credentials type for the database set up would be Service credentials. This way we would have more permission.

but before we had a work around however you will still find the script showing error in Reporting Services configuration manager.

We have generated the login script and executed thru' Management Studio. Verified the account permission as mentioned above.

Just ignore the error message that you see in Configuration Manager -

Configuration Manager error message - "System.UnauthorizedAccessException: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))