Can't get linked servers to work

I'm having trouble getting a new linked server to work.
Server Mfr is 2005 Dev (sp1) and is the 'local' server.
Server Krypton is 2000 EE sp3 and is the target server I need to link to and
query.
I have a valid Windows login on both systems and I'm in the sysadmin group
on both servers.
After linking the servers using Mgt Studio, I ran this:
EXEC sp_addlinkedsrvlogin @.rmtsrvname = 'KRYPTON', @.useself = 'true'
When I try running this query:
SELECT * FROM OPENQUERY(KRYPTON, 'SELECT * FROM CRICUST WHERE CustName =
''Acme''')
I get these errors:
OLE DB provider "SQLNCLI" for linked server "KRYPTON" 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.
Can you see what I'm missing?
Thanks!
djNo login credentials are being passed to the remote server.
Did you set everything up needed for delegation?
Have you tried with a SQL Login to see if the basics parts
(without the delegation issues) are working? For the linked
server, Just map all users to a valid SQL login on the
remote server to see if that works.
-Sue
On Thu, 10 Aug 2006 06:40:02 -0700, dj
<dj@.discussions.microsoft.com> wrote:

>I'm having trouble getting a new linked server to work.
>Server Mfr is 2005 Dev (sp1) and is the 'local' server.
>Server Krypton is 2000 EE sp3 and is the target server I need to link to an
d
>query.
>I have a valid Windows login on both systems and I'm in the sysadmin group
>on both servers.
>After linking the servers using Mgt Studio, I ran this:
>EXEC sp_addlinkedsrvlogin @.rmtsrvname = 'KRYPTON', @.useself = 'true'
>When I try running this query:
>SELECT * FROM OPENQUERY(KRYPTON, 'SELECT * FROM CRICUST WHERE CustName =
>''Acme''')
>I get these errors:
>OLE DB provider "SQLNCLI" for linked server "KRYPTON" 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.
>Can you see what I'm missing?
>Thanks!
>dj|||Hi Sue -
We're not on AD, so I believe delegation isn't an option.
If I'm using Windows authentication and my account exits on both machines,
do I still need to pass credentials? I thought the 'useself' parameter took
care of that.
Or am I just very confused (quite possibly)?
Thanks.
"Sue Hoegemeier" wrote:

> No login credentials are being passed to the remote server.
> Did you set everything up needed for delegation?
> Have you tried with a SQL Login to see if the basics parts
> (without the delegation issues) are working? For the linked
> server, Just map all users to a valid SQL login on the
> remote server to see if that works.
> -Sue
> On Thu, 10 Aug 2006 06:40:02 -0700, dj
> <dj@.discussions.microsoft.com> wrote:
>
>|||You are correct that if you aren't using AD, you can't use
delegation. Delegation will pass your authentication
information from one server to another. useself just says to
use the current login for authentication against the remote
server but doesn't really manage any of the passing of
credentials or how that will work (or not work).
Try using a SQL login for the security mappings to the
remote linked server.
-Sue
On Thu, 10 Aug 2006 11:07:01 -0700, dj
<dj@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Hi Sue -
>We're not on AD, so I believe delegation isn't an option.
>If I'm using Windows authentication and my account exits on both machines,
>do I still need to pass credentials? I thought the 'useself' parameter too
k
>care of that.
>Or am I just very confused (quite possibly)?
>Thanks.
>"Sue Hoegemeier" wrote:
>

Can't created a linked server on a named instance to default insta

