This may occur during the nightly job execution, while testing linked server connection or when running dashboards that utilize linked servers.
Login Failed for user 'NT AUTHORITY\ANONYMOUS LOGON' (Microsoft SQL Server, Error: 18456)
This happens when attempting to connect to the source database via the link using a server other than the database server hosting the linked server or the database server where the source database is located.
The reason for the error is because the Windows credentials coming from the originating server (a server other than the server hosting the linked server or the database) are dropped by the 2nd hop of authentication from the server hosting the linked server to the database server where the source database is located.
Option 1: Use SQL authentication
To switch to SQL authentication, create a SQL account on the database server hosting the source database that the linked server connects to. Open the linked server properties and use the created SQL account for the linked server as illustrated in the screen shot below:
Option 2: Configure Kerberos To configure Kerberos for SQL linked server, follow the instructions detailed in the link below: https://blogs.msdn.microsoft.com/farukcelik/2008/01/02/how-to-set-up-a-kerberos-authentication-scenario-with-sql-server-linked-servers/
Subscribing will provide email updates when this Article is updated. Login is required.