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 select “As the user viewing the report“ you will get this kind of error when you click on test connection.

2. 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.

3. 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 Flow:

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.

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.

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.

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.

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

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).

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).

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

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)