Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to Change SQL Server Port Number for a Linux Server


By:   |   Read Comments   |   Related Tips: More > 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.

connect to Linux Server via PuTTY

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.

Connect to SQL Server as a super user

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.

netstat command to check all connections running on server with their port number

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.

Verify that SQL Server is running on 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.

set TCPPORT to 1435

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.

restart mssql-server

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.

Run netstat to see the port change

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.

making a database connection using port no 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.

connect using 1433 post change
Next Steps


Last Update:






About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

View all my tips
Related Resources





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools