Tag Archive: SQL Server Reporting Services


SSRS 2016 Error when using HTTPS

My first surprise, I couldn’t simply enable HTTPS on the Web Portal (in Reporting Services Configuration Manager). Straight away this causes an error when trying to access https://host.domain/reports

The service is not available.

The report server isn’t configured properly. Contact your system administrator to resolve the issue. System administrators: The report server Web Portal URLs and Web Service URLs don’t match. Use Reporting Services Configuration Manager to configure the URLs and ensure they match.

Seemingly behind the scenes the web portal is communicating with the web service (and it doesn’t know to use a different URL), so this also needs configuring for HTTPS. After setting this up, I can hit the basic web portal pages but if I try and view my subscriptions for example I receive an error;

Not overly helpful, and doing some snooping in Chrome Developer Tools simply shows the HTTP request returning a 500 error.

There is nothing in the event log on the SSRS server, but there are a few lengthy error messages in the reporting services log files;

Microsoft.ReportingServices.Portal.WebHost!reportserverwebapp!6!09/04/2018-07:59:37:: e ERROR: [m9yd0rtt]: OData exception occurred: System.Net.WebException: The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel. ---> System.Security.Authentication.AuthenticationException: The remote certificate is invalid according to the validation procedure.
at System.Net.Security.SslState.StartSendAuthResetSignal(ProtocolToken message, AsyncProtocolRequest asyncRequest, Exception exception)
at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.ForceAuthentication(Boolean receiveFirst, Byte[] buffer, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.ProcessAuthentication(LazyAsyncResult lazyResult)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Net.TlsStream.ProcessAuthentication(LazyAsyncResult result)
at System.Net.TlsStream.Write(Byte[] buffer, Int32 offset, Int32 size)
at System.Net.ConnectStream.WriteHeaders(Boolean async)
--- End of inner exception stack trace ---
at System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebRequest request)
at System.Web.Services.Protocols.HttpWebClientProtocol.GetWebResponse(WebRequest request)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at Microsoft.SqlServer.ReportingServices2010.ReportingService2010.ListExtensions(String ExtensionType)
at Microsoft.SqlServer.ReportingServices2010.RSConnection2010.<>c__DisplayClass75.<ListExtensions>b__74()
at Microsoft.SqlServer.ReportingServices2010.RSConnection2010.SoapMethodWrapper`1.ExecuteMethod(Boolean setConnectionProtocol)
at Microsoft.SqlServer.ReportingServices2010.RSConnection2010.SoapMethodWrapper`1.ExecuteMethod()
at Microsoft.SqlServer.ReportingServices2010.RSConnection2010.ListExtensions(String extensionType)
at Microsoft.ReportingServices.Portal.Services.SoapProxy.SoapRS2010Proxy.<>c__DisplayClass41.<ListExtensions>b__40()
at Microsoft.ReportingServices.Portal.Services.SoapProxy.SoapAuthenticationHelper.ExecuteWithWindowsAuth[TReturn](SoapHttpClientProtocol soapClient, IPrincipal userPrincipal, Func`1 func)
at Microsoft.ReportingServices.Portal.Services.SoapProxy.SoapAuthenticationHelper.ExecuteWithCorrespondingAuthMechanism[TReturn](SoapHttpClientProtocol soapClient, IPrincipal userPrincipal, Func`1 func)
at Microsoft.ReportingServices.Portal.Services.SoapProxy.SoapRS2010Proxy.ListExtensions(IPrincipal userPrincipal, String extensionType)
at Microsoft.ReportingServices.Portal.Repositories.SystemService.ListExtensions(IPrincipal userPrincipal, ExtensionType extensionType)
at Microsoft.ReportingServices.Portal.Repositories.SystemService.PopulateLocalizedExtensionNames(IPrincipal userPrincipal, List`1 subscriptions)
at Microsoft.ReportingServices.Portal.ODataWebApi.V1.Controllers.SubscriptionsController.GetEntitySet(String castName)
at Microsoft.ReportingServices.Portal.ODataWebApi.V1.Controllers.Reflection.EntitySetReflectionODataController`1.GetRoot(ODataPath oDataPath, Int32& index)
at Microsoft.ReportingServices.Portal.ODataWebApi.V1.Controllers.Reflection.ReflectionODataController`1.Get(ODataPath oDataPath)
at lambda_method(Closure , Object , Object[] )
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass10.<GetExecutor>b__9(Object instance, Object[] methodParameters)
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary`2 arguments, CancellationToken cancellationToken)
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Controllers.ApiControllerActionInvoker.<InvokeActionAsyncCore>d__0.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__0.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__2.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Controllers.ExceptionFilterResult.<ExecuteAsync>d__0.MoveNext().

