SQL Server login error when accessing SSRS web service from Asp.net core 3.1

Käzz 1 Reputation point
2020-11-27T23:27:56.333+00:00

My ASP.Net Core 3.1 App programmatically generates reports on SSRS. I've added a Connected Service reference to the SSRS web service in my app.
The App basically builds RDL and sends the RDL over to the SSRS Service using the service reference. The RDL contains an SQL query that's used to pull the data for the report.

The Problem is the SQL Server is returning an error - Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' - here's the entire error message from the SSRS service:

Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'MyDataSource'. ---> System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITY\\ANONYMOUS LOGON'

Here's the code snippet:

using System.ServiceModel;
using System;

var binding = new BasicHttpBinding();
binding.Security.Mode = BasicHttpSecurityMode.TransportCredentialOnly;
binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Windows;  // tried with 'Ntml' also didn't work           

var endpointAddress = new System.ServiceModel.EndpointAddress(ssrsUrl);

using(var proxy = new ReportExecutionServiceSoapClient(binding, endpointAddress))
{
 var trustedUser = new TrustedUserHeader();
 var loadRequest = new LoadReportDefinition2Request(trustedUser, rdl);

 var loadResponse = await proxy.LoadReportDefinition2Async(loadRequest);

 var deviceInfo = DeviceInfo(request.RenderOptions.CsvExcelMode);
 var renderRequest = new Render2Request(loadResponse.ExecutionHeader, trustedUser, format, deviceInfo, PageCountMode.Estimate);

 var renderResponse = await proxy.Render2Async(renderRequest);                    
 result = renderResponse.Result;
}
.NET
.NET
Microsoft Technologies based on the .NET software framework.
3,347 questions
ASP.NET Core
ASP.NET Core
A set of technologies in the .NET Framework for building web applications and XML web services.
4,144 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,793 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. ZoeHui-MSFT 32,506 Reputation points
    2020-11-30T05:56:02.317+00:00

    Hi @Käzz ,

    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

    It seems like you're running into a "double-hop" issue.

    There are quite a few steps required to get the delegation to work, such as trusting the servers for delegation, creating SPNs and making sure that other proper permissions are given to the account that IIS is using to run the web site. Here is an article that can help take you through a lot of the required steps here:

    https://learn.microsoft.com/en-us/archive/blogs/taraj/checklist-for-double-hop-issues-iis-and-sql-server

    For more details, please refer this link:

    https://stackoverflow.com/questions/10957443/web-app-getting-login-failed-for-user-nt-authority-anonymous-logon

    https://learn.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2003/cc739764(v=ws.10)?redirectedfrom=MSDN

    Regards,
    Zoe


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

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    What can I do if my transaction log is full?--- Hot issues November

    How to convert Profiler trace into a SQL Server table -- Hot issues November

    0 comments No comments

  2. Duane Arnold 3,211 Reputation points
    2020-12-14T17:24:06.617+00:00

    You could just simply add NT AUTHORITY\ANONYMOUS account to login to the database with appropriate permissions to access the database. I would assume that the solution is running on a protected Windows domain, which are the credentials presented to the database the program is presenting.

    https://www.toolbox.com/security/network-security/question/what-is-anonymous-logon-122707/

    0 comments No comments