Enable Remote Connection on SQL Server 2012 Express

I wrote articles about how to enable remote connection on SQL Server 2008 and SQL Server 2005 many years ago. However, I want to keep its content more up-to-date so I decide to write it again for SQL Server 2012 Express which shows step-by-step guide how to allow remote connection to SQL Server 2012 Express with additional information how to configure Windows Firewall for SQL Server Express on Windows 7.

On default installation of SQL Server 2012 Express, remote connection is disabled. When you’re trying to connect to remote SQL Server 2012 Express using SQL Server Management Studio, you will receive the error message similar as below:

Cannot connect to SQL01\SQLEXPRESS
Additional information: 
 -> A network related or instance-specific error occurred while establishing a connection to SQL Server.
    The server was not found or was not accessible. Verify that the instance name is correct and that
    SQL Server is configured to allow remote connections. (provider: SQL Network interfaces, error: 26 - 
    Error Locating Server/Instance Specified) (Microsoft SQL Server)
Error: 26 - Error Locating Server/Instance Specified

Or you may receive this error message:

Cannot connect to SQL01\SQLEXPRESS
Additional information: 
 -> A network related or instance-specific error occurred while establishing a connection to SQL Server.
    The server was not found or was not accessible. Verify that the instance name is correct and that
    SQL Server is configured to allow remote connections. (provider: SQL Network interfaces, error: 28 - 
    Server doesn't support requested protocol) (Microsoft SQL Server)
Error: 28 - Server does not support requested protocol

If you receive this error message as below, it means that you already have connection to the SQL Server. However, the authentication for the user is failed. You should check that you have enabled SQL Server authentication mode (for the ‘sa’a account) and the account is not disabled.

Cannot connect to SQL01\SQLEXPRESS
Additional information: 
 -> Login failed for user 'sa'. (Microsoft SQL Server, Error: 18456)
Error: 18456 - Login failed for user "sa"

Section

To fix “Error: 26 – Error Locating Server/Instance Specified” and “Error: 28 – Server does not support requested protocol“, follows step-by-step guide here:

  1. Verify that you have spelled server and instance name correctly.
  2. Configure SQL Server Browser Service.
    SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer.
  3. Enable TCP/IP Protocol for SQL Server Express to accept remote connection.
  4. Configure Windows Firewall to allow SQL Server Remote Connection

Step-by-step to enable remote connection on SQL Server 2012 Express

Configure SQL Server Browser Service

  1. Open SQL Server Configuration Manager by click Start -> All Programs -> Configuration Tools -> Microsoft SQL Server 2012 -> SQL Server Configuration Manager.
    Open SQL Server Configuration Manager
  2. On SQL Server Configuration Manager, select SQL Server Services -> right-click on SQL Server Browser and select Properties.
    Open Properties of SQL Server Browser
  3. On SQL Server Browser Properties, click Service tab and change Start Mode to Automatic and click Apply.
    Set Start Mode to Automatic
  4. On SQL Server Browser Properties, switch back to Log On tab and leave the option Built-in account to Local Service and click Start to start the service now. Then, click OK to close the Properties.
    Note: You can use other account for SQL Server Browser service. For more information about account privilege needed, see SQL Server Browser Service. For more information about service accounts, see Service Account (SQL Server Express).
    Configure account run SQL Server Browser and Start the Service

Back to section

Enable TCP/IP Protocol for SQL Server Express

  1. Back to SQL Server Configuration Manager, expand SQL Server Network Configuration and select Protocols for SQLEXPRESS. Next, right-click on TCP/IP and select Enable.
    Enable Remote Connection on SQL Server 2012 Express - Enable TCP/IP Protocol for SQLEXPRESS Instance
  2. In order to take effect of enabling TCP/IP, we have to restart the SQL Server. Select SQL Server Services and right-click on SQL Server (SQLEXPRESS) and select Restart.
    Restart SQL Server Express

Back to section

Configure Windows Firewall to allow SQL Server Remote Connection

  1. Before we can configure Windows Firewall, we need to note down the executable paths of SQL Server and SQL Server Browser first. On SQL Server Configuration Manager, right-click SQL Server (SQLEXPRESS) and select Properties.
    Open SQL Server Express Properties
  2. On Properties, click on Service tab. Remember or note down this “binary path” for later use. On default installation, it is “C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\Binn\sqlservr.exe“.
    SQL Server Express Binary Path
  3. Repeat the above step for SQL Server Browser. Note down its binary path for later use. On default installation, it is “C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe“.
    SQL Server Browser Binary Path
  4. Open Windows Firewall by Start -> Control Panel -> Network and Internet Network and Sharing Center -> Windows Firewall. Then, click on Allow a program or feature through Windows Firewall.
    Open Windows Firewall
  5. On Allowed Programs, click Change Settings and click Allow another program.
    Allow Programs to Communicate through Windows Firewall
  6. On Add a Program, click Browse.
    Add A Program
  7. On Browse, browse to the binary path of SQL Server Express that noted down earlier and click Open.
    Browse to SQL Server Binary Executable
  8. You notice that SQL Server has been added to the program list. Click Add to continue.
    Add SQL Server to Windows Firewall Allowed Lists
  9. SQL Server has been added to Windows Firewall Allowed List. Next, we have to add SQL Server Browser, click Allow another Program.
    Allow Another Program - SQL Server Browser
  10. On Add a Program, click Browse.
    Add A Program - Click Browse
  11. On Browse, browse to the binary path of SQL Server Browser that noted down earlier and click Open.
    Browse to SQL Server Browser Binary Executable
  12. You notice that SQL Server Browser has been added to the program list. Click Add to continue.
    Add SQL Server Browser to Windows Firewall Allowed Lists
  13. Now Both SQL Server and SQL Server Browser are added to Windows Firewall Allowed Lists. You should be able to test remote connection now.
    SQL Server and SQL Server Browser have been added to Windows Firewall Allowed Lists
  14. Test remote connection to SQL Server 2012 Express successfully.
    Successfully Remote Connected to SQL Server 2012 Express

Back to section

3 Responses to “Enable Remote Connection on SQL Server 2012 Express”

Comments are now closed for this article. If you still have issue and would like to discuss, you can send me a message.