To assign a TCP/IP port number to the SQL Server Database Engine follow the steps below:
STEP 1: Open the SQL Server Management Studio and login to your Database locally.
STEP 2: Right-click on the server name in the left panel and select Properties.
STEP 3: Select the Connections page on the left side and make sure that “Allow remote connections to this server” option is checked. Then exit SQL Server Management Studio.
STEP 4: Now Open the SQL Server Configuration Manager and expand the node SQL Server Network Configuration. Then Select Protocols for SQLEXPRESS (or whatever the name of your SQL Server instance is). Make sure that TCP/IP protocol is enabled.
STEP 5: Right click on TCP/IP and choose Properties. In the TCP/IP Properties dialog box, click on the IP Addresses tab and scroll down to IPAII section. Make sure TCP Dynamic Ports is blank and that TCP Port is set to 1433 (or whatever you want to set TCP Port).
STEP 6: First Click on Apply button and then the OK button to save your changes. After that restart your SQL Server instance.
Now you are able to connect to your SQL Server instance remotely using SQL Server Management Studio.
If there is problem persist in remote connection, you most likely need to configure your firewall settings. Make sure the TCP port 1433 is not blocked by your firewall.
Take a look at the following steps to create a new rule in firewall setting for remote connection.
STEP 1: In the Windows Firewall in Control Panel, click on the Advanced Settings option on the left OR Start >> Run >> Type wf.msc and hit Enter.
STEP 2: In the Windows Firewall with Advanced Security window that opens, click on the Inbound Rules in the left panel. Click on the New Rule on the right panel of the window.
STEP 3: Select Port on the list of options and click Next.
STEP 4: Select TCP (or UDP if that is what you require) and Specific local ports, and then enter your ports in the text box in front of Specific local ports (i.e. 1234). Click Next.
STEP 5: Select Allow the connection. Click on Next.
STEP 6: Select the options which you want from Domain, Private, Public. Click on Next.
Domain – This rule will be applicable when the computer is linked to a network domain.
Private – This rule will be applicable when the computer is linked to trusted private networks.
Public – This rule will be applicable when the computer is linked to untrusted public networks.
STEP 7: Finally, Enter the Name and Description in the text boxes respectively. Then click Finish.
After following these steps your port should be allowed through the Windows Firewall.