hi, I have a very frustrating issue trying to set up a linked server from SQL 2005 to SQL 2000, with windows account\authentication. 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

I get the following errors when I attempt to run a query ie (select * from server.db.dbo.table): PLEASE HELP![]

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

Try connecting using the named pipes protocol to sql 2000 server from QA.
If it succeeds then when you cofiguring your linked server in SQL 2005 use 2000 server as npervername instead of just server name.

EX: EXEC sp_addlinkedserver @server='Server2', @srvproduct='', @provider='SQLNCLI', @datasrc='npQLSERVER2000, @provstr='Integrated Security=SSPI'


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?

Check whether named-pipes and tcp-ip are enabled on the linked server.

---

Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

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

Hi,
which version you are using !? SQL Express,Evaluation and Developer Edition allows local client only by default.
could you check for the remote connection service is started in Surface Area Configuration!!!

Regards


Hemantgiri S. Goswami
"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami
------------------------
http://hemantgirisgoswami.blogspot.com

Check the login privileges between SQL 2005 and 2000 versio.

---

Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

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.

Can you check whether SQL Browser is ON on SQL Server 2005, I know it is not compulsory but might try with ON.

Is the authentication mode between 2005 and 2000 same?

---

Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

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.

It seems he tried that too.

Could you post the TSQL used to configure the linked server and mention where you've executed this linked server setup.

---

Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

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!

If it works for SQL authentication then it should work for Windows authentication too, make sure to check account privleges on both SQL 2005 & 2000 servers.

---

Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.