Changing the SQL Server 2019 port on Linux and using SQL Server aliases on the client hosts

Logo

Introduction

When SQL Server is installed in a WAN/DMZ area and running on its default port (1433) with firewall rules opened on this port for incoming connections, quickly strange errors appear in the SQL Server error log file :

errorlog
2019-06-01 11:03:58.18 Logon       Login failed for user 'usera'. Reason: Could not find a login matching the name provided. [CLIENT: 61.173.166.212]
2019-06-01 12:48:31.32 Logon       Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: 185.234.219.85]
2019-06-01 12:48:34.47 Logon       Login failed for user 'Mssqla'. Reason: Could not find a login matching the name provided. [CLIENT: 185.234.219.85]
2019-06-01 12:48:41.08 Logon       Login failed for user 'dbhelp'. Reason: Could not find a login matching the name provided. [CLIENT: 185.234.219.85]

Checking IP with tracert command let’s you travel all over the world, especially China.

C:\> tracert 185.234.219.85

 ...
  7    13 ms    17 ms    15 ms  chinatelecom-1.gw.opentransit.net [81.52.179.174]
 ...
 13    34 ms    35 ms    42 ms  ZMRBK-569C1D561 [185.234.219.85]

This is a "normal" behaviour, robots know well the port 1433 in their algorithms. Let’s see how to change the SQL Server 2019 port on Linux Ubuntu with the utility mssql-conf and the consequences for the clients. In this article the port is modified from 1433 to 35027. A non typical port should be chosen.

Architecture Linux SQL Server 2019

Before changing the server port too quickly, think of the impacted client machines, treatments will fail without any action.

The (automated) deployment of SQL Server aliases on client hosts when changing the server port drastically reduces negative impacts. SQL Server aliases are discussed at the end of this article.

Changing the port with mssql-conf

Run the utility mssql-conf to change the port of SQL Server :

mssql@vps$ /opt/mssql/bin/mssql-conf set network.tcpport 35027

Obviously, this parameter is static, SQL Server service must be restarted :

mssql@vps$ systemctl restart mssql-server

Create a firewall rule to open the new port 35027 of SQL Server. For the integrated Ubuntu firewall (ufw - Uncomplicated Firewall), as root :

root@vps$ ufw allow 35027/tcp comment 'Microsoft SQL Server'
          
root@vps$ ufw reload
          
root@vps$ ufw status numbered
          
Status: active

     To                Action      From
     --                ------      ----
...
[ 2] 1433              ALLOW IN    Anywhere
[ 3] 35027/tcp         ALLOW IN    Anywhere          # Microsoft SQL Server
...
[ 5] 1433 (v6)         ALLOW IN    Anywhere (v6)
[ 6] 35027/tcp (v6)    ALLOW IN    Anywhere (v6)     # Microsoft SQL Server

Remove old firewall rules for the port 1433 :

root@vps$ ufw delete 5
root@vps$ ufw delete 2

To check everything is OK, run sqlcmd with the option -S "server or ip,port" from the server and from a remote computer :

mssql@vps$ sqlcmd -Usa -S "localhost,35027"
C:\> sqlcmd -Usa -S "vps,35027"

About the utility bcp, same syntax :

C:\> bcp master..sysobjects out sysobjects.bcpc -Usa -S "vps,35027"

For SQL Server Management Studio 2018, idem, add the port after the name or the IP of the server in the field Server name.

SQL Server Management Studio Connexion

Simplifying client connections with SQL Server aliases

Great, the port is modified and SQL Server engine is more protected from attacks and robots, but obviously the new port won’t be hard coded in all client programs (sqlcmd, bcp, ADO.NET…). If the port is modified, every client program is impacted.

Unfortunately, port numbers cannot be specified in DNS aliases or host file entries. SQL Server aliases will solve the issue.

As a prerequisite, SQL Server native client is installed on all client machines. Instead of using the nomenclature <hostname,port>, all clients will use henceforth the SQL Server alias vps pointing to the server vps, port 35027. By default a SQL Server alias existence is first checked by the SQL Server client, so the alias name can be the same than the host name : this will drastically reduce regressions on clients.

The alias can be created in 2 ways :

  • With the graphical interface SQL Server Configuration Manager.
  • With scripts modifying the windows registry.

SQL Server Configuration Manager

Open Microsoft Management Console (mmc.exe). If the snap-in SQL Server Configuration Manager is not displayed, in the menu, select File Add/Remove Snap-in…, then add SQL Server Configuration Manager :

Add SQL Server Configuration Manager in MMC

To create the alias, right click on Aliases in the menu SQL Native Client 11.0 Configuration 32 bitsNew Alias…

SQL Server Configuration Manager MMC - New Alias

Set the alias name, server name and port.

SQL Server Configuration Manager MMC - Alias properties

Repeat the previous steps now for the 64 bits version : SQL Native Client 11.0 ConfigurationNew Alias….

That’s all, the client now can use the alias (sqlcmd, SSMS…)

C:\> sqlcmd -Usa -Svps 

Scripts

All actions done previously through the graphical interface are written in the windows registry in the following keys :

32 bits : HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\MSSQLServer\Client\ConnectTo

64 bits : HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

Windows registry Alias SQL Server

Obviously, a script (powershell or regedit) can handle the alias creation in the windows registry, for example in PowerShell :

New-Item -Path "HKLM:\Software\Microsoft\MSSQLServer\Client\" -Name ConnectTo -Force -ErrorAction Stop | Out-Null
New-Item -Path "HKLM:\Software\Wow6432Node\Microsoft\MSSQLServer\Client\" -Name ConnectTo -Force -ErrorAction Stop  | Out-Null
 
$Alias      = "vps";
$ServerName = "vps";
$dbPort     = "35027";
$TCPAlias   = "DBMSSOCN," + $ServerName + "," + $dbPort

New-ItemProperty -Path "HKLM:\Software\Microsoft\MSSQLServer\Client\ConnectTo\" -Name $Alias -PropertyType String -Value $TCPAlias -ErrorAction Stop | Out-Null
New-ItemProperty -Path "HKLM:\Software\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo\" -Name $Alias -PropertyType String -Value $TCPAlias -ErrorAction Stop | Out-Null

This kind of script could be used in any deployment tool to easily update configurations of the client machines.

Conclusion

SQL Server aliases deployment on client hosts, if possible automated, is a very good solution when you decide to change the SQL Server port to the non default one, modification recommended for a SQL Server based in a WAN/DMZ area, more vulnerable to attacks and robots.