3

Myself and another admin have been trying to troubleshoot the double-hop authentication issue on our new reporting server (where the report runs as the authenticated user, but cannot delegate to the data source and returns a 'NT Authority\Anonymous’ authentication error) and we seem to be hitting a dead end. Here is a bit about our setup.

We have SSRS 2016 installed on one server, and our data source for reports is on another server. We have done the following steps to try and enable authentication

  1. Set Group Policy on the Reporting Server to enable delegation of credentials
  2. Ensured that SSRS and MSSQLsrv on the report server have the correct SPN set for the service account running both services:
    • http/NetBIOS:80 domain\servicename
    • http/FQDN.contoso.com:80 domain\servicename
    • mssqlsvc/FQDN.contoso.com:1433 domain\servicename
  3. Enable Kerberos Delegation on the Service account, and ensure the "Account is sensitive and cannot be delegated." box is not checked
  4. Switched the Tag to <RSWindowsNegotiate/> in the RSReportServer.config file
  5. Restarted the Service

We are still receiving the error when trying to connect data sources on the report server and even issues connecting to web page via browser. Strangely enough we tried setting up a link server on the report server configuring the security to run under the user executing the command and were able to successfully do a double-hop on the link server, but still not from SSRS.

Does the datasource need to be running under a service account, or can it run under the local service account for SQL? Would the service account need to have delegated rights to the OU in which the Servers exist? What other items may I be missing?

joshcorr
  • 101
  • 2
  • 10

2 Answers2

6

We finally solved the problem after 6+ months.

After setting the SPN for the SSRS service account (you only need http/NetBIOS:80 domain\servicename and http/FQDN.contoso.com:80 domain\servicename) you need to set the Delegation tab on the SSRS service account to contain either "Trust this user for delegation to any service" or on the "Trust this user for delegation to specified services only..." select the MSSQLSRV service from the Data Source you are trying to query.

We had mistakenly thought that this delegation was for specifying which services on the report server needed to be allowed to pass the Kerberos ticket, and not which servers this service account was allowed to send them to on behalf of the user.

As a result learned that it doesn't matter what service account the data source sql server is running as long as it has the correct SPN and OU delegation has nothing to do with this type of Kerberos delegation.

joshcorr
  • 101
  • 2
  • 10
  • 1
    We had to select the MSSQLSvc service from the service account running the SQL Engine, and then restart the SSRS services. But this worked, thanks – Luke Apr 17 '18 at 09:31
0

The key here is to add a registry key to enable Kerberos for the new Chromium Edge/Chrome.

HKLM\SOFTWARE\Policies\Microsoft\Edge\AuthNegotiateDelegateAllowlist HKLM\SOFTWARE\Policies\Google\Chrome\AuthNegotiateDelegateAllowlist

the value of this key should be the SSRS server name.

Note this has to be on the end user's machine, not on the SSRS server machine.

See my full answer to enable double hop for SSRS on another thread at https://stackoverflow.com/a/76210263/1622919

Mike
  • 91
  • 1
  • 2