Link to home
Start Free TrialLog in
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!
Avatar of adwiseman
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.
"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"?
Avatar of dverb

ASKER

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?
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.
Avatar of dverb

ASKER

Sorry for leaving that out.  Both servers are part of the same domain, and so is my local machine.
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.
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).
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)
"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.
ASKER CERTIFIED SOLUTION
Avatar of dhenson
dhenson

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dverb

ASKER

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

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.
ANWAR1965, this question was already closed and marked as answered....