reporting-servicesssrs-2017

SSRS Web Service URL Provides an Error (500)


When I try to access the SSRS Web Service URL (e.g http://SERVERNAME:1234/ReportServer/), it provides me with an Error 500. However, the Web Portal URL (http://SERVERNAME:1234/Reports/) works just fine. It even provides me with the UI for the SQL Server Reporting Services.

So far I've tried re-installing the SSRS, changing the ports, accounts and the database, all without any luck (the Reporting Services service is running). I am genuinely out of ideas and Microsoft's documentation is terrible regarding this particular issue as it suggested attempting to modify the NETWORK SERVICE account permissions, which can be done after adding one of the Windows features (which, I may add, can be done only on the Enterprise version of WIndows 10).

Has anyone had this issue or managed to find a workaround?


Solution

  • The instance of SSRS has two web applications, which are pretty independent. Normally they have such URLs:

    1. http://ServerName/Reports
    2. http://ServerName/ReportServer

    In your case, seems first one configured and work correctly and the second one failing. The reason of a failure can be found in log files that are in stored in a SSRS instance folder, for instance:

    D:\SQLData\MSRS13.MSSQLSERVER\Reporting Services\LogFiles

    This path can be different, depends what was chosen during the initial installation as the instance directory. It can be found by opening a task manager and discovering a file location of ReportingServicesService.exe:

    enter image description here

    Then, in the LogFiles directory open the most recent log file and check for messages with a category ERROR, like:

    ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: , Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset 'RequestTypeList'. ---> System.Data.SqlClient.SqlException: Invalid object name 'dbo.FactHistoricTrend'.
    Could not use view or function 'dbo.FactFlatSSRS_Trend' because of binding errors.
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
       at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
       at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
       at System.Data.SqlClient.SqlDataReader.get_MetaData()
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
       at Microsoft.ReportingServices.DataExtensions.SqlCommandWrapperExtension.ExecuteReader(CommandBehavior behavior)
       at Microsoft.ReportingServices.OnDemandProcessing.RuntimeLiveQueryExecutor.ExecuteReader(IJobContext jobContext, DataSourceErrorInspector errorInspector, String commandText)
       --- End of inner exception stack trace ---;
    

    In this way, you will know what is the reason of Error 500 of your ReportServer web service