Linked Servers Connection Issue – Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’

When setting up Linked Server with in SQL Server you get following error message:

TITLE: Microsoft SQL Server Management Studio
——————————
“The linked server has been created but failed a connection test. Do you want to keep the linked server?”
——————————
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

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

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3310&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476
——————————

The SQL Server you are trying to setup linked server for does not allow delegation. So in order for you to get linked server working you’ll have to first get SPN setup by server administrator in AD.

Lets say we are trying to setup Linked Server on two SQL Servers, SSA and SSB. We have a domain account on SSA called Domain\U1 that needs access to information on SSB.

• Create a new SQL Account on SSB with VERY strong password, lets call it SqlServerAccount_U2.
• Grant the SQL Account on SSB access to resources that are needed.
• On SSA under linked server security, click “ADD” to add a local->remote mapping.
• Under local login select Domain\U1.
• Level Impersonate unchecked.
• Under remote User SqlServerAccount_U2.
• Under remote password enter the VERY strong password you created.
• Select ‘Not to be made’ under for logins not defined in the list above.

Ref: http://msdn.microsoft.com/en-us/library/ms189580(SQL.90).aspx

Thanks.

Inserting into Linked Servers

Recently I was trying to help someone on Microsoft Newsgroups with issue with Linked Server. When inserting into Linked Server directly via the SQL Statement from a Query analyzer there were no issues everything worked successfully.

But when they changed the insert to work from with in a trigger the application started failing with following error:

Server: Msg 7391, Level 16, State 1, Line 1 The operation could not be performed because the OLE DB provider ‘SQLOLEDB’ was unable to begin a distributed transaction.

Their SQL Server configuration:

• Two physical SQL Servers.
• In two different domains.
• With firewall between them; all relevant ports for DTC opened for communication.

I suggested checking the settings for her DTC security and configuration as in the following site:

http://bytes.com/topic/sql-server/answers/513448-trigger-between-linked-server-dtc-issue

But it did not help them out, they decided to use DTCPing.exe (Download Here) utility to further troubleshoot the issue and this time following error showed up:

————————————–
…
tablename= #dtc28215
Creating Temp Table for Testing: #dtc28215
Warning: No Columns in Result Set From Executing: ‘create table #dtc28215 (ivalint)’
Initializing DTC
Beginning DTC Transaction
Enlisting Connection in Transaction
Error:
SQLSTATE=25S12,Native error=0,msg=’[Microsoft][SQL Native Client]Die Transaktion wurde bereits implizit oder explizit ³bertragen oder abgebrochen.’
Error:
SQLSTATE=24000,Native error=0,msg=[Microsoft][SQL Native Client]Ung³ltiger Cursorstatus
Typical Errors in DTC Output When
a. Firewall Has Ports Closed
-OR-
b. Bad WINS/DNS entries
-OR-
c. Misconfigured network
-OR-
d. Misconfigured SQL Server machine that has multiple netcards.
Aborting DTC Transaction
Releasing DTC Interface Pointers
Successfully Released pTransaction Pointer.
————————————–
(a-c) There is a firewall and the servers are in two different domains, but name resolution (ping and nslookup) with full name . work correct on both sides. In Firewall all ports are opened in both sides.
(d) The SQL server has 2 network cards, but the second is disabled. So this might not be the problem.

I had seen similar DTCPing errors before so I told them to reference the Microsoft Tech reference link to see if this will help out:

http://blogs.msdn.com/distributedservices/archive/2008/11/12/troubleshooting-msdtc-issues-with-the-dtcping-tool.aspx

They ended up opening a call and following is the solution they came up with this problem:

• DTC Uses NetBIOS when communicating between servers.
• So adding each server to HOSTS file on each of the two SQL Servers at \driver\etc\host resolved the issue.

I have not had to do that any of the configurations myself; but their issue was communication between two servers on two different domains.

To read the original Microsoft Newsgroup Posting, please click here.