SQL Server 2012 Express and remote connections

During the installation of a BES10.1.1 proof of concept I noticed my fresh SQL Server 2012 Express instance was not accepting remote connections. After a short investigation I learned that a SQL Server Express installation does not listen for remote connections out of the box.

How to enable remote connections?

1) Enable the TCP/IP protocol for the instance

Start the SQL Server Configuration Manager. On Windows 2012 you probably will not have a shortcut to start the configuration manager. So open a cmd/powershell and start the configuration manager with sqlservermanager11.

Expand SQL Server Network Configuration and select the Protocols item for the particular instance you are configuring. Select TCP/IP, right-click (or choose action from the menu) and select enable.

2) Choose to use dynamic ports or a static port

The default is to use dynamic ports.

If you would like to use a static port (like the familiar 1433 port):

  • Double-click TCP/IP to enter the properties;
  • On the IP Addresses tab scroll to IPAll;
  • Clear the TCP Dynamic Port box;
  • Enter the desired port in the TCP Port box;
  • Commit the changes by pressing OK.

If you want to use dynamic ports you will need the SQL Server Browser service running. This service is disabled by default.

Enabling the service with PowerShell:

set-service -Name SQLBrowser -StartupType Automatic
start-service -Name SQLBrowser 

or with the commandline front-end to the Service Control Manager called sc.exe:

sc.exe config SQLBrowser start=auto
sc.exe start SQLBrowser


3) Adjust the Windows Firewall to allow traffic to SQL Server components

You can disable the firewall alltogether but this is not best practice. You can allow SQL server and browser processes to receive data through the firewall by adding two firewall rules.

First find the path to the SQL server and browser executables. You can double-click their corresponding services in services.msc and take note of the Path to executable.

Another way to find the path to the executables is by using sc.exe. Start by issueing a sc.exe query and grabbing everything with Service and SQL in the output.

> sc.exe query|findstr SERVICE.*SQL
SERVICE_NAME: MSSQL$BES10
SERVICE_NAME: SQLBrowser
SERVICE_NAME: SQLWrite

We are only interested in the SQL server and SQL browser services. In my case the SQL server instance is called BES10. Issue a sc.exe qc for the services you are interested in and take note of the BINARY_PATH_NAME. Mind the single quotes when querying services with special characters like $ in the name.

> sc.exe qc 'MSSQL$BES10'
[SC] QueryServiceConfig SUCCESS

SERVICE_NAME: MSSQL$BES10
        TYPE               : 10  WIN32_OWN_PROCESS
        START_TYPE         : 2   AUTO_START
        ERROR_CONTROL      : 1   NORMAL
        BINARY_PATH_NAME   : "D:\SQL\MSSQL11.BES10\MSSQL\Binn\sqlservr.exe" -sBES10
        LOAD_ORDER_GROUP   :
        TAG                : 0
        DISPLAY_NAME       : SQL Server (BES10)
        DEPENDENCIES       :
        SERVICE_START_NAME : NT Service\MSSQL$BES10
> sc.exe qc 'SQLBrowser'
[SC] QueryServiceConfig SUCCESS

SERVICE_NAME: SQLBrowser
        TYPE               : 10  WIN32_OWN_PROCESS
        START_TYPE         : 2   AUTO_START
        ERROR_CONTROL      : 1   NORMAL
        BINARY_PATH_NAME   : "c:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe"
        LOAD_ORDER_GROUP   :
        TAG                : 0
        DISPLAY_NAME       : SQL Server Browser
        DEPENDENCIES       :
        SERVICE_START_NAME : NT AUTHORITY\LOCALSERVICE

In above example the path to the SQL server executable is “D:\SQL\MSSQL11.BES10\MSSQL\Binn\sqlservr.exe”. However in a more default installation it probably will be “C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\Binn\sqlservr.exe”

The path for the SQL Browser executable will be something like: “c:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe”

Now you can add the firewall rules. You can do this through the Firewall applet in Control Panel by using Allow an app trough windows firewall or you can the two PowerShell commands shown below.

New-NetFirewallRule -DisplayName "SQL Browser" -Direction Inbound -Program "C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe" -Profile Domain -Action Allow
New-NetFirewallRule -DisplayName "SQL Server 2012 Express" -Direction Inbound -Program "D:\SQL\MSSQL11.BES10\MSSQL\Binn\sqlservr.exe" -Profile Domain -Action Allow

In the above example I added the rules to the “Domain” profile. If you want to add the rule to all profiles just leave the profile option out.

4) Restart the SQL Server service

Now you should be good to go.