By: Manvendra Singh | Comments | Related: > SQL Server on Linux
Problem
I have written multiple tips on SQL Server vNext running on Linux based operating systems. In this tip, I will explain how to change the port number used by a SQL Server instance running on a Linux server.
Solution
Many tasks for configuring SQL Server on Linux are different because the SQL Server Configuration Manager is not available on Linux. Microsoft has provided a configuration script "mssql-conf" for Linux based SQL Server to make such configuration changes. The "mssql-conf" script installs with SQL Server vNext for Red Hat Enterprise Linux, SUSE Linux and Ubuntu and can be found in /opt/mssql/bin/. This utility is very useful to set the parameters like TCP ports, default data file location, default log file location, etc.
This tip is intended to focus on only changing the port number which SQL Server uses to run on Linux. As you many know, the default port number for SQL Server is 1433. Often, DBAs change this for security or other reasons and in this tip we will show how the port number can be changed.
Changing SQL Server Port Number on Linux
Before moving forward, you need to have SQL Server vNext running on Red Hat Linux, SUSE Linux or Ubuntu server. Here are some installation tips.
Step 1: The first step is to connect to your Linux server using PuTTY or through a local login on the server. I connected to my Linux server where SQL Server vNext is running using the PuTTY application as shown in the below picture.
Step 2: Changing the TCP port for SQL Server is an instance level configuration change, so we need to connect using super user privileges. Run the below code to connect using super user.
#use super user account privilege. Enter password once you get prompt. sudo su
Now, we have connected using super user privileges as shown in the below image.
Step 3: As you many know, SQL Server uses port number 1433 as the default, so let's verify this. There are multiple ways to check, but we will use the netstat command to check all connections running on the server with their port numbers.
#Run netstat command to check the SQL Server port no. netstat -tulpn
We can see there are two SQL Server processes running under two different port numbers. Port number 1433 is used by SQL Server as I said above and port number 1434 is used by the SQL Server Browser service.
We can verify the configuration by making a database connection using the port number. Connect to SQL Server using the sqlcmd utility and pass the port number on which it is running. If SQL Server does not establish the connection this means you are passing the wrong port number or the service is not running. Here we will test port number 1435 and then test with port number 1433.
#Run below command to connect to SQL Server using port no 1435. Enter the sa password whenever you get prompted. sqlcmd -S192.168.209.128,1435 -Usa #Run below command to connect to SQL Server using port no 1433. Enter the sa password whenever you get prompted. sqlcmd -S192.168.209.128,1433 -Usa
Here also it is verified that SQL Server is running on port number 1433. Now our task is to change this port number from 1433 to 1435, then again we will do same steps to verify the changes and that time SQL Server will be connected using port number 1435 and will throw error for port number 1433.
Step 4: Now we will use the configuration script "mssql-conf" to set the new port number to 1435 for SQL Server. Let's execute the mssql-conf utility as shown below.
#execute below command to set the new port no. Replace 1435 with your port no. /opt/mssql/bin/mssql-conf set tcpport 1435
Once you execute the above command, it will set the default port number to the new port number 1435. We can see the command has executed and says it is applying the value 1435 to the tcpport as shown in the below image. This change also requires a restart of the SQL Server service to apply this setting as stated in the below image.
Step 5: As per the above image, now run the below command to restart the SQL Server service.
#Restart SQL Server Service systemctl restart mssql-server #Check SQL Server Service status systemctl status mssql-server
The SQL Server service has been restarted as we can see in the below image that the service is running.
Step 6: Now we will repeat Step 3 to verify the change. Go ahead and run the below netstat command to check the port SQL Server is now using.
#Run netstat command to check the SQL Server port no. netstat -tulpn
We can see port number 1435 is used by SQL Server in the below image.
Now we will connect to SQL Server using port number 1435 to validate the above change. Run the sqlcmd utility to make a database connection.
#Connect to SQL Server. Enter sa password once you get password prompt. sqlcmd -S192.168.209.128,1435 -Usa
You have established a successful database connection using port number 1435.
To check to make sure you cannot connect to SQL Server using port number 1433, we can run the connection test again for port 1433. In the below image we can see that this failed.
Next Steps
- The configuration script "mssql-conf" is very useful and you should explore this utility.
- Learn more about SQL Server vNext version and stay tuned for more tips on this topic.
- Explore more knowledge on SQL Server Database Administration Tips.
About the author
Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
