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

Question

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;
}

Answer 1

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

Answer 2

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/