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

x
?
Solved

Query between multiple linked servers

Posted on 2004-03-22
13
Medium Priority
?
1,497 Views
Last Modified: 2009-04-28
I need to query tables from two servers.  The facts are as follows:

They are both NOT LOCAL servers
They are both SQL Server 2000 on Windows 2000 and using Windows NT Authentication Only
My NT Login has System Administrator rights on both SQL Servers

Let's use the server names Test and Final.

I currently have not been able to properly add Test as a linked server on Final.
I have also not been able to properly add Final as a linked server on Test.

I need to connect to one server and query results from the other ...

Connection (via Query Analyzer or Execute SQL Task in a DTS Package): Test
Login:  {NT Authentication}

SQL Statement (assume the database name is 'DB1' and the table name is 'table1'):
select * from Final.DB1.dbo.table1

Error Message:
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Security does not appear to be passed properly from one server to the other.

Every SQL "expert" I've asked has yet to resolve this problem.

Can this problem be solved or is it a Microsoft 'goof up'?

Please advise.  

Thank you!
0
Comment
Question by:dverb
  • 4
  • 3
  • 2
  • +3
13 Comments
 
LVL 14

Expert Comment

by:adwiseman
ID: 10652833
Check the security configuration of the linked server.  the sever you are connecion is not accepting the security being passed.

In enterprise manager, this would be under the security tab of the properties window of the linked server.
0
 
LVL 34

Expert Comment

by:arbert
ID: 10652845
"Can this problem be solved or is it a Microsoft 'goof up'?"

Not a goof up.  

What security option did you select in the linked server properties?  "Not be Made", "Be Made without using a security context", "Be made using the login's current security context", or "Be Made using this security context"?
0
 

Author Comment

by:dverb
ID: 10653138
I have tried all four security options ...

using "Not be Made" returns the error:
Error 7416: Access to the remote server is denied because no login-mapping exists

using "Be Made without using a security context" returns the error:
Error 7399: OLE DB provider 'SQLOLEDB' reported an error.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize returned 0x80004005:  ].

using "Be made using the login's current security context" returns the error:
Error 18456: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

using "Be Made using this security context" returns the error:
Error 18452: Login failed for user {my login}.  Reason: Not associated with a trusted SQL Server connection.
{my login} is my full login (including the domain name)

Any suggestions?
0
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!

 
LVL 34

Expert Comment

by:arbert
ID: 10653159
If these remote servers are NOT part of the same domain, you'll have to map a login--you won't be able to use a trusted connection/Windows Authentication.
0
 

Author Comment

by:dverb
ID: 10653182
Sorry for leaving that out.  Both servers are part of the same domain, and so is my local machine.
0
 
LVL 13

Expert Comment

by:danblake
ID: 10653605
I presume that the user:
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Is a real user ?  or are you logged in as an anonymous user ?
You will need higher sql-server account level access to generate a linked server.
0
 
LVL 13

Expert Comment

by:danblake
ID: 10653638
Can you create the linked server when logged in on one of your two sql-servers using the sysadmin accounts ?

If this does not work, check that you can access each sql-servers in enterprise manager on each server to each other.  It may be that they are not configured to comunicate to each-other, for some very odd reason (such as a firewall in the way or something blocking a TCP IP port to a specific destination).
0
 
LVL 2

Expert Comment

by:dhenson
ID: 10654199
Danblake makes a very good point...  is there a Firewall between these two servers?
It would be advisable to step back from the sql side of things for a second and verify that you have connectivity on whatever port SQL uses to create the linked server connection (between the two servers).

More specifically verify the following ports are open between the two servers:
53,135,137,139, 1433

(info from http://www.microsoft.com/windows2000/techinfo/reskit/samplechapters/cnfc/cnfc_por_simw.asp)
0
 
LVL 34

Expert Comment

by:arbert
ID: 10654258
"Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'."

This is an error that occurs when SQL Server can't figure out the user to pass to the linked server--so, to get this error, you know there isn't a firewall in place blocking the ports.

When you're sure that your user is on the domain and setup as a login to both sql servers?  The idea method would be to use the same service account on both servers.
0
 
LVL 2

Accepted Solution

by:
dhenson earned 2000 total points
ID: 10655014
http://www.winnetmag.com/SQLServer/Article/ArticleID/23670/23670.html

The above article discusses this "double-hop" problem and describes a workaround using Account Delegation....

Hope this helps.

dhenson
0
 

Author Comment

by:dverb
ID: 10661770
PERFECT!!!!!  Trust both SQL Servers and trust the account that starts the services on both SQL Servers.  You just made my job a lot easier!  Thanks!!!!!
0
 

Expert Comment

by:ANWAR1965
ID: 10858125

Please login to query analyzer as system Admin. "sa"  

in security - linked server properties should  "Be made using this securiy context " with 'sa' and password. your target server should also be registerd at your source server group.  

I have solved this problem and all my store procedures are working fine which Idesinged after the replication failur.
0
 
LVL 34

Expert Comment

by:arbert
ID: 10859955
ANWAR1965, this question was already closed and marked as answered....
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

879 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