I have been trying to create a linked server on a named instance to a defaul
t
instance using intergrated security. It seems to work fine from a default
instance to any other SQL instance, including a named instance, but any time
I create the linked server on the named instance a call to the linked server
fails with the error:
Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
All servers are running SQL Server 2000 and the only difference in the
installation besides the fact one is named, is that the named instance is SP
4
and all others are SP3.
From a default instance the following works fine:
sp_addlinkedserver 'ORDEV01'
ORDEV01.Master.dbo.sp_helpdb
and the following to a named instance works as well:
sp_addlinkedserver 'LOU\Master'
[LOU\Master].Master.dbo.sp_helpdb
But if I try to created a linked server on the name dinstance LOU\Master I
get the error:
Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Thanks in advance for any help.Byron
Does the instance have Windows Authentication?
"Byron" <Byron@.discussions.microsoft.com> wrote in message
news:CB90A676-83FF-41CC-9C04-221920F998FF@.microsoft.com...
>I have been trying to create a linked server on a named instance to a
>default
> instance using intergrated security. It seems to work fine from a default
> instance to any other SQL instance, including a named instance, but any
> time
> I create the linked server on the named instance a call to the linked
> server
> fails with the error:
> Msg 18452, Level 14, State 1, Line 1
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> All servers are running SQL Server 2000 and the only difference in the
> installation besides the fact one is named, is that the named instance is
> SP4
> and all others are SP3.
> From a default instance the following works fine:
> sp_addlinkedserver 'ORDEV01'
> ORDEV01.Master.dbo.sp_helpdb
> and the following to a named instance works as well:
> sp_addlinkedserver 'LOU\Master'
> [LOU\Master].Master.dbo.sp_helpdb
> But if I try to created a linked server on the name dinstance LOU\Master I
> get the error:
> Msg 18452, Level 14, State 1, Line 1
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> Thanks in advance for any help.

Can't create linked server

Dear Sir,
I have a remote db server which I can connect using Query Analyzer but can't
setup a linked server to it, using same user account, any suggestion how to
trouble shoot?
Henry
Henry
What is an error you are getting?
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:C31AA693-9574-4636-B56E-5E004189F6CF@.microsoft.com...
> Dear Sir,
> I have a remote db server which I can connect using Query Analyzer but
> can't
> setup a linked server to it, using same user account, any suggestion how
> to
> trouble shoot?
> Henry
|||Dear Uri Dimant,
I can create the linked server but whenever I tried to access, e.g. click on
the Security->Linked Server->RemoteServer->Table/View, it return the error of
Error 7399
Both of my local and remote db are SQL 2000.
Henry
"Uri Dimant" wrote:

> Henry
> What is an error you are getting?
> "Henry" <Henry@.discussions.microsoft.com> wrote in message
> news:C31AA693-9574-4636-B56E-5E004189F6CF@.microsoft.com...
>
>
|||Hi Henry,
Do u have sa password on both the server.
if yes then drop ur linked server and create another one
give remote user and local user as sa and sa password.
in the other server type should be sql server if u create linked
server between sql server.
click the remote login option and give sa and sa password.
check the server option
these three should be selected
dataaccess,
RPC and RPC Out.
hope this help u
from
Doller
Henry wrote:[vbcol=seagreen]
> Dear Uri Dimant,
> I can create the linked server but whenever I tried to access, e.g. click on
> the Security->Linked Server->RemoteServer->Table/View, it return the error of
> Error 7399
> Both of my local and remote db are SQL 2000.
> Henry
> "Uri Dimant" wrote:
|||This has some example syntax for adding a linked server:
http://msdn.microsoft.com/library/en..._adda_8gqa.asp
-oj
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:5B480C91-E86A-46FC-AFC3-D59D8CBBBCCA@.microsoft.com...[vbcol=seagreen]
> Dear Uri Dimant,
> I can create the linked server but whenever I tried to access, e.g. click
> on
> the Security->Linked Server->RemoteServer->Table/View, it return the error
> of
> Error 7399
> Both of my local and remote db are SQL 2000.
> Henry
> "Uri Dimant" wrote:
|||Dear Doller,
I don't have SA account on remote db right now, but I have other linked
server using account without SA rights, e.g. have dbo rights and can also
make connection. Do you know if there any docuement for the access rights
requirement?
Henry
"doller" wrote:

> Hi Henry,
> Do u have sa password on both the server.
> if yes then drop ur linked server and create another one
> give remote user and local user as sa and sa password.
> in the other server type should be sql server if u create linked
> server between sql server.
> click the remote login option and give sa and sa password.
> check the server option
> these three should be selected
> dataaccess,
> RPC and RPC Out.
> hope this help u
> from
> Doller
>
> Henry wrote:
>

Can't create linked server

