Linked Server problem

Last Post 08 Apr 2008 03:55 PM by gilou28. 7 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
gilou28
New Member
New Member

--
03 Apr 2008 07:03 AM
I used to be able to do this :-((((

I'm trying to define a new linked server from server A to server B and I always hit the connection refused error.

In the Linked server definition, I force the connection to use the service account that runs both instances and that is part of the administrator group on each machine.

I get:
TCP Provider: No connection could be made because the target machine actively refused it.
OLE DB provider "SQLNCLI" for linked server "bla bla bla" returned message "Login timeout expired".

.....

ve tried with :@provstr=N'Integrated Security=SSPI;' and @provstr=N'Integrated Security=TRUE;'
no difference

One detail, ONLY TCPIP protocol is enabled on these servers. Shared Memory, Named Pipes and VIA are disabled and we are not using Kerberos. Security is Windows on both servers.

This is the script that I used:
quote:


EXEC master.dbo.sp_addlinkedserver @server = N'MYSERVER\MYINSTANCE', @provider=N'SQLNCLI',@provstr=N'Integrated Security=True;',@srvproduct=''
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MYSERVER\MYINSTANCE',@useself=N'False',@locallogin=NULL,@rmtuser=N'MYDOMAIN\$Myuser',@rmtpassword='hahaha'

gilou28
New Member
New Member

--
04 Apr 2008 09:09 AM
humm, I din't change that option from the default, so I think it is off.

Is the Service Broker required? It is disabled on the target instance and the SQL Agent is also disabled.
gilou28
New Member
New Member

--
04 Apr 2008 10:40 AM
ok thanks, I'll give that a try.

gilou28
New Member
New Member

--
07 Apr 2008 09:25 AM
No luck, getting the following error msg after enabling database chaining on both instance and restarting them:

Login failed for user 'MYDOMAIN\$blablabla'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)

MYDOMAIN\$blablabla is a domain user that is running the SQL Server and Agent services on both servers. This user is also local admin on each server.

I can connect with that domain user on both servers without any problems using Windows auth.

I'm forcing the use of that user in the definition of the Linked server.




gilou28
New Member
New Member

--
08 Apr 2008 04:09 AM
I'm not sure I'm following your assertion rm...

First of all, those linked server that I'm trying to define will only be used by monitoring processes, and those are fired by the Agent service account which is the same as the Instance service account which is part of the administrator group of all the servers hosting SQL (developpement setup, KISS).

The domain account used in the definition of the linked server is forced to be that same account, eliminating all security issues.... so I tought :-)




gilou28
New Member
New Member

--
08 Apr 2008 03:55 PM
Nope, it is forced to a specific user, the same domain user that is running the services (SQL) ans that is part of the admin group of both source and target.
gilou28
New Member
New Member

--
09 Apr 2008 09:06 AM
You are both right, and sorry rm I did not understood what you said initially.

Since I'm using Windows auth, It seems that I'm forced to pass the current user security context.

But, when doing so, I get the following errors (I'm connected with my user which is "god" on MyPlanet :-)

The OLE DB provider "SQLNCLI" for linked server "MyServer\MyInstance" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "MyServer\MyInstance".
OLE DB provider "SQLNCLI" for linked server "MyServer\MyInstance" returned message "Invalid authorization specification".
OLE DB provider "SQLNCLI" for linked server "MyServer\MyInstance" returned message "Invalid connection string attribute". (Microsoft SQL Server, Error: 7399)

gilou28
New Member
New Member

--
10 Apr 2008 04:25 AM
Thanks, but I think that this applies to SQL Server 2005 pre-SP2.

Both instances are SP2 version 3200.


Acceptable Use Policy
---