I was recently working with a client with a SQL Server Reporting Services (SSRS) issue. Their company has standardized on using Google Chrome for the browser. However, they were running into issues when using Google Chrome with SSRS reports.
The first issue was that they were receiving a log in prompt to the SSRS server when browsing to it. The second issue was the infamous Kerberos Double-Hop issue. If you’re not familiar with the Kerberos Double-Hop architecture, check out this link: https://docs.microsoft.com/en-us/archive/blogs/askds/understanding-kerberos-double-hop.
In SSRS, the Kerberos Double-Hop issue is seen by receiving the following message when running a report:
This message indicates that somewhere along the line, the Kerberos ticket wasn’t used. The system has started using the generic “NT AUTHORITYANONYMOUS LOGIN” account for trying to connect to other computers. In this case, the SSRS report is using a data source that connects to a different computer.
Showing SSRS errors remotely
By default, SSRS does not display these error messages remotely. You need to configure SSRS to do so. This link (https://docs.microsoft.com/en-us/sql/reporting-services/report-server/enable-remote-errors-reporting-services) explains what you need to do.
Once SSRS was configured to show the error messages remotely, I could see the error message in the image above. This is a common error message, and I recognized it as the Kerberos Double-Hop issue.
Troubleshooting
My first troubleshooting tool was to ensure that Kerberos was properly set up. I used the Kerberos Configuration Manager for SQL Server (download at https://www.microsoft.com/en-us/download/details.aspx?id=39046) to ensure that everything was configured correctly. If you are using an Availability Group (AG) listener, ensure that a Service Principal Name (SPN) is created for this on each cluster node that can be running that AG.
I finally utilized another browser (Internet Explorer) to verify that things would work properly with SSRS with that browser. It did, so we are facing a difference between browsers and how they work with Kerberos.
Google Chrome and Windows Auto-Login
The first issue to resolve is the credential prompt when browsing to the SSRS server. My clue to resolving this came from https://sysadminspot.com/windows/google-chrome-and-ntlm-auto-logon-using-windows-authentication/. The solution was to add the server name and the fully qualified domain name of the server to the trusted sites. As indicated in the linked article, we also needed to set the “User Authentication” to “Automatic login with current user name and password”.
Once both of these two settings were made, Google Chrome was now automatically passing the credentials to log into the SSRS server, so that a prompt for the credentials was not displayed when browing to the SSRS Server.
However, this did not fix the Kerberos Double-Hop issue.
Google Chrome and Kerberos Double-Hop
Researching Chrome and Kerberos eventually led me to this link (https://www.chromium.org/developers/design-documents/http-authentication), where I read about the Kerberos Credentials Delegation (Forwardable Tickets). Hey, this sounds exactly like a Double-Hop. This lead me to the AuthNegotiateDelegateWhitelist policy in Chrome, at https://dev.chromium.org/administrators/policy-list-3#AuthNegotiateDelegateWhitelist.
Using RegEdit, I created this registry key with the value containing the server name and fully qualified domain name of the server for all servers that are part of this Availability Group (and which could potentially be running the SQL Server instance that the report connects to).
And this corrected the Kerberos Double-Hop Issue when using Google Chrome with SSRS.
Automation
One of the concerns the client had was that they didn’t want to require someone to go to every workstation to make this work. Thankfully, all of these changes are registry entries, so a domain Group Policy Object (GPO) could be created to push these changes out to the workstation automatically.
This Microsoft link (https://support.microsoft.com/en-us/help/182569/internet-explorer-security-zones-registry-entries-for-advanced-users) guides us to the Internet Explorer registry entries to make for the trusted sites zone.
An alternative method of implementing this would be to create a *.reg file, where the entries can be merged into the registry by simply double-clicking it. This file would contain:
Windows Registry Editor Version 5.00 [HKEY_CURRENT_USERSoftwareMicrosoftWindowsCurrentVersionInternet SettingsZones2] "1A00"=dword:00000000 [HKEY_CURRENT_USERSoftwareMicrosoftWindowsCurrentVersionInternet SettingsZoneMapEscDomainsMyDomain.com] [HKEY_CURRENT_USERSoftwareMicrosoftWindowsCurrentVersionInternet SettingsZoneMapEscDomainsMyDomain.comMySSRSServerName] "http"=dword:00000002 [HKEY_CURRENT_USERSoftwareMicrosoftWindowsCurrentVersionInternet SettingsZoneMapEscDomainsMyDomain.comMyAGListenerName] "http"=dword:00000002 [HKEY_CURRENT_USERSoftwareMicrosoftWindowsCurrentVersionInternet SettingsZoneMapEscDomainsMySSRSServerName] "http"=dword:00000002 [HKEY_CURRENT_USERSoftwareMicrosoftWindowsCurrentVersionInternet SettingsZoneMapEscDomainsMyAGListenerName] "http"=dword:00000002 [HKEY_LOCAL_MACHINESoftwarePoliciesGoogleChrome] "AuthNegotiateDelegateWhitelist"="MyAGListenerName,MyAGListenerName.MyDomain.com,AGNode1,AGNode1.MyDomain.com,AGNode2,AGNode2.MyDomain.com"
You will need to replace “Mydomain”, “MySSRSServerName”, “MyAGListenerName”, “AGNode1” and “AGNode2” as necessary to fit your environment.
Registry Key explanation
The first registry key sets the User Authentication to “Automatic login with current user name and password”.
The second registry key add your domain to the trusted servers. The third and fourth registry keys add your SSRS Server and AGListener names to this domain.
The fifth and sixth registry keys add your SSRS Server and AGListener names to the trusted sites, without being part of a domain name.
The seventh registry key adds the AGListener and all nodes of the Availability Group (both server name only, and fully qualified domain names) to the Google Chrome “AuthNegotiateDelegateWhitelist” policy.
If you’re not using an Availability Group Listener, change AGListener to the name of the SQL Server instance that the report’s data source goes to.
In Conclusion:
Using Google Chrome with SSRS, especially when the reports are on a different server, is certainly possible. It does require jumping through a few hoops. The hoops can be performed in a GPO to make it easy to apply across your company easily.
The post Using Google Chrome with SSRS appeared first on Wayne Sheffield.