SQL Server Performance

Linked Server 2005 to 2000 - using Windows Auth

Discussion in 'SQL Server 2005 General DBA Questions' started by sql_jr, Nov 28, 2006.

  1. sql_jr New Member

    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].
  2. MohammedU New Member

    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.
  3. sql_jr New Member

    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?
  4. satya Moderator

    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.
  5. sql_jr New Member

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

    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
  7. satya Moderator

    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.
  8. sql_jr New Member

    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.
  9. satya Moderator

    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.
  10. sql_jr New Member

    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!
  11. MohammedU New Member

    Did you try configuring Linked server with SQL login?



    Mohammed U.
  12. satya Moderator

    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.
  13. sql_jr New Member

    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!
  14. sql_jr New Member

    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!
  15. satya Moderator

    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.
  16. MohammedU New Member

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


    Mohammed U.
  17. jadjr New Member

    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.
  18. jadjr New Member

    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.
  19. satya Moderator

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

    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
  21. satya Moderator

    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...
  22. rohit2900 Member

    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.
  23. satya Moderator

    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.
  24. rohit2900 Member

    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


  25. satya Moderator

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

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

    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

Share This Page