Modify MSSQL port








Are you trying to change the MSSQL port?

This guide is for you.


TCP port 1433 is the default port for SQL Server. This SQL port is also the official Internet Assigned Number Authority (IANA) socket number for SQL Server.

However, some organizations specify to change the SQL Server port number to enhance security.

No port is inherently secure - it's made secure by your restrictions on access to it, through configuration of your network. 

SQL Server can use Transport Layer Security (TLS) to encrypt data that is transmitted across a network between an instance of SQL Server and a client application.


Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to perform SQL related queries.

In this context, we shall look into how to change the MSSQL port using SQL Server Configuration Manager.


How to modify MSSQL port ?

To assign a TCP/IP port number to the SQL Server Database Engine, follow the process below:


i. In SQL Server Configuration Manager, in the console panel, expand SQL Server Network Configuration, expand Protocols for <instance name> and then double-click TCP/IP.

ii. In the TCP/IP Properties dialog box, on the IP Addresses tab, several IP addresses appear in the format IP1, IP2, up to IPAll.

One of these is for the IP address of the loopback adapter, 127.0.0.1. Additional IP addresses appear for each IP Address on the computer.

Right-click each address, and click Properties to identify the IP address that we want to configure.

iii. If the TCP Dynamic Ports dialog box contains 0, indicating the Database Engine is listening on dynamic ports, delete the 0.

iv. In the IPn Properties area box, in the TCP Port box, we type the port number we want this IP address to listen on, and then click OK.

If the Listen All setting on the Protocol tab is set to “Yes”, then it will only use TCP Port and TCP Dynamic Port values under the IPAll section and ignore individual IPn sections entirety.

If the Listen All setting is set to “No”, then it will ignore the TCP Port and TCP Dynamic Port settings under the IPAll section and use the TCP Port, TCP Dynamic Port, and Enabled settings on the individual IPn sections.

Each IPn section has an Enabled setting with a default value of “No” which causes SQL Server to ignore this IP address even if it has a port defined.

v. In the console panel, click SQL Server Services.

vi. In the details panel, right-click SQL Server (<instance name>) and then click Restart, to stop and restart SQL Server.

Make sure the port is open in the Windows firewall.


In order to open a port, follow the steps given below:

1. From the Start menu >> Control Panel >> System and Security >> Windows Firewall.

2. Then click Advanced Settings >> Inbound Rules >> New Rule in the Actions window >> Rule Type of Port >> Next.

3. On the Protocol and Ports page click TCP.

4. Select Specific Local Ports and type the port number.

5. Click Next.

6. On the Action page, click Allow the connection.

7. Click Next.

8. On the Profile page, click the appropriate options for our environment.

9. Click Next.

10. On the Name page, enter a name.

11. Click Finish.

12. Restart the computer.


How to connect to a specific port ?

Once we configure SQL Server to listen on a specific port, there are three ways to connect to a specific port with a client application:

1. Run the SQL Server Browser service on the server to connect to the Database Engine instance by name.

2. Create an alias on the client, specifying the port number.

3. Program the client to connect using a custom connection string.


[Couldn't change MSSQL port? We'd be happy to assist you. ]



Conclusion

This article will guide you on the right steps to change the #MSSQL port using #SQL Server Configuration Manager. 

You can check TCP/IP connectivity to SQL Server by using telnet. For example, at the command prompt, type telnet 192.168. 0.0 1433 where 192.168. 0.0 is the address of the #computer that is running SQL Server and 1433 is the port it is listening on.

To specify a port in SQL Server Management Studio (#SSMS)?

1. You'll need the SQL Server Configuration Manager. 

2. Go to Sql Native Client Configuration, Select Client Protocols.

3. Right Click on #TCP / #IP and set your default port there.


For Linux Tutorials

We create Linux HowTos and Tutorials for Sys Admins. Visit us on LinuxAPT.com

Also for Tech related tips, Visit forum.outsourcepath.com or General Technical tips on www.outsourcepath.com