linked server null

If I remote on to sql05 and open management studio
and select * from sql2000.dbo.tablename
Where sql2000 is a link from sql server05 to sql server 2000
it works
Same thing happens if I just try Test Connection under Linked Servers on my
local machine.
If on my desktop in ManagementStudio I try the same query I get:
TITLE: Microsoft SQL Server Management Studio
--
"The test connection to the linked server failed."
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
TCP Provider: An existing connection was forcibly closed by the remote host.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
OLE DB provider "SQLNCLI" for linked server "HRSQL" returned message
"Communication link failure". (Microsoft SQL Server, Error: 10054)
For help, click:
http://go.microsoft.com/fwlink?Prod...54&LinkId=20476
BUTTONS:
OK
--Hi Chuck,
I understand that the linked server failed to work on your local machine
with the error:
"TCP Provider: An existing connection was forcibly closed by the remote
host.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
OLE DB provider "SQLNCLI" for linked server "HRSQL" returned message
"Communication link failure". (Microsoft SQL Server, Error: 10054)"
If I have misunderstood, please let me know.
The error indicates that the linked server login is not associated with a
trusted SQL connection and that the login account did not have the valid
user's password.
I would like to check with you the following questions:
1. How did you add the linked server?
2. Did you use SQL login or Windows login for the linked server?
3. Is your local server in the same domain as the remote server?
4. What is your remote SQL Server login mode, Windows Authentication or
Mixed?
If your servers are in the same domain and your local domain logon account
is allowed on the remote server, I recommend that you run "osql /Stcp:HRSQL
/E" from command prompt to check if you can connect to the remote SQL
Server instance HRSQL via Windows Authentication.
If you can connect to the remote SQL Server instance, remove the linked
server from SSMS:
Server Objects->Linked Servers
Then run the following statements to add the linked server:
sp_addlinkedserver 'HRSQL'
Go
sp_addlinkedsrvlogin 'HRSQL','true'
Go
Then run the query again.
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============