XSW: Enabling SQL Remote Connections

XSW: Enabling SQL Remote Connections

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

1. Open MS/SQL Management Studio and right-click server name in the left pane and select Properties
2. Select Connections in the left pane and make sure that checkbox Allow remote connections to this server is selected.
Configure SQL express server to listen on static port.
1. Open MS/SQL Server Configuration Manager and click on ?SQL Server Services? in the left pane.

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 MS/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?.  syntax is the following: netstat -ano | find /i ?116?. 

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

4. Restart MS/SQL Server service  and identify the process ID assigned to MS/SQL service.
 
5. Start the Command Prompt app and execute command: netstat -ano | find /i ?6524?

6.  In MS/SQL Server Configuration Manager and click on MS/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.

7. Restart MS/SQL Server service, identify new process ID assigned to MS/SQL service and in the command prompt execute command: netstat -ano | find /i ?3948?. 

At this stage MS/SQL Express is configured to listen on standard port 1433.
Turn on the SQL Server Browser service.
1. Open MS/SQL Server Configuration Manager and click on ?SQL Server Services? in the left pane, right-click SQL Server Browser service and select Properties.

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

3. Click Start button to start MS/SQL Browser service

4. Confirm that SQL Server Browser service is up and running. 

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 MS/SQL Server and to the MS/SQL Server Browser service.
Four exceptions must be configured in Windows Firewall to allow access to MS/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.<INSTANCE_NAME>\MSSQL\Binn\sqlservr.exe] where <INSTANCE_NAME> 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
 
Reference: https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-configuration-manager
MS/SQL Server Configuration Manager
SQL Server Configuration Manager is a tool to manage the services associated with SQL Server, to configure the network protocols used by SQL Server, and to manage the network connectivity configuration from SQL Server client computers. SQL Server Configuration Manager is a Microsoft Management Console snap-in that is available from the Start menu, or can be added to any other Microsoft Management Console display. Microsoft Management Console (mmc.exe) uses the SQLServerManager<version>.msc file (such as SQLServerManager13.msc for SQL Server 2016) to open Configuration Manager. Here are the paths to the last four versions when Windows in installed on the C drive.
  
SQL Server 2017C:\Windows\SysWOW64\SQLServerManager14.msc
SQL Server 2016C:\Windows\SysWOW64\SQLServerManager13.msc
SQL Server 2014C:\Windows\SysWOW64\SQLServerManager12.msc
SQL Server 2012C:\Windows\SysWOW64\SQLServerManager11.msc
 
 
Because SQL Server Configuration Manager is a snap-in for the Microsoft Management Console program and not a stand-alone program, SQL Server Configuration Manager does not appear as an application in newer versions of Windows.
  • Windows 10:
    To open SQL Server Configuration Manager, on the Start Page, type SQLServerManager13.msc (for SQL Server 2016). For previous versions of SQL Server replace 13 with a smaller number. Clicking SQLServerManager13.msc opens the Configuration Manager. To pin the Configuration Manager to the Start Page or Task Bar, right-click SQLServerManager13.msc, and then click Open file location. In the Windows File Explorer, right-click SQLServerManager13.msc, and then click Pin to Start or Pin to taskbar.
    • Windows 8:
      To open SQL Server Configuration Manager, in the Search charm, under Apps, type SQLServerManager<version>.msc such as SQLServerManager13.msc, and then press Enter.
If this article did not provide a solution, please open a case with our support team by sending an email message to support@xypro.com.