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)

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)

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)

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.
   
2. On SQL Server Configuration Manager, select SQL Server Services -> right-click on SQL Server Browser and select Properties.
   
3. On SQL Server Browser Properties, click Service tab and change Start Mode to Automatic and click Apply.
   
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).
   

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

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.
   
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“.
   
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“.
   
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.
   
5. On Allowed Programs, click Change Settings and click Allow another program.
   
6. On Add a Program, click Browse.
   
7. On Browse, browse to the binary path of SQL Server Express that noted down earlier and click Open.
   
8. You notice that SQL Server has been added to the program list. Click Add to continue.
   
9. SQL Server has been added to Windows Firewall Allowed List. Next, we have to add SQL Server Browser, click Allow another Program.
   
10. On Add a Program, click Browse.
    
11. On Browse, browse to the binary path of SQL Server Browser that noted down earlier and click Open.
    
12. You notice that SQL Server Browser has been added to the program list. Click Add to continue.
    
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.
    
14. Test remote connection to SQL Server 2012 Express successfully.
    

Back to section