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.
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 35027Obviously, this parameter is static, SQL Server service must be restarted :
mssql@vps$ systemctl restart mssql-serverCreate 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 numberedStatus: 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 2To 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.
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 :
To create the alias, right click on Aliases in the menu SQL Native Client 11.0 Configuration 32 bitsNew Alias…
Set the alias name, server name and port.
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
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-NullThis 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.