sqlcmd: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

Question

I have the privilege to connect to db on test2, but if I remote desktop on another host using the same account, and run the following command there

SQLCMD -E -S test2 -d test -i Silo.sql -b -v

(1 rows affected) Msg 18456, Level 14, State 1, Server test\colo, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Why this happen, and what's the workaround?

And strangely, it complains the user is 'NT AUTHORITY\ANONYMOUS LOGON', but in reality, I used my own account

Solution

You will get this error if you have asked for

1. Connect to remote server
2. The local server is in a domain
3. The remote server is not in a domain

The connection will not be made using your domain login (which is unknown on the target, but will instead use ANONYMOUS)

• Looking at your error

(1 rows affected) Msg 18456, Level 14, State 1, Server test\colo, Line 1 Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

It looks instead like part of your script is accessing linked server object from test through to test\colo, and you have not set up linked server logins correctly. If test and test\colo are not on the same domain, then you will need to set up linked server login mapping using sp_addlinkedsrvlogin

Comments

[Daniel Wu]

I am using vpn, so local server is not in domain, but the remote desktop connection is in a domain, and the target db server is also in a domain, so do you think it is the root cause, and what's the workaround

[RichardTheKiwi]

You have connected to test2, but it is accessing test\colo. What is the relationship between the two?