Linked Server Problems from SQL2005

When I run linked server queries from SQL2005 it fails.
Query Example :
SELECT * FROM [servername].master.dbo.sysobjects
Error:
OLE DB provider "SQLNCLI" for linked server " servername" 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.
Notes:
The linked server is configured to use the current logins security
context. It does not matter whether I connect to SQL2000 or SQL2005.
I have a case with MS (6 hours on the phone yesterday), but no help
yet. All spn settings are as recommended by Microsoft. The MS engineer
states that SQL is not passing the login as indicated in a capture file
from netcap.
Any help is appreciated.
TerryHi
Is the remote server SQL2005? Have you tried using a SQL Server login? Can
you use openquery to return the result?
John
"Terry" wrote:

> When I run linked server queries from SQL2005 it fails.
> Query Example :
> SELECT * FROM [servername].master.dbo.sysobjects
> Error:
> OLE DB provider "SQLNCLI" for linked server " servername" 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.
> Notes:
> The linked server is configured to use the current logins security
> context. It does not matter whether I connect to SQL2000 or SQL2005.
> I have a case with MS (6 hours on the phone yesterday), but no help
> yet. All spn settings are as recommended by Microsoft. The MS engineer
> states that SQL is not passing the login as indicated in a capture file
> from netcap.
> Any help is appreciated.
> Terry
>|||Terry wrote:
> When I run linked server queries from SQL2005 it fails.
> Query Example :
> SELECT * FROM [servername].master.dbo.sysobjects
> Error:
> OLE DB provider "SQLNCLI" for linked server " servername" 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.
> Notes:
> The linked server is configured to use the current logins security
> context. It does not matter whether I connect to SQL2000 or SQL2005.
> I have a case with MS (6 hours on the phone yesterday), but no help
> yet. All spn settings are as recommended by Microsoft. The MS engineer
> states that SQL is not passing the login as indicated in a capture file
> from netcap.
> Any help is appreciated.
> Terry
>
You are connecting to ServerA using Windows Authentication, not a SQL
login. The linked server connection is attempting to pass your SQL
login over to ServerB. You'll have to login to ServerA using a true SQL
login, or setup an explicit user mapping in the linked server definition.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I should be able to use Windows Authenitication, and have always in the
past with SQL2000. Auditing concerns require I know who is connecting.
Mapped logins do work but this is not a solution.
The problem occurs when I connect to SQL2000 or SQL2005 from SQL2005.
Terry
Tracy McKibben wrote:
> Terry wrote:
> You are connecting to ServerA using Windows Authentication, not a SQL
> login. The linked server connection is attempting to pass your SQL
> login over to ServerB. You'll have to login to ServerA using a true SQL
> login, or setup an explicit user mapping in the linked server definition.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||On the 2000 server, run the instcat.sql from the latest service pack you
have installed. This fixes the connect strings. You have to edit the
instcat.sql to put in the correct SQL version. This is documented in the
instcat.sql file. This fixed my 2005->2000 linked server problems. Same as
your problem.
Jackie
"Terry" <tduffy@.calamos.com> wrote in message
news:1152881495.272827.29100@.h48g2000cwc.googlegroups.com...
>I should be able to use Windows Authenitication, and have always in the
> past with SQL2000. Auditing concerns require I know who is connecting.
> Mapped logins do work but this is not a solution.
> The problem occurs when I connect to SQL2000 or SQL2005 from SQL2005.
> Terry
> Tracy McKibben wrote:
>|||Thank you for the responses...
instcat.sql fixes the schema binding errors on 2005->2000 servers. It
does not resolve my issues. instcat has already been run on my 2000
boxes.
Terry
Jackie Brophy wrote:[vbcol=seagreen]
> On the 2000 server, run the instcat.sql from the latest service pack you
> have installed. This fixes the connect strings. You have to edit the
> instcat.sql to put in the correct SQL version. This is documented in the
> instcat.sql file. This fixed my 2005->2000 linked server problems. Same as
> your problem.
> Jackie
> "Terry" <tduffy@.calamos.com> wrote in message
> news:1152881495.272827.29100@.h48g2000cwc.googlegroups.com...|||Hi Terry
I assume that you are using the latest service packs on both instances?
Previously this error has been the result of a virus checker or firewall
blocking communications or possibly a faulty network. Do you get any command
s
running on the remote system?
Windows authentication should not require the user to map a linked server
login, but you can run it with the remote server name parameter only to make
sure these are mapped.
You may want to try and check what protocols you are using and try a
different one!
John
"Terry" wrote:

> Thank you for the responses...
> instcat.sql fixes the schema binding errors on 2005->2000 servers. It
> does not resolve my issues. instcat has already been run on my 2000
> boxes.
> Terry
> Jackie Brophy wrote:
>|||We are still on RTM SQL05, but similar issues (double hops only) exist
on a dev box with sp1. SQL 2000 is a mix of SP3 and SP4.
Firewall and anti-virus are not any issue. I am not sure what you mean
by "commands
running on the remote system." Also, any ideas what might be "faulty"
in the network.
Linked servers with mapped logins works.
Thanks
John Bell wrote:[vbcol=seagreen]
> Hi Terry
> I assume that you are using the latest service packs on both instances?
> Previously this error has been the result of a virus checker or firewall
> blocking communications or possibly a faulty network. Do you get any comma
nds
> running on the remote system?
> Windows authentication should not require the user to map a linked server
> login, but you can run it with the remote server name parameter only to ma
ke
> sure these are mapped.
> You may want to try and check what protocols you are using and try a
> different one!
> John
> "Terry" wrote:
>|||Hi Terry
Use SQL profiler on both machines to try and see what is happening. Did you
check the protocols being used?
http://msdn2.microsoft.com/en-us/library/ms175496.aspx
You may want to make sure that this still occurs on the latest hotfixes for
each version. If that fails you may want to raise an incident with PSS for
this.
John
"Terry" wrote:

> We are still on RTM SQL05, but similar issues (double hops only) exist
> on a dev box with sp1. SQL 2000 is a mix of SP3 and SP4.
> Firewall and anti-virus are not any issue. I am not sure what you mean
> by "commands
> running on the remote system." Also, any ideas what might be "faulty"
> in the network.
> Linked servers with mapped logins works.
> Thanks
>
> John Bell wrote:
>

Linked Server Not Displaying Table Details

I have created a linked server using my local SQL2005. The linked server can be seen as a linked server, but the database can not been expanded to see the tables, stored procedures, views, and other details regarding the linked server. The only method that I have found that will allow me to see all the details is to use XP's Remote Desktop Connection to access the database. Has anyone else experienced this problem, if so, how did you resolve it?

Hi,

you need SQL Server 2k5 SP2 to browse through databases, tables etc from UI.

Code Snippet

EXEC sp_addlinkedserver
@.server = N'LONDON2',
@.srvproduct = N'',
@.provider = N'SQLNCLI',
@.provstr = N'SERVER=192.168.100.150;Integrated Security=True'

EXEC sp_tables_ex @.table_server = 'LONDON2',
@.table_catalog = 'AdventureWorks',
@.table_schema = 'HumanResources',
@.table_type = 'TABLE'

Linked server from SQL2005 to SQL2000

Hi

I use the following SQL statements to create a link server. RemoteServerName is an SQL2000 and I am executing this in another machine which is SQL2005.

Though the link server is created successfully, I am not able to se the tables under it so could not query anything.

-

USE [master]

EXEC master.dbo.sp_addlinkedserver @.server = N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'collation compatible', @.optvalue=N'false'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'data access', @.optvalue=N'true'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'rpc',@.optvalue=N'false'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'rpc out', @.optvalue=N'false'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'connect timeout', @.optvalue=N'0'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'collation name', @.optvalue=null

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'query timeout', @.optvalue=N'0'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'use remote collation', @.optvalue=N'true'

EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself = N'False', @.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'

Did you check permissions of remoteUser on the remote server instance? You can do this by just using the remote login to connect to the server using ISQLW and see what tables you can access or check your permissions.|||Yes, remoteUser is able to access RemoteServerName through Query Analizer.|||I didn't mean just accessing the server. That works since you didn't get any errors. What tables can you access? What happens if you try to select from a known table?|||

You have to use the Servername.Database.UserObject.Table on the query..

example,

Select * from [RemoteServer\MSSS2000].NorthWind.dbo.Orders

Linked server from SQL2005 to SQL2000

Linked server from SQL2005 to SQL2000

Hi

I use the following SQL statements to create a link server. RemoteServerName is an SQL2000 and I am executing this in another machine which is SQL2005.

Though the link server is created successfully, I am not able to se the tables under it so could not query anything.

--

USE [master]

EXEC master.dbo.sp_addlinkedserver @.server = N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'collation compatible', @.optvalue=N'false'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'data access', @.optvalue=N'true'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'rpc',@.optvalue=N'false'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'rpc out', @.optvalue=N'false'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'connect timeout', @.optvalue=N'0'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'collation name', @.optvalue=null

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'query timeout', @.optvalue=N'0'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'use remote collation', @.optvalue=N'true'

EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself = N'False', @.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'

What do you mean by "I am not able to see the tables under it"... have you tried:

SELECT * FROM [targetServer].[master].dbo.SysObjects to see if you are actually connected (and can query) ?

|||No, this does not work, I am getting no object found|||Can you post your query to access the linked server, as well as the error message you got? Maybe you got right object name, but incorrect schema name?|||

Hey u can't see the link server objects in 2005 as u used to see in sql2000.

But u can query an object as u wish.

if u know the name of any table.

Try to query the link server like

Select * from LinkServerName...tablename.

You can also use openquery statement.

Good Luck

|||Do you mean you can't find Linked Server objects in Management Studio? Actually?Linked Server objects are moved to the "Server Objects" catalog in Management Studio, no longer under the Security branch as in Enterprise Manager

You can also get information about linker servers usingsp_linkedservers system procedure.