Kerberos Issue: NT Authority\Anonymous Login Failed – Issue Resolved!

Kerberos Issue: NT Authority\Anonymous Login Failed issue occurs when Domian User Account is not trusting SQL Server Service Account, due to multiple VM’s configuration. In the below Kerberos flow diagram, you can understand the how the auth will function and what is the work around for this issue.

Kerberos Issue:

  1. This is the portal when you selectAs the user viewing the report you will get this kind of error when you click on test connection.
Kerberos Issue
  1. Now when you directly browse the report which is associated to SQL Data source, you will this kind of error in the web portal logs.
Kerberos Issue
  1. If you check the log of SSRS/PBIRS you will find the same kind of error details, as you can in the below list.
Kerberos Issue

Kerberos Flow:

Kerberos Issue
Note: This flow has all the details like, Domain Account, Service Account Used, Domain Controller, Machine Name with FQDN and other Required details. All the below configuration (SPN’s & Delegation) is as per this flow diagram of Kerberos.

SSRS Machine Setup:

My Configuration:

  • SSRS Machine is logged in with Domain User “Vickey\SSRSAdmin”
  • SSRS is running under service account “Vickey\SAAccount”
  • SSRS Machine name with FQDN is “SSRSServer.Vickey.Lab”
  • SSRS URL is http://SSRSServer/ReportsSSRS

Step 1: If you want to setup Kerberos then the following tag must be added, to set as the first Authentication types in the “rsReportServer.config” file.

image 18

Step 2: Set the SPN’s with the Configuration Details as shown above. Please open CMD with administrator rights and run these below commands as per your configuration.

SPN’s on SSRS/PBIRS

SETSPN -S http/SSRSServer Vickey\SAAccount
SETSPN -S http/SSRSServer.Vickey.Lab Vickey\SAAccount

SPN
Note: The SPN must be set for the NETBIOS name of the server and the FQDN. If you access the reports using a host header or DNS alias, then that should also be registered SETSPN -S http/www.ReportsSSRS.com Vickey\SAAccount

Delegation settings will be enabled in the SSRS service account. But for that we first need to make sure, the Server’s is set with proper Service principal name. So, we will see delegation as the final piece of the configuration.

SQL Machine Setup:

My Configuration

  • SQL is running under service/logon account “Vickey\SQLAdmin”
  • SQL Machine is loged in with Domain Account “Vickey\SQLAdmin”
  • SQL Machine with FQDN name is “SQLServer.Vickey.Lab
  • SQL Service is listening on port 1433 (SQLDB). If it is default SQL Server Instance name, then no need to specify “Port Number” or you can also specify SQL Server name in my case it is “SQLDB.”
  • Note: If SQL is a named instance, then no doubt it is listening on a different port, then that port needs to be mentioned in the SPN.

Now we have all the required details to set SPN’s for SQL Server account. So, considering all the below details we will proceed further to set the SPN’s. Open command prompt in you SQL Machine and run these below commands.

SPN’s on SQL

Setspn –s MSSQLSvc/SQLServer:1433 Vickey\SQLAdmin
Setspn –s MSSQLSvc/SQLServer.Vickey.Lab:1433 Vickey\SQLAdmin

setspn -s MSSQLSvc/SQLServer.Vickey.Lab:SQLDB Vickey\SQLAdmin

SPN
Note: In my case Machine login User and SQL Service Account are same. By default, the SQL Server Logon account will be “NT Service\MSSQL$SQLSSRS” Logon Account. so, make sure to change it to “Domain User Account”image 15

DC or AD Delegation Setup:

Remember the SPN’s which we set for SSRS Service Account and SQL Service Account, in my case I am listing below the SPN’s configured, so that we can call that SPN’s and make use of it to configure the Delegation.

image 17
image 21

Trust Delegation Setup

Step 1: Browse your Active Directory/Domain Computers and go to users, locate the SSRS Service Account in my case it is this “Vickey\SAAccount”

Step 2: Go to delegation tab and select “Trust this user for any delegation to specified service only” and select “Use Kerberos only (Default)” and proceed further as per below screenshot. For More Details: https://learn.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2003/cc739764(v=ws.10)?redirectedfrom=MSDN

Delegation

Step 3: In “Users or Computer” option call the SQL Service Account, which you have configured the Delegation’s as mentioned in the below image. In my case I am using the SQL Service Account as “VICKEY\SQLAdmin” (For more see SQL Machine Configuration).

