Solved

Query between multiple linked servers

Posted on 2004-03-22
13
1,470 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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 500 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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Solar Winds can't see SQL Server Express 17 32
SSMS Opening Mode 9 18
Run Stored Procedure uisng ADO 5 20
SQL Recursion schedule 13 14
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

839 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