When I started to work with SQL Server on Azure VMs, I was completely excited because now the need to build local VMs has become super easy. If I need something for testing, then I know I can get a SQL Server instance in a jiffy and it can be brought down as soon as my work is done. The more we start working with softwares on the cloud, it is important to also understand the nuances of security that we need to be aware. I recently noticed the ERRORLOG on my virtual machine sitting in Azure and was surprise to see below error in ERRORLOG multiple times every minute.
Error: 18456, Severity: 14, State: 5.
Login failed for user ‘KISAdmin’. Reason: Could not find a login matching the name provided. [CLIENT: xx.174.233.xx] Error: 18456, Severity: 14, State: 7.
Login failed for user ‘sa’. Reason: An error occurred while evaluating the password. [CLIENT: xx.174.233.xx] Error: 18456, Severity: 14, State: 5.
Login failed for user ‘KHB’. Reason: Could not find a login matching the name provided. [CLIENT: xx.174.233.xx] Error: 18456, Severity: 14, State: 5.
Login failed for user ‘Chred1433’. Reason: Could not find a login matching the name provided. [CLIENT: xx.174.233.xx]
(I have masked the IPs in error messages above)
I realized that
- The IP address listed was not an IP which I know
- Account shown in login failed messages doesn’t exist on my SQL server instance.
Since this is a virtual machine in Microsoft Azure, I checked the endpoints for this VM as they are responsible for any entry to the sever from the outside world. Here is what I saw.
As we can see above that I have created endpoint for SQL Server on port 1433 and it’s a very well-known port used by SQL Server. My theory about hacking became more firm when I attempted to do remote desktop on the IP given in the error message.
Here are my suggestions:
- If Login failed message says <local machine> it is less likely to be hacker, but some process somewhere on the machine.
- Above is also true if the IP address is within your organization.
- If the IP Address is outside your organization, then answer is simple – don’t expose your SQL Server on the Internet. Else you may want to take below steps
- Change SA password and make sure its complex.
- If you don’t need SQL Authentication, don’t use it. Change the mode to Windows only.
In my case, since its and VM in Azure and I have to stay with SQL authentication so I changed the public port to something different and then there were no more Login failed attempts from that machine. When I searched, I also found article from Microsoft as below
Interesting my simple idea of using non-default endpoint of not listed there but they do ask to use ACL for endpoints for better security of SQL running on Azure virtual machine.
Do you check SQL ERRORLOG regularly to monitor the server’s security?
Reference: Pinal Dave (https://blog.sqlauthority.com)