locked
Server 2008 R2 Express accepts only local connections? RRS feed

  • Question

  • My question SQL Server 2008 R2 Express accepts only local connections? [closed] was closed unanswered and my account (with the same username/nick vgv8) was banned in 1 min without any warning and notification from all StackExchange sites (StackOverFlow.com, ServerFault.com, SuperUser.com).

    I could not understand can MS SQL Server 2008 R2 Express  accept remote connections?

    And what are local connections - in the same subnetwork, AD, workgroup, the same machine? 

    Sunday, August 29, 2010 5:49 AM

Answers

  • Hi vgv8,

     

    Based on my research, this issue happens due to many reasons, such as TCP port 1433, named pipes, firewall, permission. Please note that networking protocols are disabled by default in SQL Server Express.

     

    To enable SQL Server Express to accept remote connections, we need to perform the following steps:

        1. Make sure SQL Server is up and the instance you try to connect is running.

        2. Make sure that the SQL Server Browser service is started on the server.

        3. Enable Named Pipes and TCP/IP protocol, make sure that SQL Server port is by default 1433 and then restart the SQL Server service to take effect. By the way, please don't enable VIA protocol.

        4. Make sure the server machine is reachable, e.g, DNS can be resolve correctly, you are able to ping the server.

        5. Make sure TCP port 1433 and UDP port 1434 are not blocked by Windows firewall. If firewall is enabled on the server, you need to put sqlbrowser.exe and/or UDP port 1434 into exception.

        6. Enable Mixed Mode(SQL Server authentication and Windows authentication).

        7. Make sure the login has sufficient rights to open its default database. Maybe its default database was removed or offline.

        8. Make sure your server name and instance name are correct and there is actually such an instance on your target machine, such as <computername>\<instancename>.

        9. Make sure the password you typed is correctly and the username exists on the remote SQL Server.

     

    For more information, please visit the following links:

    How to configure SQL Server 2005 to allow remote connections: http://support.microsoft.com/kb/914277/en-us

    How to: Configure Express to accept remote connections: http://blogs.msdn.com/b/sqlexpress/archive/2005/05/05/415084.aspx

     

    If you have any more questions, please feel free to let me know.

     

    Thanks,

    Yoyo Yu

    • Marked as answer by YoYo Yu Friday, September 3, 2010 10:16 AM
    Monday, August 30, 2010 9:16 AM

All replies

  • Yes. Local connection is from the workstation/server that SQL Express is installed on. For remote connections, ensure that TCP/IP is enabled for the SQL Express instance.
    Monday, August 30, 2010 8:45 AM
  • Hi vgv8,

     

    Based on my research, this issue happens due to many reasons, such as TCP port 1433, named pipes, firewall, permission. Please note that networking protocols are disabled by default in SQL Server Express.

     

    To enable SQL Server Express to accept remote connections, we need to perform the following steps:

        1. Make sure SQL Server is up and the instance you try to connect is running.

        2. Make sure that the SQL Server Browser service is started on the server.

        3. Enable Named Pipes and TCP/IP protocol, make sure that SQL Server port is by default 1433 and then restart the SQL Server service to take effect. By the way, please don't enable VIA protocol.

        4. Make sure the server machine is reachable, e.g, DNS can be resolve correctly, you are able to ping the server.

        5. Make sure TCP port 1433 and UDP port 1434 are not blocked by Windows firewall. If firewall is enabled on the server, you need to put sqlbrowser.exe and/or UDP port 1434 into exception.

        6. Enable Mixed Mode(SQL Server authentication and Windows authentication).

        7. Make sure the login has sufficient rights to open its default database. Maybe its default database was removed or offline.

        8. Make sure your server name and instance name are correct and there is actually such an instance on your target machine, such as <computername>\<instancename>.

        9. Make sure the password you typed is correctly and the username exists on the remote SQL Server.

     

    For more information, please visit the following links:

    How to configure SQL Server 2005 to allow remote connections: http://support.microsoft.com/kb/914277/en-us

    How to: Configure Express to accept remote connections: http://blogs.msdn.com/b/sqlexpress/archive/2005/05/05/415084.aspx

     

    If you have any more questions, please feel free to let me know.

     

    Thanks,

    Yoyo Yu

    • Marked as answer by YoYo Yu Friday, September 3, 2010 10:16 AM
    Monday, August 30, 2010 9:16 AM