Configuring a Custom Port for MS SQL Remote Access: A Detailed Guide

Introduction

Managing a Microsoft SQL Server involves ensuring that the database is accessible remotely. This process often requires setting up custom ports for better control and security. In this comprehensive guide, we will walk you through the steps to configure a custom port for MS SQL remote access. This tutorial is designed to be user-friendly and technical enough for advanced users, ensuring a smooth setup process for your SQL Server.

Prerequisites

Before proceeding with the configuration steps, ensure that you have the necessary permissions to make changes to the SQL Server, firewall, and network settings. Having the appropriate tools, such as Microsoft SQL Server Configuration Manager, Windows Firewall with Advanced Security, and access to the router's settings, will also be required.

Step-by-Step Guide to Setting a Custom Port

Using Configuration Manager

The first step in setting up a custom port for MS SQL is to use the Microsoft SQL Server Configuration Manager. This utility offers precise control over SQL Server's network settings.

Launch Configuration Manager: Open the Configuration Manager by searching for it or navigating to the Control Panel and selecting Administrative Tools > SQL Server Configuration Manager. Navigate to Network Configuration: In the left pane, expand the SQLEnablers node and then expand the Protocols for [Instance_Name] Modify IP Addresses: Right-click IPAll and select Properties. In the TCP/IP Properties window, check the Enabled option if it is not already selected. Set the Custom Port: In the IP address section, enter the desired custom port number in the appropriate Listen All or IP[n] fields (replace [n] with the relevant IP address number). Apply and Restart: Click Apply to save the changes, then restart the SQL Server service to ensure they take effect.

Firewall Configurations

After setting the custom port in the Configuration Manager, it's essential to update the firewall rules to allow traffic through the new port.

Access Windows Firewall: Open the Windows Firewall with Advanced Security. Create a New Inbound Rule: In the left pane, select Inbound Rules and click New Rule. Choose Port and click Next. Configure the Port: Specify Port Number as the custom port you configured, select the Protocol (TCP or UDP if applicable), and set the Action to Allow the connection. Assign the New Rule: Name the rule and indicate which user or group it should apply to. Make sure to select Public, Domain, and Private networks. Apply the Changes: Click Finish to create and apply the inbound rule.

Port Forwarding on the Network Router

To ensure remote access from the internet, you need to forward the custom port on your router.

Access Router Settings: Log in to your router's management interface via a web browser. Find Port Forwarding Settings: Search for Port Forwarding or Virtual Servers in the router's settings. This section will vary depending on your router model. Configure Port Forwarding: Enter the custom port number, enter the IP address of the server hosting the SQL instance, and enable forwarding. Save and Apply: Save the changes and ensure that port forwarding is active.

Conclusion

Configuring a custom port for MS SQL remote access is a critical step in securing your database and ensuring its accessibility from remote locations. By following the detailed steps outlined in this guide, you should be able to set up your SQL Server to use a custom port, with appropriate configurations for the firewall and network router to enable public and domain connections.

Keywords

custom port, MS SQL remote access, firewall configuration, network router port forwarding, configuration manager