The strange case of Data source can’t be created with Reporting Services 2016 in Azure VM

 

Recently we were investigating a case reported by one of the Reporting Services customers where he was able to render reports, but every time he tried to create a datasource he got the following error.

This is a very common scenario where the team has plenty of coverage during coding , testing and dogfooding, I was very surprised that it wasn’t working but you still could render reports and navigate the portal.

The next step is to check the logs (in a default installation would be in C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\LogFiles), something to be aware is that Reporting Services 2016 has introduced a new log file for the new portal , those logs start with Microsoft.ReportingServices.Portal for example Microsoft.ReportingServices.Portal.WebHost_06_19_2016_15_59_43.log, we still have the old logs for any operation that interact with the ReportServer , including calls to the SOAP endpoint, report execution and background operations.

The log was showing a 401 unauthorized error.

 Microsoft.ReportingServices.Portal.WebHost!reportserverwebapp!11!06/19/2016-16:00:48:: e ERROR: [56x1bucj]: 
OData exception occurred: System.Net.WebException: The request failed with HTTP status 401: Unauthorized.
   at Microsoft.SqlServer.ReportingServices2010.RSConnection2010.SetConnectionProtocol()
   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()

 

From the error is easy to identify that the Portal is calling the Report Server in order to retrieve the list of extensions available to create the datasource, but the mystery still remains, if possible to connect to the portal https://myazurevm.westus.cloudapp.azure.com/reports and is also possible to connect to the reportserver https://myazurevm.westus.cloudapp.azure.com/reportserver, so clearly the permissions are ok.

Next step is either to attach the debugger or to use a network monitor like wireshark or Microsoft Netmon and figure out what is going on the machine, lets try the network monitor

There is something fishy here, wireshark by default isn’t able to capture local traffic so seeing the call between the portal and the reportserver is interesting, also the source and the destination of the calls are from different IP addresses and the portal process and reportserver are in the same machine

Now lets check the authentication challenge / response for that 401 and looks like is trying to use NTLM (for a good reference on how does it work , check my favorite https://blogs.msdn.microsoft.com/chiranth/2013/09/20/ntlm-want-to-know-how-it-works/)

Reporting services setup configures different default authentication based on your selections on the setup (all of them detailed here https://msdn.microsoft.com/en-us/library/cc281253.aspx), I checked mine and was configured <RSWindowsNTLM /> which maps the response I observed in the network monitor and well you can’t impersonate when you are using NTLM, to rule out wasn’t another issue I tried the NOT RECOMMENDED option of enabling basic and everything started to work (also the credentials were traveling as clear text in the network so at least SSL is configured it is a configuration to avoid), other option for credentials delegation is to configure Kerberos properly but that is a beast that I’m not ashamed to confess I haven’t tamed (once in a while and after a lot of reading and experimenting works for me).

The real clue here is why the portal is trying to contact the report server in a different IP address, so lets check the Reporting Services Configuration Tool for the Report Server Web Service URL and the Web Portal URL

Here is the clue, the only available url for the Portal to talk with the Report Server Web Service is the azure public DNS name

There are two solutions for this issue

Easy Solution

Add all the assigned IP addresses to the Report Server HTTP URL

 

Advanced Solution

Use the same procedure documented for https://msdn.microsoft.com/en-us/library/cc281307.aspx, check for the step “Add the NetBIOS and Fully Qualified Domain Name (FQDN) for the host header to the list of BackConnectionHostNames stored in the Windows Registry”

The summary of instructions are

1. Add a new DWORD value in the registry path HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\LanmanServer\Parameters

Value name: DisableStrictNameChecking
Data type: REG_DWORD
Base: Decimal
Value: 1

2. Add a new Multi-String value in the registry path HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\MSV1_0

Value name: BackConnectionHostNames
Data type: Multi-String
Value: ssrs20163.westus.cloudapp.azure.com