Solved

Query between multiple linked servers

Posted on 2004-03-22
13
1,443 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
 
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

IT, Stop Being Called Into Every Meeting

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

Title # Comments Views Activity
Complex SQL 10 34
Slow SQL query 12 29
sql query Help 12 34
DATETIMEOFFSET feature 1 5
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

743 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