Delegation

Step 4: List of all the Services will be listed, as you can see that I have set the SPN’s for SQL Service Account so I will “select all” and click on add. So that following SPNs that will be trusted for delegation. This is the same Service Account SPN’s which we have set in (SQL Machine Configuration).

image 24

Step 5: All the list of services selected for delegation will be added and then at last click on Apply.

Trust delegation

Note: If “RSWindowsNegotiate” is set without the http spn then it will fall back to NTLM protocol and sometimes NTLM protocol can fail if you do not have disableLoopBackCheck enabled.

SQL Server Reporting Services (SSRS) offers several configurable options for authenticating users and client applications against the report server. By default, the report server uses Windows Integrated authentication and assumes trusted relationships where client and network resources are in the same domain or in a trusted domain.

Troubleshooting Steps:

If this does not work, then possibilities we have to collect some logs or check the Windows event files too more details. Please finds the below points if it could help on the issue.

  1. If you are getting the same error, then connect to the portal from a client machine and do the following steps:
  2. Then on the SSRS open the windows event logs and look for the security log for more information.
  3. Then on the SSRS open the windows event logs and look at the security log.
  4. Look at the logon events and find the one for the connection that was just made.
  5. Look at the detail of the event and if you scroll down, you will find authentication type.
  6. If it says NTLM then Kerberos is failing on the first hop.
  7. If it says Kerberos, then it is failing on the second hop.
  8. If the first hop is failing collect a network trace from the client machine while making a connection to the portal.
  9. If the second hop failed, then collect network trace on the SSRS server when reproducing the issue. 

Conclusion:

Kerberos Issue is very common when it comes to end user access, suppose if some domain user is trying to access the SSRS/PBIRS URL with his Domain User Account then for sure it is going to fails, because the end user is not trusted by the SQL Server Account. Kerberos issue can be in Single Hop, multiple Hop depends on how the organization has configured it.

Also, this applies to different domain user who don’t have access to this domain. But if you create trust between the 2 different domains for sure this Kerberos auth will work.

SQL Bi Links:

  1. Port Number For Analysis Services – Find Ports For SSAS? (kingfishertechtips.in)
  2. RS.exe Utility: Migrate RDL Reports To Another SSRS/PBIRS (kingfishertechtips.in)
  3. Direct Query Mode ON In Tabular Model Issue – Visual Studio (kingfishertechtips.in)
  4. SMTP Server In SSRS/PBIRS |Subscribe To Email Problem/Issue (kingfishertechtips.in)
  5. SQL Server 2022 Download, Install, Connect Using SSMS Tool (kingfishertechtips.in)
  6. Creating Tabular Project & Deploy Model To Analysis Service (kingfishertechtips.in)
  7. Deploy Tabular Project To Azure Analysis Service – SSDT Tool (kingfishertechtips.in)
  8. SSRS/PBIRS – Install And Configure The Report Server On-Prem (kingfishertechtips.in)
  9. Could Not Load Folder Contents In Portal URLs – SSRSPBIRS (kingfishertechtips.in)
  10. SSRS/PBIRS – Install And Configure The Report Server On-Prem (kingfishertechtips.in)
  11. SQL Server 2022 Download, Install, Connect Using SSMS Tool (kingfishertechtips.in)
  12. Capture Fiddler Trace for Debugging the Network Traffic (kingfishertechtips.in)
  13. On-Premises Gateway: Configure/Map SQL DB To Power Bi (kingfishertechtips.in)
  14. Fixed Column/Row Header Make Visible In Any Report (kingfishertechtips.in)
  15. Self-Signed Certificate: Configure SSRS/PBIRS With SSL (kingfishertechtips.in)
  16. Create Local User Account/Local Admin User In Windows 10/11 (kingfishertechtips.in)
  17. Login With AAD/Domain/Microsoft Account To Azure VM’s/RDP (kingfishertechtips.in)
Vickey Rajpoot
Vickey Rajpoot

Hello there! I'm dedicated Microsoft Data & AI Engineer at LTIMindtree, where I thrive technology into actionable insights. Dive into my world by visiting my YouTube channel & Webiste, "Kingfisher Tech Tips".

Articles: 112

Leave a Reply

Your email address will not be published. Required fields are marked *