Sunday, November 27, 2016

Changing the SQL Server port on Linux


Today, I decided to see how I can change the port SQL Server is listening on



To see what port SQL Server is listening on, you can use the netstat command, here is what the output looks like when I run netstat -tulpn | grep sqlservr


I used grep to limit the output to lines that contain sqlservr only

Next, to change the port that SQL Server is listening on to 3000, I ran the following command

sudo /opt/mssql/bin/mssql-conf set tcpport 3000


I restarted SQL Server bu running the command systemctl restart mssql-server
Then I ran  netstat -tulpn | grep sqlservr again and as you can see the output now has 3000 as the port number

Now I tried using sqlcmd, I ran the following sqlcmd -S localhost -U SA


I got an timeout error

Finally I ran the sqlcmd command again but I added port number 3000, like this
sqlcmd -S localhost,3000 -U SA


As you can see, this worked and I could connect to SQL Server listening on port 3000 when supplying that to the sqlcmd command


How do we reset SQL Server back to the default port?
To reset SQL Server back to use the default port, run the command below

/opt/mssql/bin/mssql-conf unset tcpport

Here is a screenshot of running netstat, resetting the port, restarting SQL Server and running netstat again





You want to learn a little more?


We used tulpn as options in our netstat command. So what do all these options do?

[-t] 
tcp

[-u] 
udp

[-l]
Show only listening sockets

[-p]
Show the PID (process identifier) and name of the program to which each socket belongs.

[-n] 
Show numerical addresses instead of trying to determine symbolic host, port or user names.



On a Linux system to get help for a command, use man, this will bring up the user manual for that command

So if you run man netstat, you will see the following (I only pasted 1 page here, there are more)

man netstat

--------------------------

NETSTAT(8)                 Linux Programmer's Manual                NETSTAT(8)

NAME
       netstat  - Print network connections, routing tables, interface statis‐
       tics, masquerade connections, and multicast memberships

SYNOPSIS
       netstat  [address_family_options]  [--tcp|-t]   [--udp|-u]   [--raw|-w]
       [--listening|-l]     [--all|-a]     [--numeric|-n]    [--numeric-hosts]
       [--numeric-ports]           [--numeric-users]           [--symbolic|-N]
       [--extend|-e[--extend|-e]]  [--timers|-o] [--program|-p] [--verbose|-v]
       [--continuous|-c]

       netstat              {--route|-r}              [address_family_options]
       [--extend|-e[--extend|-e]]         [--verbose|-v]        [--numeric|-n]
       [--numeric-hosts] [--numeric-ports] [--numeric-users] [--continuous|-c]

       netstat {--interfaces|-i} [--all|-a] [--extend|-e[--extend|-e]] [--ver‐
       bose|-v]  [--program|-p]  [--numeric|-n]  [--numeric-hosts] [--numeric-
       ports] [--numeric-users] [--continuous|-c]

       netstat      {--groups|-g}       [--numeric|-n]       [--numeric-hosts]
       [--numeric-ports] [--numeric-users] [--continuous|-c]
 Manual page netstat(8) line 1/348 15% (press h for help or q to quit)


To see all my SQL Server on Linux posts, click here: SQL Server on Linux

No comments: