0

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.

UPDATE

https://www.sqlservercentral.com/blogs/how-to-resolve-user-error-in-kerberos-configuration-manager

1 Answer 1

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.

2
  • Thank you. I used Kerberos Connection Manager to set up SPN on server B (client) and I was succeeded to creat linked server. BUT I have another problem on dev server (Where just SQL Manager is installed). "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'" but on the dev server there is not SQL server installed.
    – sada
    Jun 30, 2022 at 14:00
  • 1
    @sada It doesn't matter that SQL Server isn't installed on the third server (server C - the DEV server), it's still a double hop when you establish a client connection from any application (in this case SQL Server Management Studio) from server C to server B and then use a Linked Server to connect to server A from server B. You need to make sure Kerberos delegation is setup properly to establish a trust between server B to server A. You can find info on how to do that in the links in my answer. Or you can change the settings of your Linked Server to use a SQL Login instead.
    – J.D.
    Jun 30, 2022 at 21:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.