Linked Server: Login Failed for User NT AUTHORITY\ANONYMOUS LOGON, and Error 7391 Distributed Transactions

The linked server works perfectly when you remote onto the server and run it there, and fails the moment you run the same query from your own desktop. That one sentence is the whole diagnosis, and this page is about the two messages it produces.

Which of These Messages Are You Holding

Linked servers fail in several places and the messages look alike. Find your line, take its jump, ignore the rest of the page.

Run One Query Before You Change Anything

Two connections are involved, and you need to know how each one authenticated. The first is you to the first instance. The second is that instance to the linked server, made on your behalf, and you can read it through the linked server itself. Run this from your own desktop, not from a remote session on the server, because that is the case that fails.

-- Hop 1: how you reached this instance
SELECT net_transport, auth_scheme, local_net_address, client_net_address
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

-- Hop 2: how the instance reached the linked server, on your behalf
SELECT *
FROM OPENQUERY(LinkedServerName,
    'SELECT SYSTEM_USER AS arrived_as, auth_scheme, net_transport
     FROM sys.dm_exec_connections WHERE session_id = @@SPID');

Both halves, from a Windows login against a loopback linked server:

-- hop 1
net_transport   auth_scheme   local_net_address   client_net_address
-------------   -----------   -----------------   ------------------
TCP             NTLM          192.168.1.109       192.168.1.109

-- hop 2
arrived_as      auth_scheme   net_transport
-------------   -----------   -------------
HPAI01\Peter    NTLM          TCP
HPAI01\Peter    NTLM          Session

Three things to read off that. auth_scheme is the whole answer, and Microsoft’s SPN article gives you the one query and the table of what each SPN state produces: when the SPN is registered correctly, local connections use NTLM and remote connections use Kerberos. So NTLM on a connection made from the server console is normal and proves nothing. NTLM on the connection from your desktop is the fault, and the SPN is where you start.

The second row of hop 2 is not a second connection to worry about. MS Docs is explicit that a net_transport of Session is the extra row a multiple active result sets session gets, and the provider uses one.

And arrived_as is the column that settles the argument with whoever owns the remote server. It is the name the remote instance is making its permission decisions against. If it says your Windows account, delegation worked. If it says a shared SQL login, you have an explicit mapping. If the connection never gets that far, you are in the next section.

Login Failed for User NT AUTHORITY\ANONYMOUS LOGON

Nobody created a login called NT AUTHORITY\ANONYMOUS LOGON, and you cannot fix this by granting it rights. That name is what Windows calls a connection that arrived carrying no credentials, and the remote instance is reporting, accurately, that it does not know who is asking. That Msg, Severity and State line is the one a linked server raises when the login it mapped is refused, and it is captured further down this page with a named login in it. The anonymous form of the user name needs two machines in a domain, so it is documented here, not reproduced.

The cause is the double hop. Count the connections. Hop one is your workstation to the first instance. Hop two is that instance to the linked server, opened on your behalf, which means your identity has to travel one machine further than you did. For that to happen, hop one has to have used Kerberos, the account running the first instance’s service has to be trusted to delegate to the second, and your own account must not be flagged as sensitive and not delegatable in Active Directory. Miss any one of those and there is nothing to forward, so the second hop opens anonymously and fails. Microsoft’s own wording on the login-mapping procedure is that delegation is not needed for single-hop scenarios but is required for multiple-hop ones.

That is also why it works when you remote onto the server. There, you typed your password into that machine, so it holds a full credential for you and can authenticate outward on its own. From your desktop it holds an impersonation token, and an NTLM impersonation token cannot be passed on. Same query, same permissions, one fewer hop.

There are only two real fixes and they pull in opposite directions. Either make the hop delegate, which means a correct SPN on both instances so hop one comes back KERBEROS, and delegation granted on the first instance’s service account, ideally constrained to the second instance’s SQL Server service rather than left unconstrained. Or stop delegating, and map the login explicitly so hop two authenticates as a fixed account instead of as you. The second is faster and is what most shops end up doing; the price is that the remote server’s auditing now sees one name instead of every user’s, and that name’s permissions apply to everybody. If you want the background on why one scheme is offered and the other taken, Kerberos vs NTLM in SQL Server is the companion page, and when the handshake fails outright rather than falling back you are on SSPI handshake failed.

The Four Login Mappings on the Security Page

Every linked server has a login mapping, whether anyone chose one or not. sp_addlinkedserver creates a default that says use the caller’s own credentials, so a linked server built with the defaults is a delegating linked server, and that is the one that breaks from a desktop. The four radio buttons at the bottom of the Security page in SQL Server Management Studio are the same four states, and each does something different to hop two.

In T-SQL all four are sp_addlinkedsrvlogin, and the whole thing turns on @useself, which defaults to true. The fourth option is the one that cures an anonymous logon failure without touching Active Directory:

-- Option 4, for one named local login. Hop 2 stops being a delegated hop.
EXEC sp_addlinkedsrvlogin
     @rmtsrvname  = N'LinkedServerName',
     @useself     = N'false',
     @locallogin  = N'DOMAIN\reporting_user',
     @rmtuser     = N'svc_linked_reader',
     @rmtpassword = N'...';   -- the remote login's real password

-- What is mapped today, and whether it delegates
SELECT s.name AS linked_server, ll.remote_name,
       ll.uses_self_credential, SUSER_NAME(ll.local_principal_id) AS local_login
FROM sys.linked_logins AS ll
JOIN sys.servers AS s ON s.server_id = ll.server_id
WHERE s.is_linked = 1;

An explicit mapping for a named login beats the catch-all, so you can leave the default delegating mapping in place for everyone who connects on the server itself and add one line for the account that connects from elsewhere. uses_self_credential of 1 in that second query is the delegating state; a remote_name with nothing in uses_self_credential is option 4. To take the whole estate’s inventory at once rather than one server at a time, Get Linked Servers reads the catalog views across every instance, and Generate Linked Server Script writes the definition back out as T-SQL so a rebuilt server comes back the same.

What Each Mapping Fails With

The reason the mapping matters more than it looks is that three of the four states produce a different error, and the error tells you which state you are in without opening a dialog. All four, in order, against the same linked server and the same query:

-- 1. Not be made
Msg 7416, Level 16, State 1, Server HPAI01, Line 1
Access to the remote server is denied because no login-mapping exists.

-- 2. Be made without using a security context
Msg 7399, Level 16, State 1, Server HPAI01, Line 1
The OLE DB provider "MSOLEDBSQL19" for linked server "LAB1001_LOOP" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Server HPAI01, Line 1
Cannot initialize the data source object of OLE DB provider "MSOLEDBSQL19" for linked server "LAB1001_LOOP".
OLE DB provider "MSOLEDBSQL19" for linked server "LAB1001_LOOP" returned message "Invalid authorization specification".

-- 3. Be made using this security context, wrong password
Msg 18456, Level 14, State 1, Server HPAI01, Line 1
Login failed for user 'lab1001_hopuser'.

-- 4. Be made using this security context, correct password
note
-------------------
row written locally

So 7416 means stop looking at the network and go and look at the mapping list, because your login is not in it and there is no catch-all either. 7399 with Invalid authorization specification means somebody picked the second radio button, which no SQL Server target will ever accept. And an 18456 that names a real login means the mapping is doing its job and the credentials in it are stale, which is an ordinary login failure with a state code, covered on Login Failed or Permission Denied.

What you will not see in that list is a message about the network, because there was not one. A linked server that cannot reach the other machine at all fails with Login timeout expired and a provider error 53 before authentication is ever attempted, and that is a connectivity problem wearing a linked server’s clothes. If you are building the linked server rather than fixing one, Create a Linked Server to Another SQL Server walks the whole setup including which security option to pick.

Error 7391: Unable to Begin a Distributed Transaction

The two quoted names in that line are your provider and your linked server, so the message looks personalised and tells you nothing about the cause. This one is not security. Authentication has already succeeded, which you can prove by running the same statement without the surrounding transaction and watching it work. What failed is the Microsoft Distributed Transaction Coordinator.

It happens because of a promotion you did not ask for. MS Docs spells out the rule: when a distributed query runs inside a local transaction, the transaction is automatically promoted to a distributed one if the remote data source supports it, and another SQL Server always does. So an INSERT ... SELECT across a linked server, wrapped in a BEGIN TRANSACTION that somebody added for safety, quietly becomes a two-phase commit between two machines, and now MS DTC has to be working on both of them and able to talk to the other. Take the BEGIN TRANSACTION away and the same statement runs, which is the single fastest way to confirm you are here.

What to check, in the order that finds it:

1. Is the service running on both machines. sc query MSDTC at an elevated prompt on each. It is set to start on demand on a lot of builds, so a server that has never run a distributed transaction will report it stopped and that is not itself the fault.
2. Is Network DTC Access on, with both directions allowed. In Component Services, under the local DTC’s security properties: Network DTC Access, Allow Inbound and Allow Outbound. Missing Allow Inbound on the far server is the classic one, because the outbound side works and the error still appears.
3. Do the two ends agree on authentication. Mutual authentication requires both machines to be in the same or trusting domains. A pair that is not gets No Authentication Required, set identically on both sides, or it fails.
4. Is the firewall open both ways. DTC needs the RPC endpoint mapper on TCP 135 plus its dynamic port range, and the two coordinators call each other, so a rule in one direction is half a rule. There is a built-in Distributed Transaction Coordinator firewall group for exactly this.
5. Can each machine resolve the other’s name. DTC identifies partners by host name, not by the address your linked server happens to use, so a linked server pointed at an IP can connect while DTC still cannot.

If both ends are in your control and the transaction genuinely needs to be atomic across them, that list is the work. The option in the next section is the other answer, and it is the one people reach for first.

The Remote Proc Transaction Promotion Fix, and When It Is Wrong

One linked server option turns the automatic promotion off:

EXEC sp_serveroption
     @server   = N'LinkedServerName',
     @optname  = 'remote proc transaction promotion',
     @optvalue = 'false';

-- and what it is set to across the estate
SELECT name, is_remote_proc_transaction_promotion_enabled, is_rpc_out_enabled
FROM sys.servers
WHERE is_linked = 1;

Read what it actually covers before you set it. MS Docs is narrow and precise: the option governs calling a remote stored procedure, it defaults to true, and setting it to false stops a local transaction being promoted when a remote procedure call is made. It has no effect at all if the transaction was already distributed, and none if there is no transaction open.

Which means it does not cover the statement most people are holding when they meet 7391. A four-part-name INSERT, UPDATE or DELETE inside an explicit transaction is a distributed query, not a remote procedure call, and it still needs MS DTC with the option off. On a single-instance test the write fails identically either way, with a different number because the linked server points back at the same instance:

-- promotion = true, and again with promotion = false. Same result both times.
SET XACT_ABORT ON;
BEGIN TRANSACTION;
INSERT INTO LAB1001_LOOP.Lab1001_hop.dbo.HopTarget (note) SELECT N'four part write';
COMMIT TRANSACTION;

Msg 3910, Level 16, State 2, Server HPAI01, Line 1
Transaction context in use by another session.

That is a useful trap to know about on its own: a linked server whose data source is the instance it lives on cannot be written to inside an explicit transaction, and 3910 rather than 7391 is how you tell a loopback linked server from a genuine DTC problem. It also means you cannot reproduce 7391 on one box, so if you are building a test for this you need two instances.

When the option is the wrong fix. Turning promotion off does not make the distributed transaction work, it removes it. The remote call now commits or rolls back on its own, and if the local half fails afterwards, the remote change stays. If the two writes were wrapped in a transaction because they have to be both or neither, you have traded an error message for silent divergence between two servers, and nobody will find it until a reconciliation does. Set it to false when the remote call is a read, or when the remote work is genuinely independent and somebody added the transaction out of habit. Fix MS DTC when it is not. The session-scoped equivalent is SET REMOTE_PROC_TRANSACTIONS OFF, which is the safer thing to test with because it dies with the connection, and the instance-wide sp_configure 'remote proc trans' setting is the one to leave alone.

The Linked Server Query That Will Not KILL

The third linked-server incident has no error number. A query across the linked server is running, the far end is slow or gone, and KILL does not end it. This is what that looks like: a remote call started, then killed, then watched for 30 seconds.

session  status            wait_type  wait_time  command
51       running           OLEDB        6992 ms  SELECT
                                                 -- KILL 51 issued here
51       running           OLEDB       15203 ms  KILLED/ROLLBACK
51       running           OLEDB       22675 ms  KILLED/ROLLBACK
51       running           OLEDB       30132 ms  KILLED/ROLLBACK

KILL 51 WITH STATUSONLY
SPID 51: transaction rollback in progress. Estimated rollback completion: 0%.
Estimated time remaining: 0 seconds.

Nothing in that is broken. The command changed to KILLED/ROLLBACK, so the KILL was accepted, and the wait type never moved off OLEDB because the session is inside a call to the provider and the engine cannot take it back until that call returns. The rollback estimate reads 0% complete and 0 seconds remaining for the whole wait, which is not a stuck rollback, it is a rollback that has not been able to start. When the remote call finally returned, the session ended on its own and the client got Cannot continue the execution because the session is in the kill state.

So the practical answer is that you do not kill it faster from this side; you make the remote call end. A query timeout on the linked server, set with sp_serveroption, puts a ceiling on it in future. Right now, the thing that ends it is whatever is holding the far end, which may be a block on the remote instance rather than anything local. The wait type names the layer you are stuck in: OLEDB is the provider call itself and is normal on any linked-server query, MSQL_XP is an extended stored procedure rather than a distributed query, and the PREEMPTIVE_DTC waits mean the engine has left the scheduler to talk to MS DTC, which points the investigation at the coordinator and not at the query. The mechanics of KILL itself, what the rollback estimate means and when a restart is the only remaining option, are on How to Kill a SPID in SQL Server.

Common Questions