Solved

Linked serever in SQL Server 2000

Posted on 2006-06-15
11
3,725 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
  • 3
  • 2
  • 2
  • +2
11 Comments
 
LVL 10

Expert Comment

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

Author Comment

by:simonrimmington
Comment Utility
local account
0
 
LVL 3

Expert Comment

by:haidersyed
Comment Utility
try to link them using sa or some other authentication mechanism
0
 

Author Comment

by:simonrimmington
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:simonrimmington
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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…

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now