Linked Server 2005 to 2000 - using Windows Auth

hi, I have a very frustrating issue trying to set up a linked server from SQL 2005 to SQL 2000, with windows accountauthentication. TCP doesn't work at all, and read that named pipes could be used instead. Note: NP is enabled on both servers as well, and I set up an alias on the SQL2K box to the SQL2K box. Also, timeout on the linked server is set to 0<br /><br />I get the following errors when I attempt to run a query ie (select * from server.db.dbo.table): PLEASE HELP![<img src='/community/emoticons/emotion-6.gif' alt='' />]<br /><br />OLE DB provider "SQLNCLI" for linked server "JDEQADEV1" returned message "Login timeout expired".<br />OLE DB provider "SQLNCLI" for linked server "JDEQADEV1" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".<br />Msg 5, Level 16, State 1, Line 0<br />Named Pipes Provider: Could not open a connection to SQL Server [5].

Try connecting using the named pipes protocol to sql 2000 server from QA.<br />If it succeeds then when you cofiguring your linked server in SQL 2005 use 2000 server as np<img src='/community/emoticons/emotion-7.gif' alt=':s' />ervername instead of just server name.<br /><br />EX: EXEC sp_addlinkedserver @server='Server2', @srvproduct='', @provider='SQLNCLI', @datasrc='np<img src='/community/emoticons/emotion-7.gif' alt=':S' />QLSERVER2000, @provstr='Integrated Security=SSPI' <br /><br /><br />Mohammed U.

Mohammed, thx for reply. That didn't seem to work, unless I did something wrong:

OLE DB provider "SQLNCLI" for linked server "JDEQADEV1" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "JDEQADEV1" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 5, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [5].
OLE DB provider "SQLNCLI" for linked server "JDEQADEV1" returned message "Invalid connection string attribute".


Plus I am trying to have it impersonate using another windows account set up for linked servers. I also did try using a sql account and that failed too?

hi, Satya I believe they both are. Please help! I need to get this working...Any ideas?

What is the requirement? I did not use 'sa' or sysadmin, but gave rights on the linked server to the id only to access the db necessary.

1.SQL Browser is ON
2. I can log into the Linked Server with the id I created (and access/see the tables.
3. Authentication is Mixed for Both Servers.

H-E-L-P! Thanks again!

Did you try configuring Linked server with SQL login?



Mohammed U.

I actually went throught the SSM --> Server Objects -> Linked Servers on 2005 to set it up. Is there a way to script it? Basically, I select SQL Server, added the login, and selected use this security context (for the login) to log in. Hope this makes sense. Thanks!

Ok, I tried using a sql account again (sure it didn't work before), with limited rights to 2-3 tables, and....tada! - it works.

Disappointed that I could not get the Windows Auth working, but satisfied for now. Many thanks!

Check the 2005 service account access to SQL 2000...


Mohammed U.

I am having a similiar issue as has been described here except for one thing. I can access the SQL2000 linked server (security context set to use this specific windows account) while I am logged in as that user. However, if I login as a different user, I then receive the "TCP Provider: An existing connection was forcibly closed by the remote host. Login failed for domainuser". I have not set up anything for Kerberose, but as I understand it, it is only needed in a double-hop instance. In this case, since I have selected "Be made using this context", I am under the impression that the login happens directly from the SQL Server to the linked server thereby creating only a single hop. Can you explain what I need to do to make this work?
Thanks.

I am having a similiar issue as has been described here except for one thing. The two servers are in different modes and will need to stay that way (2005- mixed; 2000 Windows). When using the "...made using this context..." I also get the "TCP Provider: An existing connection was forcibly closed by the remote host. Login failed for domainuser". I have not set up anything for Kerberose, but as I understand it, it is only needed in a double-hop instance. In this case, since I have selected "Be made using this context", I am under the impression that the login happens directly from the SQL Server to the linked server thereby creating only a single hop. Can you explain what I need to do to make this work?
Thanks.

We are experiencing the same problem. Satya,
Can you try to reproduce this on your end? Create a linked server as follows.
Provider: SQL Native ClientProduct Name: OLE DBData Source: (name of your sql server instance)Security Tabselect "Be made using the login's current security context"Server Options TabSet RPC and RPC Out to True
From a PC/Laptop connect to the instance containing the linked server and run "Select * from LinkedServer.Database.dbo.sysobjects"
See if you get the following error.OLE DB provider "SQLNCLI" for linked server "LVTS_SQL6" returned message "Communication link failure".Msg 10054, Level 16, State 1, Line 0TCP Provider: An existing connection was forcibly closed by the remote host.Msg 18452, Level 14, State 1, Line 0Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
I believe the problem is related to MSDTC on Windows 2003 Server and the linked server option "Be made using the login's current security context"
Thanks, Dave

HI,
I'm alos facing the same issue.
We havea server which is in Domain and is Running on SQL Server 2000 and with windows authentication.
Now we have another server running on 2005 and is not part of any domain and running in mixed mode. Now I want ot create a linked server on the 2nd server to the one which is running in domain. Wht should I do? I already created a user in domain and granted the necessary db access in 1st server. and created the same user (with same user id and p/w) in 2nd server which is not in domain and granted the db access. But still I'm not able to access the tables.
Error Message: TCP Provider: An existing connection was forcibly closed by remote host.
Reason: Not associated with trusted sql server connection.
Can anybody help and give a step by step process to handle the situation.

I missed one thing Both the servers are not part of any domain and 1st one is running with windows authentication and 2nd is in mixed mode...Now I want to create linked server on 2nd server pointing to 1st server??? Is this possible? If Yes then How?
FYI: 1st server is on SQL Server 2000 standard SP4...2nd is on SQL Server 2005 Standard.
Its Very Urgent....
Thx

Then Should I create the same user in 2nd server with same p/w and then try to connect???
Will this work???

Guys....I've tried everything to make this possible but I wasn't able to do it....
Can any body help me?
Satya where r u?
Rohit