Linked server anonymous login error

SQLLover21 201 Reputation points
2021-02-09T20:21:38.22+00:00

I am trying to access a database in my UAT server through a windows login credential. When I try to access the database, I get the error Login failed for user

'NT AUTHORITY\ANONYMOUS LOGON

This windows account has sysadmin rights so I am not sure why I can't access it.

I believe that this database is part of a linked server because when I test the connection to the linked server, I get the same anonymous login error. I asked a fellow team member to try to test this connection, and he says his test succeeded with no error. When I change the security property in the linked server to "be made without using a security context" it gives me this error:

The OLE DB provider "SQLNxxx" for linked server "xxx" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "SQLNxxx" for linked server "xxxx".
OLE DB provider "SQLxxxx" for linked server "Mxxxx5" returned message "Invalid authorization specification". (.Net SqlClient Data Provider)

As you can tell from the error above ^ the linked server is a connecting from one SQL Server to another. I have tried to read from tables and views as well and still receive the same anon error.

I have researched multiple days online and could not find a good resolution. Some of the solutions they suggested are to change the security settings but that one did not work.

Please offer me some advice as what I can do to resolve this issue. Thank you in advance.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,107 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,582 questions
0 comments No comments
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-02-10T02:12:40.117+00:00

    Hi @SQLLover21 ,

    Welcome to Microsoft Q&A!

    "Login Failed for user 'NT Authority\ANONYMOUS' LOGON"

    Regarding to above error, the client is probably running under the local system account, and SQL Server has not registered SPN. Thus, NTLM was used.

    Since the client and SQL Server are located on different machines, the local system account of the client cannot be authenticated using NTLM, so the identity of the client is regarded as ANONYMOUS LOGON.

    The solution is to manually register the SPN under the SQL Server service account so that Kerberos could work normally.

    In order to manually register the SPN, you need to use the tool Setspn.exe.

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 103.9K Reputation points MVP
    2021-02-09T22:46:05.55+00:00

    The normal means for access over a linked server is self-mapping. That is, you log on to the remote server with your own credentials. As you have noticed, this does not always work.

    For this to work, SQL Server must be able to pass your login token to the remote server. This can work if you are logged into Windows on the machine where SQL Server is running, but at the same time fail if you are logged to SQL Server from another computer. This is a double-hop, and for this to work, Kerberos needs to be set up correctly.

    There are other possibilities as well, for instance, the machines are in a workgroup or there is no trust between domains.

    1 person found this answer helpful.
    0 comments No comments