Dear Sir,
I have a remote db server which I can connect using Query Analyzer but can't
setup a linked server to it, using same user account, any suggestion how to
trouble shoot?
HenryHenry
What is an error you are getting?
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:C31AA693-9574-4636-B56E-5E004189F6CF@.microsoft.com...
> Dear Sir,
> I have a remote db server which I can connect using Query Analyzer but
> can't
> setup a linked server to it, using same user account, any suggestion how
> to
> trouble shoot?
> Henry|||Dear Uri Dimant,
I can create the linked server but whenever I tried to access, e.g. click on
the Security->Linked Server->RemoteServer->Table/View, it return the error of
Error 7399
Both of my local and remote db are SQL 2000.
Henry
"Uri Dimant" wrote:
> Henry
> What is an error you are getting?
> "Henry" <Henry@.discussions.microsoft.com> wrote in message
> news:C31AA693-9574-4636-B56E-5E004189F6CF@.microsoft.com...
> > Dear Sir,
> > I have a remote db server which I can connect using Query Analyzer but
> > can't
> > setup a linked server to it, using same user account, any suggestion how
> > to
> > trouble shoot?
> > Henry
>
>|||Hi Henry,
Do u have sa password on both the server.
if yes then drop ur linked server and create another one
give remote user and local user as sa and sa password.
in the other server type should be sql server if u create linked
server between sql server.
click the remote login option and give sa and sa password.
check the server option
these three should be selected
dataaccess,
RPC and RPC Out.
hope this help u
from
Doller
Henry wrote:
> Dear Uri Dimant,
> I can create the linked server but whenever I tried to access, e.g. click on
> the Security->Linked Server->RemoteServer->Table/View, it return the error of
> Error 7399
> Both of my local and remote db are SQL 2000.
> Henry
> "Uri Dimant" wrote:
> > Henry
> > What is an error you are getting?
> >
> > "Henry" <Henry@.discussions.microsoft.com> wrote in message
> > news:C31AA693-9574-4636-B56E-5E004189F6CF@.microsoft.com...
> > > Dear Sir,
> > > I have a remote db server which I can connect using Query Analyzer but
> > > can't
> > > setup a linked server to it, using same user account, any suggestion how
> > > to
> > > trouble shoot?
> > > Henry
> >
> >
> >|||This has some example syntax for adding a linked server:
http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_adda_8gqa.asp
-oj
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:5B480C91-E86A-46FC-AFC3-D59D8CBBBCCA@.microsoft.com...
> Dear Uri Dimant,
> I can create the linked server but whenever I tried to access, e.g. click
> on
> the Security->Linked Server->RemoteServer->Table/View, it return the error
> of
> Error 7399
> Both of my local and remote db are SQL 2000.
> Henry
> "Uri Dimant" wrote:
>> Henry
>> What is an error you are getting?
>> "Henry" <Henry@.discussions.microsoft.com> wrote in message
>> news:C31AA693-9574-4636-B56E-5E004189F6CF@.microsoft.com...
>> > Dear Sir,
>> > I have a remote db server which I can connect using Query Analyzer but
>> > can't
>> > setup a linked server to it, using same user account, any suggestion
>> > how
>> > to
>> > trouble shoot?
>> > Henry
>>|||Dear Doller,
I don't have SA account on remote db right now, but I have other linked
server using account without SA rights, e.g. have dbo rights and can also
make connection. Do you know if there any docuement for the access rights
requirement?
Henry
"doller" wrote:
> Hi Henry,
> Do u have sa password on both the server.
> if yes then drop ur linked server and create another one
> give remote user and local user as sa and sa password.
> in the other server type should be sql server if u create linked
> server between sql server.
> click the remote login option and give sa and sa password.
> check the server option
> these three should be selected
> dataaccess,
> RPC and RPC Out.
> hope this help u
> from
> Doller
>
> Henry wrote:
> > Dear Uri Dimant,
> > I can create the linked server but whenever I tried to access, e.g. click on
> > the Security->Linked Server->RemoteServer->Table/View, it return the error of
> > Error 7399
> >
> > Both of my local and remote db are SQL 2000.
> >
> > Henry
> >
> > "Uri Dimant" wrote:
> >
> > > Henry
> > > What is an error you are getting?
> > >
> > > "Henry" <Henry@.discussions.microsoft.com> wrote in message
> > > news:C31AA693-9574-4636-B56E-5E004189F6CF@.microsoft.com...
> > > > Dear Sir,
> > > > I have a remote db server which I can connect using Query Analyzer but
> > > > can't
> > > > setup a linked server to it, using same user account, any suggestion how
> > > > to
> > > > trouble shoot?
> > > > Henry
> > >
> > >
> > >
>

Can't create linked server

