Enabling Remoting on SQL Express

Summary

When you try to connect to an instance of Microsoft SQL Server 2012 Express from a remote computer, you might receive an error message such as “Named Pipes Provider, error: 40 – Could not open a connection to SQL Server”

This guide is not my own work and is shadowed from the great resource that is citrix24 blog:
https://blog.citrix24.com/configure-sql-express-to-accept-remote-connections/

Issue

Named SQL instances listen on dynamic ports. This is the function of the Sql Server Browser Service to inform the clients of the actual port. The Sql Browser listens on UDP 1434 and answers all client request with the port number the current instance is using. Sql Server Browser service is required for both TCP and named pipes protocols. SQL Server Browser is used by clients transparently and there is no need for special configuration.

Implementation

Enable remote connections on the instance of SQL Server that you want to connect to from a remote computer.

1. Open SQL Management Studio and right-click server name in the left pane and select Properties

 Figure 1

Figure 1

2. Select Connections in the left pane and make sure that checkbox Allow remote connections to this server is selected as it is shown in Figure 2.

 Figure 2

Figure 2

Configure SQL express server to listen on static port.

1. Open SQL Server Configuration Manager and click on “SQL Server Services” in the left pane.

 Figure 3

Figure 3

2. In the center pane, is a column that lists the Process ID for each running service. Look for the PID in the row for SQL Server. Identify the port that that PID is listening on by typing this into a command prompt:

netstat -ano | find /i “PID-Number-Of-SQL-Server”. Based on the details shown in Figure 3 syntax is the following: netstat -ano | find /i “116”. The results are shown in Figure 4.

 Figure 4

Figure 4

3. There is no results from the command executed in step 3 because TCP/IP protocol is disabled and must be enabled. In SQL Server Configuration Manager and click on SQL Server Network Configuration in the left pane and right-click TCP/IP protocol and select option Enable.

 Figure 6

Figure 5

4. Restart SQL Server service  and identify the process ID assigned to SQL service.

 Figure 6

Figure 6

5. In the command prompt execute command: netstat -ano | find /i “6524”. The results are shown in Figure 7.

 Figure 7

Figure 7

6.  In SQL Server Configuration Manager and click on SQL Server Network Configuration in the left pane and right-click TCP/IP protocol and select option Properties. Goto IP Address tab and scroll-down to APAll section. Remove value for TCP Dynamic Ports (do not enter Zero 0 !!!) and enter the port 1433 for TCP Port.

 Figure 7

Figure 8

7. Restart SQL Server service, identify new process ID assigned to SQL service and in the command prompt execute command: netstat -ano | find /i “3948”. The results are shown in Figure 9.

 Figure 9

Figure 9

At this stage SQL Express is configured to listen on standard port 1433.

Turn on the SQL Server Browser service.

1. Open SQL Server Configuration Manager and click on “SQL Server Services” in the left pane, right-click SQL Server Browser service and select Properties.

 Figure 10

Figure 10

2. Go to Service tab and for Start Mode option change start type to Automatic.

 Figure 11

Figure 11

3. Click Start button to start SQL Browser service

 Figure 12

Figure 12

4. Confirm that SQL Server Browser service is up and running as it is shown in Figure 13.

 Figure 13

Figure 13

Important note: According to SQL server hardening best practices the SQL Server Browser service should be disabled. This service, which typically isn’t required, responds to requests for SQL Server resources and redirects the caller to the correct port. Keeping the Browser service disabled will remove the redirector as an attack vector, helping to obscure the correct entry ways into your SQL Server components.

Configure the firewall to allow network traffic that is related to SQL Server and to the SQL Server Browser service.

Four exceptions must be configured in Windows Firewall to allow access to SQL Server:

  1. A port exception for TCP Port 1433. In the New Inbound Rule Wizard dialog, use the following information to create a port exception:
    • Select Port
    • Select TCP and specify port 1433
    • Allow the connection
    • Choose all three profiles (Domain, Private & Public)
    • Name the rule “SQL – TCP 1433”
  2. A port exception for UDP Port 1434. Click New Rule again and use the following information to create another port exception:
    • Select Port
    • Select UDP and specify port 1434
    • Allow the connection
    • Choose all three profiles (Domain, Private & Public)
    • Name the rule “SQL – UDP 1434
  3. A program exception for sqlservr.exe. Click New Rule again and use the following information to create a program exception:
    • Select Program
    • Click Browse to select ‘sqlservr.exe’ at this location:

[C:\Program Files\Microsoft SQL Server\MSSQL11.\MSSQL\Binn\sqlservr.exe] where is the name of your SQL instance.

  • Allow the connection
  • Choose all three profiles (Domain, Private & Public)
  • Name the rule SQL – sqlservr.exe
  • A program exception for sqlbrowser.exe Click New Rule again and use the following information to create another program exception:
    • Select Program
    • Click Browse to select sqlbrowser.exe at this location: [C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe].
    • Allow the connection
    • Choose all three profiles (Domain, Private & Public)
    • Name the rule SQL – sqlbrowser.exe

    Source: https://blog.citrix24.com/configure-sql-express-to-accept-remote-connections/

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s