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.