Dear Sir,
I have a remote db server which I can connect using Query Analyzer but can't
setup a linked server to it, using same user account, any suggestion how to
trouble shoot?
HenryHenry
What is an error you are getting?
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:C31AA693-9574-4636-B56E-5E004189F6CF@.microsoft.com...
> Dear Sir,
> I have a remote db server which I can connect using Query Analyzer but
> can't
> setup a linked server to it, using same user account, any suggestion how
> to
> trouble shoot?
> Henry|||Dear Uri Dimant,
I can create the linked server but whenever I tried to access, e.g. click on
the Security->Linked Server->RemoteServer->Table/View, it return the error o
f
Error 7399
Both of my local and remote db are SQL 2000.
Henry
"Uri Dimant" wrote:

> Henry
> What is an error you are getting?
> "Henry" <Henry@.discussions.microsoft.com> wrote in message
> news:C31AA693-9574-4636-B56E-5E004189F6CF@.microsoft.com...
>
>|||Hi Henry,
Do u have sa password on both the server.
if yes then drop ur linked server and create another one
give remote user and local user as sa and sa password.
in the other server type should be sql server if u create linked
server between sql server.
click the remote login option and give sa and sa password.
check the server option
these three should be selected
dataaccess,
RPC and RPC Out.
hope this help u
from
Doller
Henry wrote:[vbcol=seagreen]
> Dear Uri Dimant,
> I can create the linked server but whenever I tried to access, e.g. click
on
> the Security->Linked Server->RemoteServer->Table/View, it return the error
of
> Error 7399
> Both of my local and remote db are SQL 2000.
> Henry
> "Uri Dimant" wrote:
>|||This has some example syntax for adding a linked server:
http://msdn.microsoft.com/library/e...p_adda_8gqa.asp
-oj
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:5B480C91-E86A-46FC-AFC3-D59D8CBBBCCA@.microsoft.com...[vbcol=seagreen]
> Dear Uri Dimant,
> I can create the linked server but whenever I tried to access, e.g. click
> on
> the Security->Linked Server->RemoteServer->Table/View, it return the error
> of
> Error 7399
> Both of my local and remote db are SQL 2000.
> Henry
> "Uri Dimant" wrote:
>|||Dear Doller,
I don't have SA account on remote db right now, but I have other linked
server using account without SA rights, e.g. have dbo rights and can also
make connection. Do you know if there any docuement for the access rights
requirement?
Henry
"doller" wrote:

> Hi Henry,
> Do u have sa password on both the server.
> if yes then drop ur linked server and create another one
> give remote user and local user as sa and sa password.
> in the other server type should be sql server if u create linked
> server between sql server.
> click the remote login option and give sa and sa password.
> check the server option
> these three should be selected
> dataaccess,
> RPC and RPC Out.
> hope this help u
> from
> Doller
>
> Henry wrote:
>

Can't create a linked server when the servers are set up to replicate

I can't create a linked server when the same two servers are set up for repl
icatation. I get 'Error 15028: The server 'SERVERNAME' already exists.' whe
n I try to add a linked server. And it does exist as a Remote Server becaus
e as I mentioned, the two s
ervers are replicating data.
There has to be a way to add a linked server when two servers are already re
plicating. Or, there has to be a way to use the remote servers for data acc
ess.
Any suggestons?
Thanks,
SniperXYou may have to use sp_addlinkedsrvlogin . This was new for SQL 2000
remote servers. There's also a kb article documenting this issue.
274098 PRB: Adding a Linked Server Causes Error 15028
http://support.microsoft.com/?id=274098
From Books Online:
Setting Up the Local Server
In SQL Server 2000, create remote server connections for remote server
logins created by Windows Authentication by:
Setting up a local login mapping on a local server that defines what login
and password are used by an instance of SQL Server when it makes an RPC
connection to a remote server.
For logins created by Windows Authentication, you must create a mapping to
a login name and password. This login name and password must match the
incoming login and password expected by the remote server.
Using the sp_addlinkedsrvlogin stored procedure to create local login
mappings.
Note For logins created by SQL Server Authentication, it is not necessary
to create any local login mappings for executing a stored procedure against
a remote server.
Security Note When possible, use Windows Authentication.
274098 PRB: Adding a Linked Server Causes Error 15028
http://support.microsoft.com/?id=274098
When configuring new subscribers for replication, SQL Server adds the
subscriber as a remote server and stores its information in the
master..sysservers system table. Subsequent attempts to add a linked server
using the same name as an existing subscriber may cause error 15028 to
occur:
Server: Msg 15028, Level 16, State 1, Line 0
The server 'SQL1' already exists.
The article also references the use of sp_addlinkedsrvlogin
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Can't connect to linked SQL server: Cannot initialize the data source object of OLE DB provider

