none
Cannot connect to SQL Server 2008 R2 remotely

    Question


  • I've attempting to create a Development box that host a SQL Server 2008 R2; however, I'm unable to connect to it remotely.
    I can't create an ODBC connection as well.

    My setup is this:
    Windows Server 2008 R2 Standard 64-bit

    Sql Server 2008 R2:
    Microsoft SQL Server Management Studio 10.0.1600.22 ((SQL_PreRelease).080709-1414 )
    Microsoft Analysis Services Client Tools 2007.0100.1600.022 ((SQL_PreRelease).080709-1414 )
    Microsoft Data Access Components (MDAC) 6.1.7601.17514 (win7sp1_rtm.101119-1850)
    Microsoft MSXML 3.0 6.0 
    Microsoft .NET Framework 2.0.50727.5456


    I went to the server properties in Microsoft SQL Server Management Studio and checked the "Allow remote connections to this server" under Connections section.  I also went to SQL Server Configuration Manager and under SQL Server Network Configuration I enabled the TCP/IP Protocol.  Then went to Windows Firewall with Advanced Security and created an inbound rule for for the Database Engine to allow connection for port 1433.

    I would imaging that doing all of this would grant me access remote access to the box but this is not the case.  

    Is there something i'm forgetting? 

    Wednesday, July 18, 2012 6:31 PM

Answers

  • Hi Emmanuel,

    Base on your error message, the reason that you get it might is the client stack could not receive SQL Server Resolution Protocol (SSRP) response UDP packet from SQL Server Browser. You can isolate the issue by the following steps:
    1) Make sure your server name is correct, e.g., no typo on the name.
    2) Make sure your instance name is correct and there is actually such an instance on your target machine.
    3) Make sure the server machine is reachable, e.g., DNS can be resolve correctly, you are able to ping the server (not always true).
    4) Make sure SQL Server Browser is running on the server (you have confirmed it).
    5) If firewall is enabled on the server, you need to put sqlbrowser.exe and/or UDP port 1434 into exception (please pay attention to this step).
    For more details, please refer to this blog.


    Best Regards,
    Ray Chen

    Tuesday, July 24, 2012 8:43 AM

All replies

  • Would you please share the exact error message you are getting ?

    Regards,
    Ahmed Ibrahim
    SQL Server Setup Team
    My Blog
    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you.
    This can be beneficial to other community members reading the thread.

    Wednesday, July 18, 2012 6:41 PM
  • This is the error below:


    Wednesday, July 18, 2012 6:46 PM
  • I've attempting to create a Development box that host a SQL Server 2008 R2; however, I'm unable to connect to it remotely.
    I can't create an ODBC connection as well.

    I went to the server properties in Microsoft SQL Server Management Studio and checked the "Allow remote connections to this server" under Connections section.  I also went to SQL Server Configuration Manager and under SQL Server Network Configuration I enabled the TCP/IP Protocol.  Then went to Windows Firewall with Advanced Security and created an inbound rule for for the Database Engine to allow connection for port 1433.

    Is there something i'm forgetting? 

    yes, you need to enable UDP on port 1344 to allow SQL Browser.

    see the following KB http://support.microsoft.com/kb/287932


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    Wednesday, July 18, 2012 6:51 PM
  • Check SQL Browser service is runing

    Network conenctivity between database & applicaiton servers by TRACERT command

    Check telent connectivity - telnet <IPadress> <portno on sql server runing>

    If SQL is not a default instance or not runing on default port, than open UDP port 1434 for SQL Browser service


    Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!


    Wednesday, July 18, 2012 7:14 PM
  • I tried that as well and am still getting a login timeout error.
    Wednesday, July 18, 2012 7:47 PM
  • Time for How to Troubleshoot Connecting to the SQL Server Database Engine

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Wednesday, July 18, 2012 8:03 PM
  • Check for some more things :-

    1. CPU pressure on SQL Server
    2. Got to Start Menu >RUN> cliconfg.exe > check any alias is there for your SQL server
    3. Try to connect like <IP Address>\Instancename


    Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!


    Wednesday, July 18, 2012 8:06 PM
  • Hi Emmanuel,

    Base on your error message, the reason that you get it might is the client stack could not receive SQL Server Resolution Protocol (SSRP) response UDP packet from SQL Server Browser. You can isolate the issue by the following steps:
    1) Make sure your server name is correct, e.g., no typo on the name.
    2) Make sure your instance name is correct and there is actually such an instance on your target machine.
    3) Make sure the server machine is reachable, e.g., DNS can be resolve correctly, you are able to ping the server (not always true).
    4) Make sure SQL Server Browser is running on the server (you have confirmed it).
    5) If firewall is enabled on the server, you need to put sqlbrowser.exe and/or UDP port 1434 into exception (please pay attention to this step).
    For more details, please refer to this blog.


    Best Regards,
    Ray Chen

    Tuesday, July 24, 2012 8:43 AM