locked
How to enable remote connections in SQL Server 2008 RRS feed

  • Question

  • I'm trying to enable remote connections in SQL Server 2008 R2 as described in the following article.  I see the error message at the top of the article. 

    http://blogs.msdn.com/b/walzenbach/archive/2010/04/14/how-to-enable-remote-connections-in-sql-server-2008.aspx

    One step in the article describes how to enable TCP/IP in Configuration Manager.  Specifically it says that 'SQL Server NETWORK Configuration' should have TCP/IP enabled.

    The issue I've encountered is that my Configuration Manager is different than what appears in the article. My Configuration Manager shows the following:

    • SQL Server 2005 Services
    • SQL Server 2005 Network Configuration (32 bit)

           - Protocols for SQLExpress

    • SQL Native Client Configuration (32 bit)

           - Client protocols

           - Aliases

    Note that I see 'SQL Server 2005 Network Configuration' rather than that for 2008.  When 'Protocols for SQLExpress' is expanded, there is a TCP/IP setting, but it's disabled and I see an Access Denied message if I try to enable this. 

    I also see a TCP/IP setting under Client protocols and successfully enabled this.  This uses port 1433 and I configured my firewall to accomodate this port as suggested in the article. 

    I also followed the other steps in the article, but still don't have remote access to SQL Server 2008 R2.  (I can access it from the office.)  I'm wondering if this remote access issue is because SQL Server 2008 Network Configuration does not appear in Configuration Manager and if so, I'm interested in how this might be resolved.  (I'm running Windows 7 - 64 bit.)

    I'd appreciate any ideas on this.  Thanks. 

    Sunday, February 22, 2015 6:45 PM

Answers

All replies

  • Hi,

    You need to enable the TCP/IP protocol for  'Protocols for SQLExpress', Try running Configuration Manager as Administrator. Highlight the instance for which you want to configure the TCP/IP setting as shown below. Also try running the latest version of SQL Server Configuration Manager.

    Once this is enabled you have to create rules @ windows firewall(If your firewall is enabled) for remote connection to be established. The link below will help you do that

    http://www.mssqltips.com/sqlservertip/1961/configure-windows-firewall-for-sql-server-2008-database-engine-in-windows-server-2008-r2/.

    Hope this helps

    Thanks

    Bhanu


    • Edited by bhanu_nz Sunday, February 22, 2015 8:41 PM
    • Proposed as answer by Michelle Li Monday, February 23, 2015 2:23 AM
    • Marked as answer by Bontrager Friday, February 27, 2015 9:38 PM
    Sunday, February 22, 2015 8:36 PM
  • Hello,

    Please refer to the following resource.

    http://support.microsoft.com/kb/KbView/914277



    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com

    • Proposed as answer by Michelle Li Monday, February 23, 2015 2:23 AM
    Sunday, February 22, 2015 8:42 PM
  • Hi Bhanu,

    I was finally able get a hold of someone with an admin account.  (It's a company owned laptop.)  With this account, I enabled TCP/IP as you suggested.  I still cannot access SQL Server remotely though because the TCP/IP I just enabled uses 'TCP Dynamic Ports' (not the default port 1433).  This presents an issue because the Firewall needs to be updated (through Control Panel) to allow the port that TCP/IP uses.  I'm looking into this.  If you have any ideas, please let me know.

    Thanks.

      

    Friday, February 27, 2015 1:30 AM
  • Hi,

    Is your SQL Server a Default instance?

    If so plz follow the below instructions

    Having TCP/IP and Firewall rules in place you should not have any problem remote on to SQL server. By Default TCP/IP port is 1433 for SQL Server. You can check this using SQL Server Configuration Manager @SQL Server Network Configuration>Protocols for MSSQLSERVER.

     Also make sure that the below  @ SQL Server Properties is enabled

    If Your SQL Server is a named instance, Create a new TCP Port Firewall rule that allows TCP/IP port number assigned to your SQL Named Instance & enable SQL Browser Service which can be found @SQL Server Configuration Manager. You may also have to create a UDP port firewall rule for port no 1434 

    Hope this helps

    Thanks

    Bhanu






    • Edited by bhanu_nz Friday, February 27, 2015 9:26 PM
    Friday, February 27, 2015 1:49 AM
  • The server has already been configured to allow Remote connections. 

    As the issue here is no how to enable TCP/IP but how to configure the Firewall, I've opened a separate thread:

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c23271a5-b993-48b4-ab6c-fcd176a8e36e/is-sqlcmdexe-the-sql-server-2008-executable-to-be-added-to-a-firewall-inbound-rule-to-allow?forum=sqlsetupandupgrade

    Thanks.

    Friday, February 27, 2015 9:37 PM