Solved

Linked serever in SQL Server 2000

Posted on 2006-06-15
11
3,764 Views
Last Modified: 2012-05-05
I have used 2 server and linked the both servers with linked server in sql server 2000.
but when i tried to access the procedures which are using the tables in the linked server they are giving the error as follows
Msg 18456, Level 14, State 1, Procedure VW_Project_Ref, Line 4 Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

For example.
Server A have the database that we are using with domain user account (trusted connection).
Server B is linked server with the sa login so, it is giving the error as above.

So, i have changed the Server B, linked server, with windows authentication (trusted connection), but when i tried to access this linked server it is giving the error 'Error:18456 Login failed for User ..........'

Please help me how to resolve this problem with windows authentication(trusted connection)

If you are poeple are able to send the solution ASAP, if will be great ful..

Thanks
Ravikanth
0
Comment
Question by:simonrimmington
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
11 Comments
 
LVL 10

Expert Comment

by:kiranghag
ID: 16917667
what logon credentials (domain account/local account) are used to start the SQL service on linked server?
0
 

Author Comment

by:simonrimmington
ID: 16917709
local account
0
 
LVL 3

Expert Comment

by:haidersyed
ID: 16917742
try to link them using sa or some other authentication mechanism
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:simonrimmington
ID: 16917757
When i am using sa login the connection is succeeded but it is showing the error as below

Msg 18456, Level 14, State 1, Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
0
 

Author Comment

by:simonrimmington
ID: 16917762
If both server are using sa login, then it is working fine..

but our application which we have developed is to support the windows active directory users.
0
 
LVL 3

Accepted Solution

by:
haidersyed earned 125 total points
ID: 16917813
try link http://support.microsoft.com/?id=238477 or
SYMPTOMS
When you run a distributed query against a Microsoft SQL Server linked server on a Microsoft Windows NT 4.0-based computer, you receive the following error message:
Server: Msg 18456, Level 14, State 1, Line 1 Login failed for user '\'
When you run a distributed query against a Microsoft SQL Server linked server on a Microsoft Windows 2000-based computer, you receive the following error message:
Server: Msg 18456, Level 14, State 1, Line 1 Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'
This problem occurs when SQL Server uses the TCP/IP Server network library or the Multiprotocol Server network library to listen for client requests.
 Back to the top

CAUSE
This message indicates that you are attempting to access the linked server by using Windows NT authentication to impersonate the client connecting.

NTLM authentication does not support double hop because security tokens and hashes are only valid for the computer where they are generated.

For example, suppose the following configuration exists: • You have SQL Server installed on servers A and B.
• You have a client computer C.
• Server B has been set up up as a linked server on server A by means of either of the following: • The useself option of the sp_addlinkedsrvlogin stored procedure.

-or-
• The They will be impersonated option on the Security tab of the Linked Server Properties dialog box in Enterprise Manager.
 
Given this configuration, the following applies:

Clients on server A can connect to server A by means of Windows NT authentication, and will be able to successfully run queries against linked server B under their own Windows NT security account (assuming they have been granted appropriate access). The Windows NT credentials only have to make a "single hop" from server A to server B.

Clients on client C can successfully connect to server A by means of Windows NT authentication. However, if they try to run queries against linked server B, they fail with Msg 18456. This is considered a "double hop" because the Windows NT credentials from client C are sent to server A initially, and the second hop from server A to server B for the remote query is not supported on SQL Server 7.0 on a Microsoft Windows NT 4.0-based computer.
 Back to the top

WORKAROUND
To work around this problem, use one of the following methods:• Map the clients on server A to a standard security login on server B, by using either the sp_addlinkedsrvlogin stored procedure or the Security tab of the Linked Server Properties dialog box in Enterprise Manager.
• If you are running the distributed query on an instance of SQL Server that is running on a Microsoft Windows 2000-based computer, configure SQL Server to listen for client requests by using the Named Pipes Server network library, instead of using the TCP/IP Server network library or the Multiprotocol Server network library. To configure the Server network libraries for SQL Server, use the Server Network Utility.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16917814
Put permission to one domain user ( should be there on both the servers ) , and use the same user for  accessing the databases
0
 
LVL 10

Expert Comment

by:kiranghag
ID: 16917827
>>Put permission to one domain user ( should be there on both the servers ) , and use the same user for  accessing the databases

do you mean to start the SQL service?

afik, if you use windows authentication, then the credentials used to start SQL service are passed on to next server. in this case you are using local account and that might not be able to authenticate on other server and its failing.
0
 
LVL 7

Assisted Solution

by:TRACEYMARY
TRACEYMARY earned 125 total points
ID: 16924203
I had the exact same error this morning.
For all the databases and stored procedures in SQL1 and SQL2 make sure you have the permissions set for select, insert etc.....i found i missed a permission.

Is this set on your server.'NT AUTHORITY\ANONYMOUS LOGON'.

Also..........go to sql query and login as your user and try it.....
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question