Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3781
  • Last Modified:

Linked serever in SQL Server 2000

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
simonrimmington
Asked:
simonrimmington
  • 3
  • 2
  • 2
  • +2
2 Solutions
 
kiranghagCommented:
what logon credentials (domain account/local account) are used to start the SQL service on linked server?
0
 
simonrimmingtonAuthor Commented:
local account
0
 
haidersyedCommented:
try to link them using sa or some other authentication mechanism
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
simonrimmingtonAuthor Commented:
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
 
simonrimmingtonAuthor Commented:
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
 
haidersyedCommented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
Put permission to one domain user ( should be there on both the servers ) , and use the same user for  accessing the databases
0
 
kiranghagCommented:
>>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
 
TRACEYMARYCommented:
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

Technology Partners: 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!

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now