You should script out the Linked Server object and add that script to your post, so we have more to go off of.
When I see the error message:
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)
And I know at least 3 servers are at play (as you mentioned "All three servers are in the same domain"), then immediately my first thought is checking for proper SPN setup and Kerberos delegation. In the aforementioned SPN Books Online, under the Linked Servers and Delegation section, it even mentions "Delegation with linked servers requires Kerberos authentication.".
I've experienced this error many times in the past because either my SPNs were not setup properly or because Kerberos delegation was not configured between the two servers I'm trying to use a Linked Server between. Delegation setups a trust between those servers such that you're allowed to access server A
from a Linked Server on server B
when you're making the connection to server B
from a third server C
. This is known as a double hop and a trust must be established with delegation to allow a Kerberos connection to pass-through your Windows Authentication all the way from server C
to server A
, as a security measure.
The above assumes in your Linked Server object, under the Security section, you chose "Be made using the Login's current security context" which tries to pass-through the current authenticated user's security context.
Alternatively, if you changed that to "Be made using this security context" and entered the credentials for a SQL Login (as opposed to a Windows Authentication Login) that has access to server A
, then that would solve your issue too. Because now, no longer would it try to pass-through the currently authenticated user's security context, rather it would use the one of the specified SQL Login. This makes it no longer a double hop, from a security perspective.
The downside to that solution is that you have to ensure the SQL Login you choose has the appropriate access on server A
that supports all your use cases of the Linked Server. Anyone who uses that Linked Server will now be accessing server A
as the SQL Login that you entered the credentials for in your Linked Server object. You have to be careful drawing the line between over- and under-provisioning that SQL Login's permissions on server A
.
If you want to go with the first option of proper Kerberos delegation configuration and need any additional help, you may find more knowledgeable information by posting on ServerFault, as that's more of an infrastructure / security topic rather than a database one.