Kerberos authentication fails with SQL Server connector in embedded analytics reports

BA/BI SQL Server Reporting Services (SSRS)
, , , , , , , ,
1

We’re embedding SSRS 2014 reports in our customer portal using SQL Server as the data source. Authentication works fine when users access Report Manager directly, but embedded reports fail with “Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’” errors. The SQL Server connector is configured to use Windows Authentication with stored credentials, and we’ve verified the service account has proper database permissions. The SPN registration looks correct for both SSRS and SQL Server services, but constrained delegation might be the issue. Has anyone successfully configured Kerberos authentication for embedded analytics scenarios? Our users are completely blocked from loading embedded reports while direct access works normally.

2

One additional consideration for the data source credential settings - even with delegation working, you might need to configure the unattended execution account as a fallback. Some embedded scenarios require this when the credential flow gets complex. It’s in rsreportserver.config under the UnattendedExecutionAccount element. This isn’t a replacement for proper delegation, but it can help with edge cases where the security context gets lost between hops.

3

I’ve dealt with this exact scenario multiple times. The ANONYMOUS LOGON error is definitely a delegation problem, and here’s the complete solution.

First, verify your SPN registration is correct for both services:


setspn -L domain\ssrs_service_account
setspn -L domain\sql_service_account

You should see HTTP/reportserver.domain.com and MSSQLSvc/sqlserver.domain.com:1433 registered under their respective accounts.

For constrained delegation configuration in Active Directory:

  1. Open Active Directory Users and Computers
  2. Locate the SSRS service account
  3. Properties > Delegation tab
  4. Select “Trust this user for delegation to specified services only”
  5. Choose “Use any authentication protocol” (enables protocol transition)
  6. Add Services > locate SQL Server computer account
  7. Add both MSSQLSvc/sqlserver.domain.com:1433 and MSSQLSvc/sqlserver.domain.com SPNs

For data source credential settings in Report Manager:

  • Navigate to the shared data source
  • Connection Type: Microsoft SQL Server
  • Connection String: Data Source=sqlserver.domain.com;Initial Catalog=YourDB
  • Credentials: “Credentials stored securely in the report server”
  • Enter a Windows account with database permissions (domain\report_reader)
  • Check “Use as Windows credentials when connecting to the data source”
  • Test the connection

Additional verification steps:

  1. Check IIS application pool identity for the embedding application - should match the identity that will be delegated
  2. Verify kernel-mode authentication is disabled in IIS for the embedding app
  3. In rsreportserver.config, ensure RSWindowsNegotiate is listed before RSWindowsNTLM:
<Authentication>
  <AuthenticationTypes>
    <RSWindowsNegotiate/>
    <RSWindowsNTLM/>
  </AuthenticationTypes>
</Authentication>
  1. Restart SSRS service after any config changes
  2. Test with a simple embedded report first before moving to complex scenarios

The key issue is that embedded reports require the full Kerberos delegation chain: User > Web App > SSRS > SQL Server. Each hop must be configured for delegation, and the SPNs must be registered correctly. The “stored credentials” approach in the data source is actually the most reliable for embedded scenarios because it provides a consistent security context that SSRS can delegate to SQL Server, rather than trying to flow the end user’s credentials through multiple hops.

After implementing these changes, monitor the Security event log on the SQL Server for successful Kerberos authentication events (Event ID 4624 with Logon Type 3) to confirm delegation is working properly.

4

Thanks for the responses. We’re using Windows Authentication throughout. I checked the SPNs with setspn -L and found both HTTP SPNs registered correctly. However, when I looked at the delegation settings in AD, the SSRS service account shows “Do not trust this user for delegation” - that’s definitely wrong. Our security team needs to approve the change, but this looks like the root cause. The embedded app passes credentials through IIS with kernel-mode auth disabled, so the delegation chain should work once AD is configured properly.

5

I’ve seen this before. The ANONYMOUS LOGON error typically means Kerberos delegation isn’t configured properly. Even if SPNs are registered correctly, you need constrained delegation set up between the SSRS service account and SQL Server. Check Active Directory Users and Computers - find your SSRS service account, go to Delegation tab, and ensure it’s set to “Trust this user for delegation to specified services only” with the SQL Server SPN listed. Also verify the data source credential settings in Report Manager are using “Credentials stored securely in the report server” with the correct Windows account.

6

You’re on the right track. One more thing to check - make sure your SQL Server SPN is in the delegation list using the fully qualified format. It should be MSSQLSvc/sqlserver.domain.com:1433 not just the short name. Also verify that “Use any authentication protocol” is selected if you’re on Server 2012 or later, as protocol transition is often needed for web-based scenarios. I’d also recommend testing with setspn -Q to ensure there are no duplicate SPNs registered, as that causes intermittent failures that look like delegation issues.