Unable to run SSRS reports at new site in AWS.

Kashif Akram 0 Reputation points

I have 2 sites everything works on site 1 (existing site). I can connect to SSRS and can run reports but on site 2 which has its dc in the same domain as site 1, I'm unable to run the SSRS reports in site 2 with the error Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. The only way I can run the reports on the second site is to Run the report on the existing site and then immediately run the report on the second site and it works fine. It seems that the process uses the same Kerberos ticket issues by site 1.
I'm using impersonating the user id. Note: SQL and SSRS servers are in the same domain and the user login is in a different domain. Both domains have a trust relationship in place. My feeling is Site 2 dc is not issuing Kerberos tickets but I can be wrong as all other operations are working fine e.g. replication between Site 1 and Site 2. I can join computers to the domain and log in to them on site 2 and when querying the logon server it shows the right server as per the site defined in ad sites and services. Any suggestions,

Windows Server 2019
Windows Server 2019
A Microsoft server operating system that supports enterprise-level management updated to data storage.
3,510 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,827 questions
Active Directory
Active Directory
A set of directory-based technologies included in Windows Server.
5,997 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. ZoeHui-MSFT 33,626 Reputation points

    Hi @Kashif Akram,

    If your SSRS setup similar to the one below. You're likely running into a common SSRS Reporting Service Double Hop (aka Negotiate / SPNego / Kerberos/ SSO) issue.

    Clients e.g. PCs -> SQL Server A (Hop 1) with SSRS -> SQL Server B (Hop 2) contains DBs where your SSRS data sources are pointing to.

    There're 2 solutions to this.

    Solution 1 is to hardcode credential in each Data Source.

    Solution 2 is to configure Kerberos Authentication (SPN + Account Delegation). Check it out here: https://redmondmag.com/articles/2010/08/23/reporting-services-double-hop-authentication.aspx

    Regards, Zoe Hui

    If the answer is helpful, please click "Accept Answer" and upvote it.