Linked server anonymous login failure

Products

Information Centric Analytics Data Loss Prevention Core Package

Issue/Introduction

The following error may be logged during the nightly RiskFabric Processing job, while testing linked server connections, or when running dashboards that utilize linked servers:

Login Failed for user 'NT AUTHORITY\ANONYMOUS LOGON' (Microsoft SQL Server, Error: 18456)

Environment

Version : 6.x

Component : Microsoft SQL Server

Cause

Per Microsoft:

You get this error message when a connection attempt is rejected because of an authentication failure. User logins can fail for many reasons, such as invalid credentials, password expiration, and enabling the wrong authentication mode. In many cases, error codes include descriptions.

When error 18456 refers to NT AUTHORITY\ANONYMOUS LOGON:

There are double-hop (constraint delegation) scenarios across multiple computers. The error could occur if the Kerberos connection fails because of Service Principal Names (SPN) issues.

When a client-initiated call is made from IIS to a data source through a linked server connection in SQL Server, the Windows credentials of the ICA service account under which the RiskFabricAppPool runs are dropped by the second authentication hop between SQL Server and the linked data source.

Resolution

To resolve this error, select one of the following options:

Option: Connect to the linked server using a security context
1. Open SQL Server Management Studio (SSMS)
2. Connect to the Database Engine hosting the RiskFabric database
3. In Object Explorer, navigate to Server Objects > Linked Servers
4. Right-click the linked server in question and select Properties
  The Linked Server Properties - <linked-server-name> window will open
5. Select the Security page
6. Under the heading For a login not defined in the list above, connections will, select Be made using this security context
7. Enter the name and password of an account with permissions to connect to and query the remote database
8. Click the OK button to save your changes and close the Linked Server Properties window
Option: Configure Kerberos
1. To configure Kerberos for the linked server, follow the instructions provided in the following Microsoft document:
  https://learn.microsoft.com/en-us/archive/blogs/farukcelik/how-to-set-up-a-kerberos-authentication-scenario-with-sql-server-linked-servers
  NOTE: URL last validated June 9, 2023

Additional Information

MSSQLSERVER_18456: Login failed for user NT AUTHORITY\ANONYMOUS LOGON
https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error?view=sql-server-ver16#login-failed-for-user-nt-authorityanonymous-logon

NOTE: URL last validated June 9, 2023