Microsoft.ReportingServices.Portal.WebHost!reportserverwebapp!18!09/04/2018-08:00:58:: e ERROR: [dq2umtxh]: OData exception occurred: System.Net.WebException: The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel. ---> System.Security.Authentication.AuthenticationException: The remote certificate is invalid according to the validation procedure.
at System.Net.Security.SslState.StartSendAuthResetSignal(ProtocolToken message, AsyncProtocolRequest asyncRequest, Exception exception)
at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.ForceAuthentication(Boolean receiveFirst, Byte[] buffer, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.ProcessAuthentication(LazyAsyncResult lazyResult)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Net.TlsStream.ProcessAuthentication(LazyAsyncResult result)
at System.Net.TlsStream.Write(Byte[] buffer, Int32 offset, Int32 size)
at System.Net.ConnectStream.WriteHeaders(Boolean async)
--- End of inner exception stack trace ---
at System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebRequest request)
at System.Web.Services.Protocols.HttpWebClientProtocol.GetWebResponse(WebRequest request)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at Microsoft.SqlServer.ReportingServices2010.ReportingService2010.ListExtensions(String ExtensionType)
at Microsoft.SqlServer.ReportingServices2010.RSConnection2010.<>c__DisplayClass75.<ListExtensions>b__74()
at Microsoft.SqlServer.ReportingServices2010.RSConnection2010.SoapMethodWrapper`1.ExecuteMethod(Boolean setConnectionProtocol)
at Microsoft.SqlServer.ReportingServices2010.RSConnection2010.SoapMethodWrapper`1.ExecuteMethod()
at Microsoft.SqlServer.ReportingServices2010.RSConnection2010.ListExtensions(String extensionType)
at Microsoft.ReportingServices.Portal.Services.SoapProxy.SoapRS2010Proxy.<>c__DisplayClass41.<ListExtensions>b__40()
at Microsoft.ReportingServices.Portal.Services.SoapProxy.SoapAuthenticationHelper.ExecuteWithWindowsAuth[TReturn](SoapHttpClientProtocol soapClient, IPrincipal userPrincipal, Func`1 func)
at Microsoft.ReportingServices.Portal.Services.SoapProxy.SoapAuthenticationHelper.ExecuteWithCorrespondingAuthMechanism[TReturn](SoapHttpClientProtocol soapClient, IPrincipal userPrincipal, Func`1 func)
at Microsoft.ReportingServices.Portal.Services.SoapProxy.SoapRS2010Proxy.ListExtensions(IPrincipal userPrincipal, String extensionType)
at Microsoft.ReportingServices.Portal.Repositories.SystemService.ListExtensions(IPrincipal userPrincipal, ExtensionType extensionType)
at Microsoft.ReportingServices.Portal.Repositories.SystemService.PopulateLocalizedExtensionNames(IPrincipal userPrincipal, List`1 subscriptions)
at Microsoft.ReportingServices.Portal.ODataWebApi.V1.Controllers.SubscriptionsController.GetEntitySet(String castName)
at Microsoft.ReportingServices.Portal.ODataWebApi.V1.Controllers.Reflection.EntitySetReflectionODataController`1.GetRoot(ODataPath oDataPath, Int32& index)
at Microsoft.ReportingServices.Portal.ODataWebApi.V1.Controllers.Reflection.ReflectionODataController`1.Get(ODataPath oDataPath)
at lambda_method(Closure , Object , Object[] )
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass10.<GetExecutor>b__9(Object instance, Object[] methodParameters)
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary`2 arguments, CancellationToken cancellationToken)
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Controllers.ApiControllerActionInvoker.<InvokeActionAsyncCore>d__0.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__0.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__2.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Controllers.ExceptionFilterResult.<ExecuteAsync>d__0.MoveNext().

