Linked Server 2005 to 2000 – using Windows Auth | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

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

That TCP error sounds like some network issue, check whether any dropped packet or issue within firewall for such authentication

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

I have tried it and didn’t get that error. The SQL instance runs under a domain accoun which has ADMIN privileges on that server along with permissions for MSDTC….
I’ll try with Win2k too to see whether it happens…

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.

In the past I have also attempted following step to resolve:
Add the following registry key and reboot the server.
HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesTcpipParametersSycAttackProtect{DWORD} = 0
Make sure to test this before you apply on the live server.

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

You should use with a common account between these server in order to pass-thru the authentication whihc is the first layer of connectivity.

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

]]>