Tuesday, March 28, 2017

SQL Server on Linux. How to change port. Problems and Troubleshooting.

The most of those who use SQL Server don't even know that they are using default port #1433 to access their precious data.
However, to make your data just little bit less vulnerable for external intruder you can change that port within wide range of 65K addresses.

In this blog I will change SQL Server on Linux default port from #1433 to #61433.

To do so, in the Linux box I'll run following command
sudo /opt/mssql/bin/mssql-conf set tcpport 61433
Then I have to restart SQL Server to make changes to take the effect:
sudo systemctl restart mssql-server

Now we will check how it works by running following script:
SELECT local_tcp_port
FROM sys.[dm_exec_connections] 
WHERE [session_id] = @@spid;

Everything looks working. Right?
Will check how can we connect to SQL Server on Linux from outside of the box:

That error can be explained by a Server firewall, blocking our request.
We just have to allow requests on the server to go through our changed port:
(that command is applicable for Linux Ubuntu and might not work in other distributions)
sudo ufw allow 61433

As you can see, immediately after opening the port we can connect to the system from the outside.

Now, will try to perform one basic operation. Will try to read an error log by T-SQL using following:
EXEC sys.xp_readerrorlog

However, we get an execution error:

Via GUI I've got similar error:

When I've tried to read error log via PowerShell and also got error:

I've tried to read errorlog on the server itself, but result was the same and even worse, it brake the connection to the engine with error "Invalid cursor state":

Here is the corresponding message in the Error Log:

All these problems are expected. Prior experience shows that changing SQL Server port makes reading Error Log file impossible.

Besides of inability to read error log all other functions work fine.

No comments:

Post a Comment