Avatar of dverb
dverb asked on

Query between multiple linked servers

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!
Microsoft SQL Server

Avatar of undefined
Last Comment
arbert

8/22/2022 - Mon
adwiseman

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.
arbert

"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"?
ASKER
dverb

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?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
arbert

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.
ASKER
dverb

Sorry for leaving that out.  Both servers are part of the same domain, and so is my local machine.
danblake

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
danblake

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).
dhenson

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)
arbert

"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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
dhenson

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
dverb

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!!!!!
ANWAR1965


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.
arbert

ANWAR1965, this question was already closed and marked as answered....
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.