Linked server connection error

book

Article ID: 171111

calendar_today

Updated On:

Products

Information Centric Analytics Data Loss Prevention Core Package

Issue/Introduction

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)

Cause

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. 

Resolution

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/

 

Attachments

Powered by Wolken Software