I am a big advocate of using Microsoft Azure VMs for lots of uses—many clients don’t have the wherewithal to manage a full scale data center operation, or in other cases they don’t have the budget for a second data center for disaster recovery. Azure is a great use case for those options, as well as quickly spinning up dev environments for testing new releases or doing a proof of concepts. In fact, I’m currently working on a PoC for a client and we are using Azure IaaS and Power BI for Office 365.
The biggest fear most people have around cloud computing is the security aspect—they don’t trust their data not to be in their data center. In general, I think Microsoft (and Amazon) have way better security than most data centers that I’ve ever set foot in, but whenever you are using a cloud provider, you have to have a good understanding of the nuances of their security model. One thing to note about Microsoft Azure is that all virtual machines get their own public IP address (personally, I feel like this a waste of a limited resource, as VMs that are within virtual networks generally have no need for a public facing IP address, but that’s a different blog post) and security is provided by creating endpoints (by default the SQL Server template opens PowerShell, RDP and 1433 for SQL Server). Access to these endpoints can be controlled by ACL—you can define a list of IP addresses (presumably the other machines in your network) that can talk to your VM over that endpoint. However, by default, your new VM is accessible on port 1433 to the entire internet.
I was troubleshooting connectivity from my SharePoint VM to my SQL Server VM this morning, and I went to the SQL Server log, and I found:
Figure 1 Log of Failed Logins
Those IP addresses aren’t on my virtual network, and they aren’t the public IPs of any of the servers in my network. Let’s use an IP lookup service to see where they are from:
Figure 2 Ip Address #1 Nanjing, China
Figure 3 IP Address #2 Walnut, CA
As Denny Cherry (b|t) mentions in Securing SQL Server having an SA account named SA and enabled is a definite security risk. Since SQL Server accounts won’t get locked out from failed password attempts these hackers know half of the battle, and they are hammering my VM trying to guess the SA password. Chred1433 seems like an interesting name for a user (or a hack attempt at SQL Server) and kisadmin shows up in this list of attacks on SQL Server.
Securing Your VM
So what does this mean for you? If you have VMs in Azure (or in your own data center—this is just general security best practices):
- Never expose port 1433 to the internet. There are some scenarios where you have to, but I try to always work around this
- Always disable your SA account—use domain groups for access to SQL Server
- When launching a SQL Server VM in Microsoft Azure either disable the endpoint on 1433 or use ACLs to limit access to specific machines
- Use Azure Virtual Networks and Gateways to connect securely to Azure Infrastructure—when you have a virtual network, you never have to use the public IP address, and all connections can take place over secure VPN connections
No one wants to have their data breached—so make sure to follow these steps!
— One thing to note about Microsoft Azure is that all virtual machines get their own public IP address (personally, I feel like this a waste of a limited resource, as VMs that are within virtual networks generally have no need for a public facing IP address, but that’s a different blog post) and security is provided by creating endpoints (by default the SQL Server template opens PowerShell, RDP and 1433 for SQL Server).
This is incorrect. Azure VMs are deployed into cloud services. Each cloud service gets a permanent DNS name. Whenever a cloud service contains a deployed VM it will also have a SINGLE public VIP that is shared among all the VMs in the cloud service. This is why you need to configure the Azure Load Balancer to either load balance or port forward endpoints to the VMs in the cloud service. There is a preview feature in which it is possible to also provide a public instance IP address for an individual VM. During the preview this is limited to two public Instance IP addresses per subscription.
Neil–Thanks for the comment, I knew about the load balancer thing, but that is typically really tough to implement in a database environment. Interestingly (and at the time of my post, I did only have two VMs), but I went ahead and added two more, which both got public IPs (all in different cloud services) for a total of four public IPs on my subscription. This is consistent with the behavior I’ve seen in the past (that each VM gets an IP–and typically I’m doing one VM per cloud service)–am I missing something? Thanks
Since you are using single instance cloud services each VM appears to get its own public VIP – but the VIP actually belongs to the cloud service not the VM. Your general guidance on not exposing SQL Server VMs to the public internet is, of course, spot on. There are a bunch of posts about using SQL Server Always On Availability Groups for HA and this does involve deploying multiple SQL Server VMs into a single cloud service (http://blogs.msdn.com/b/igorpag/archive/2014/07/03/deep-dive-sql-server-alwayson-availability-groups-and-cross-region-virtual-networks-in-azure.aspx).
Hello! Thanks for posting this which helped confirm my suspicion 🙂 Found numerous login attempts myself for names including sa, wwo, nanshou, sysdbsa, bwsa, masqer, vice, and Chred1433, from IP 61.147.103.145 (China). There was also an entry half a minute before this (related or not), which said “client 63.246.134.112#46911 (lalka.com.ru): query (cache) ‘lalka.com.ru/ANY/IN’ denied”.
Pingback: buy ballet