Something is moaning about trust/SSL. I am using a valid wildcard certificate and don’t see any certificate related errors in the browser.

Digging around, google seems to suggest changing the “SecureConnectionLevel” parameter in rsreportserver.config to a 0, but it was already set to 0 on my server. This didn’t help.

In the end, I was able to resolve the issue by setting “SecureConnectionLevel” to 3 and adding the fully qualified server/domain name (FQDN) in the “UrlRoot” parameter (i.e. to https://host.domain) then restarting the service (either in Reporting Services Configuration Manager or Windows Service Manager).

Hope it works for you!

We recently migrated our internal reports from SSRS 2008 R2 to SSRS 2016 and had issues with a custom assembly / dll.

The deployment process seemed roughly the same (this time copying the .dll to C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer\bin and modifying rssrvpolicy.config in c:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer to give FullTrust), but while this allowed us to deploy the report and didn’t complain about the file being missing; we were getting #Error returned by the report.

After a lot of messing around, it turns out the .dll was set to target .net framework 3.5 (this was a requirement for SSRS 2008 R2). Now however, it seems the assembly needs to target .net framework 4 (for SSRS 2016). Once I changed this in Visual Studio, recompiled and deployed the new .dll, voila!

In the past I’ve been a bit blasé with security- where something doesn’t work I would just add myself to the local administrator group and voila! Now I’m trying to better my habits and, when something doesn’t work, find out exactly what needs to happen to get it working. Today I was trying to publish some reports to a brand new deployment of SQL Server Reporting Services (SSRS) 2008 R2.

When first accessing the report server from a web browser, unsurprisingly I got a permissions error:

User ‘domainusername’ does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.

So I jumped onto the report server as an administrator and from the Site Settings area assigned the System Administrator to my account:

Still the same error:

This time I tried defining security at the root node (home) folder:

And now- everything works:

Following on from my previous blog entry about SSRS permissions- I now wanted to connect to Reporting Services from SQL Server Management Studio:

Connect to Server -> Cannot connect to serverinstance -> Additional Information: A connection to the computer cannot be established. (Microsoft.SqlServer.Management.UI.RSClient) -> Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) (mscorlib)

This was resolved by adding my account to the Distributed COM Users:

Now the connection is successful from Management Studio:

Although I was expecting to see folders, reports, data sources, datasets etc:

But I’ll save that for another time!

L

Microsoft SSRS (vs Crystal Reports)

I started using SSRS yesterday and am already growing to love it (i’ve been using Crystal Reports for about 3years!).

Top points to note so far:

-Dynamic sorting is handled within SSRS (requires manual work-around in Crystal Reports, although i believe a dynamic sort control is being delivered in Crystal Reports 2010)
-Dynamic grouping appears to be simpler
-Multiple datasets can be used within the same report (Crystal requires a single dataset per report/sub-report)
-Dynamic Parameters can have “complex” queries to return the list of values (CR only allows you to pick a field from the main report’s datasource)
-Parameter Default values can be calculated using a formula or SQL statement (this is a god send- allows you to default start/end dates for monthly reports for example to the 1st/last of the month- not possible in CR)
-Collapsable groupings (i’ve not used drill-down sub-reports in CR- these may offer “similar” functionality, but  i don’t think on the same level)

There’s more that i’ve forgotten or not yet realized :) I think a lot of it boils down to SSRS being part of Visual Studio and being able to harness a lot of it’s capabilities… that’s all for now.

On a different note: I finally jumped on the Skype bandwagon- search: leetickett

L

%d bloggers like this: