Login failed at creating Linked Server

All we need is an easy explanation of the problem, so here it is.

I want to create a linked server on database server B to database server A in a development server where just the SQL Management Studio is installed.
All three servers are in the same domain.
The error message

Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. (Microsoft SQL Server, Error: 18456)

when I want to create the linked server.

I do not understand why because I was succeeded to create the linked server in another server where it is truth an SQL server is installed but I do not think it had anything to do with it.

I tried look after this problem but I couldn’t solve it.



How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply