servers.

Hello,

I have a problem when I try to query one SQL Server 2000 server from another SQL 2005 by linked server throught another computer with SQL Server 2005 installed. the three computers are in the same domain

After creating the link, I can access to the system databases but not to the user databases. When I try I get this message:

Msg 7314, Level 16, State 1, Line 1

The OLE DB provider "SQLNCLI" for linked server "EXP-THELMA" does not contain the table ""Thelma_Newsletter"."dbo"."UE_F"". The table either does not exist or the current user does not have permissions on that table.

I'm sure that the user has the right permissions to access, so I can't understand what's happening.

Any help we'll be welcome.

Thanks in advance,

Radamante.

the error is self explantory. This issue is the user does not have permission. The servers are able to communicate. check the remote user which u used to connect to Remote Server in sp_addlinkedsrvlogin has the permission to access the database

Madhu

|||

As a test try to execute exec sp_tables_ex localserver1 to test connection. If it works, you will get the list of tables available on localserver1.

Also try

select * from localserver1..dbo.dummyobjects to see what you get.

|||

Hi everybody,

Thank you very much for your help, I'm still having problems, but different one.
I'm trying to link different servers in different domains: Prod and Dev (some of them are SQL 2000 and some SQL 2005) using double-hop.
Between servers placed in Dev domain I don't have any problem, but when I try to link servers from Dev to Prod or viceversa or between servers in Prod, I receive the next message for SQL Server 2000:

OLE DB provider "SQLNCLI" for linked server "192.168.1.11" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

and this one for SQL Server 2005:

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

When I execute the query: "select net_transport, auth_scheme from sys.dm_exec_connections where session_id=@.@.spid"

the response is: TCP - KERBEROS

This seems indicate a problem in Prod domain configuration, but I'm a little lost. I don't have a clue what's happening, could you help me, please?
Thanks a lot,

Radamante

|||

I guess there could be a trust problem between the domains if you are using a different domains between the SQL servers.

Might try to create local account with identical password on both machine. Say, create "testuser" on both machineA and machineB with password "testuserpass". Grant testuser permission to access SS2k5 on machine A.

Also check whether the SID is similar on both the servers.