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:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .3042&EvtSrc=MSSQLServer&EvtID=10054&LinkId=20476[ /url]
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/subscriptions/managednewsgroups/default.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/subscriptions/support/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.
================================================== ====

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

Linked Server Access

Hi,
I am creating a linked server with
sp_addlinkedsrvlogin @.ServerAlias, 'TRUE', NULL, NULL, NULL
I am Creating a job with owner as my id.
When the job runs, it creates some internal clones of the job, the owner of
which is 'sa'.
execute @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID output ,
@.job_name = @.szTargetJobName,
@.owner_login_name = N'sa', @.description = N'Autogenerated.',
@.category_name = N'RunTime Job',
@.enabled = 1
When these clones try to access the linked server, the job fails because of
the access permission issues for the 'sa' account on the linked server.
I am not able to figure out the reason for this.
What might be the reason and what will be the solution for this.
Thanks,
Swap
Hi
A default mapping between all logins on the local server and remote logins
on the linked server is automatically created by executing
sp_addlinkedserver, therefore you should not need to issue your
sp_addlinkedsrvlogin statement.
You may want to try droping the linked server and re-linking it or
specifically try mapping the sa accounts and see if it works.
Can you access the linked server through QA, or do you get an error message?
John
"swap@.@.@." wrote:

> Hi,
> I am creating a linked server with
> sp_addlinkedsrvlogin @.ServerAlias, 'TRUE', NULL, NULL, NULL
> I am Creating a job with owner as my id.
> When the job runs, it creates some internal clones of the job, the owner of
> which is 'sa'.
> execute @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID output ,
> @.job_name = @.szTargetJobName,
> @.owner_login_name = N'sa', @.description = N'Autogenerated.',
> @.category_name = N'RunTime Job',
> @.enabled = 1
> When these clones try to access the linked server, the job fails because of
> the access permission issues for the 'sa' account on the linked server.
> I am not able to figure out the reason for this.
> What might be the reason and what will be the solution for this.
> Thanks,
> Swap

Linked Server Access

Hi,
I am creating a linked server with
sp_addlinkedsrvlogin @.ServerAlias, 'TRUE', NULL, NULL, NULL
I am Creating a job with owner as my id.
When the job runs, it creates some internal clones of the job, the owner of
which is 'sa'.
execute @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID output ,
@.job_name = @.szTargetJobName,
@.owner_login_name = N'sa', @.description = N'Autogenerated.',
@.category_name = N'RunTime Job',
@.enabled = 1
When these clones try to access the linked server, the job fails because of
the access permission issues for the 'sa' account on the linked server.
I am not able to figure out the reason for this.
What might be the reason and what will be the solution for this.
Thanks,
SwapHi
A default mapping between all logins on the local server and remote logins
on the linked server is automatically created by executing
sp_addlinkedserver, therefore you should not need to issue your
sp_addlinkedsrvlogin statement.
You may want to try droping the linked server and re-linking it or
specifically try mapping the sa accounts and see if it works.
Can you access the linked server through QA, or do you get an error message?
John
"swap@.@.@." wrote:
> Hi,
> I am creating a linked server with
> sp_addlinkedsrvlogin @.ServerAlias, 'TRUE', NULL, NULL, NULL
> I am Creating a job with owner as my id.
> When the job runs, it creates some internal clones of the job, the owner of
> which is 'sa'.
> execute @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID output ,
> @.job_name = @.szTargetJobName,
> @.owner_login_name = N'sa', @.description = N'Autogenerated.',
> @.category_name = N'RunTime Job',
> @.enabled = 1
> When these clones try to access the linked server, the job fails because of
> the access permission issues for the 'sa' account on the linked server.
> I am not able to figure out the reason for this.
> What might be the reason and what will be the solution for this.
> Thanks,
> Swap

Linked Server Access

Hi,
I am creating a linked server with
sp_addlinkedsrvlogin @.ServerAlias, 'TRUE', NULL, NULL, NULL
I am Creating a job with owner as my id.
When the job runs, it creates some internal clones of the job, the owner of
which is 'sa'.
execute @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID output ,
@.job_name = @.szTargetJobName,
@.owner_login_name = N'sa', @.description = N'Autogenerated.',
@.category_name = N'RunTime Job',
@.enabled = 1
When these clones try to access the linked server, the job fails because of
the access permission issues for the 'sa' account on the linked server.
I am not able to figure out the reason for this.
What might be the reason and what will be the solution for this.
Thanks,
SwapHi
A default mapping between all logins on the local server and remote logins
on the linked server is automatically created by executing
sp_addlinkedserver, therefore you should not need to issue your
sp_addlinkedsrvlogin statement.
You may want to try droping the linked server and re-linking it or
specifically try mapping the sa accounts and see if it works.
Can you access the linked server through QA, or do you get an error message?
John
"swap@.@.@." wrote:

> Hi,
> I am creating a linked server with
> sp_addlinkedsrvlogin @.ServerAlias, 'TRUE', NULL, NULL, NULL
> I am Creating a job with owner as my id.
> When the job runs, it creates some internal clones of the job, the owner o
f
> which is 'sa'.
> execute @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID output ,
> @.job_name = @.szTargetJobName,
> @.owner_login_name = N'sa', @.description = N'Autogenerated.',
> @.category_name = N'RunTime Job',
> @.enabled = 1
> When these clones try to access the linked server, the job fails because o
f
> the access permission issues for the 'sa' account on the linked server.
> I am not able to figure out the reason for this.
> What might be the reason and what will be the solution for this.
> Thanks,
> Swap