I find this most perplexing.

I have two servers, DEV and PROD. Now my DEV server works just great, I can connect to the linked server, query, etc... all is well.

So I'm setting up my PROD server and when I go to add the linked server I get:

Cannot initialize the data source object of OLE DB provider "SQLNCLI".... and Unable to complete login process due to delay in opening server connection.

Now I am running SQL Server 2005 and connecting to an SQL 2000 server.

The odd part is that this works just fine on DEV.

When I go to create the linked server I set:

Linked Server: "LinkedServerName"

Server Type: "SQL Server"

and that's it.

I go to Security and enter my DOMAIN\USER.ACCOUNT and then enter the login creds for the linked server.

When I click "OK" I get the above mentioned error code.

Any thoughts?

Please see if http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=990327&SiteID=1 could be useful for you.|||

Thanks, I'll check that out.

This whole thing is very odd as I even saved the working LinkedServer as TSQL script then executed it on the PROD server and with the EXACT same information for the linked server it fails to work.

I'll read up on the link you provided and keep my fingers crossed.

No in my case I'm not having an issue with impersonation. The credentials are all configured the same on both servers, yet my DEV server works while PROD fails...

Here is the error message I receive when I try to test the linked server connection:

"The test connection to the linked server failed."

Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "MaxersServer".
OLE DB provider "SQLNCLI" for linked server "MaxersServer" returned message "Unable to complete login process due to delay in opening server connection". (.Net SqlClient Data Provider)

Can't connect ODBC/SQLSrvr linked table from within COM+ component

I have an Access 2000 database containing an ODBC table linked to a SQL Serv
er 2000 database (using a system DSN).
No problem opening the table Using Access 2000 itself.
No problem even within a VB program (EXE) accessing that table using the fol
lowing code:
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = cnSrc
.Source = "SELECT * FROM " _
& "[ODBC;" & tbODBC.Properties("Jet OLEDB:Link Provider String") & "]."
_
& tbODBC.Name & vbCrLf & "WHERE FALSE"
.CursorLocation = adUseServer
End With
On Error Resume Next
rs.Open ' <-- ERROR RAISES HERE
cnSrc is an ADO Connection with Access2000 DB
tbODBC is an ADOX Table object corresponding to the linked ODBC table
But if the same code is placed in a COM+ component (DLL ActiveX), once invok
ed by any client, when the final "rs.Open" is executed, the following error
raises:
"Connection to <sql server database name> failed"
Number = 0x80004005
SQLState = 3151
I've searched many places but with no results.
Some help will be appreciated.
Thanks in advance
RafThe Jet OLEDB provider does not support distributed transactions since it
does not implement the ITransactionJoin OLE-DB interface.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Raf" <anonymous@.discussions.microsoft.com> wrote in message
news:0377EFF9-6D50-447F-B38F-779100742A52@.microsoft.com...
>I have an Access 2000 database containing an ODBC table linked to a SQL
>Server 2000 database (using a system DSN).
> No problem opening the table Using Access 2000 itself.
> No problem even within a VB program (EXE) accessing that table using the
> following code:
> Set rs = New ADODB.Recordset
> With rs
> .ActiveConnection = cnSrc
> .Source = "SELECT * FROM " _
> & "[ODBC;" & tbODBC.Properties("Jet OLEDB:Link Prov
ider
> String") & "]." _
> & tbODBC.Name & vbCrLf & "WHERE FALSE"
> .CursorLocation = adUseServer
> End With
> On Error Resume Next
> rs.Open ' <-- ERROR RAISES HERE
> cnSrc is an ADO Connection with Access2000 DB
> tbODBC is an ADOX Table object corresponding to the linked ODBC table
> But if the same code is placed in a COM+ component (DLL ActiveX), once
> invoked by any client, when the final "rs.Open" is executed, the following
> error raises:
> "Connection to <sql server database name> failed"
> Number = 0x80004005
> SQLState = 3151
> I've searched many places but with no results.
> Some help will be appreciated.
> Thanks in advance
> Raf
>