If you’re running SQL Server somewhere in Amazon Web Services (AWS), you’ve probably heard that it should not have a public-facing IP address. Your SQL Server likely stores your organization’s most precious data, and you want to protect it like you would your first-born, and that means cutting it off from outside access.

What happens when you have a public-facing SQL Server?

In time you will see lots of entries like this in your Event Application Log:

 

Even if you disable your “sa” account, you’ll still get hammered with login attempts, but with a slightly different Application Log message:

Every SQL Server that’s publicly accessible on the Internet is eventually subject to a brute force attack on the “sa” account. So not only should your SQL Server be protected from the outside Internet, the “sa” account should be disabled as well. Disabling the “sa” account is easy.

My trick to blocking public access to SQL Server

Setting up your SQL Server so that it can’t be accessed by the Internet but can be accessed by your web and application servers is trickier, though. Amazon provides an article on how to do that via multiple VPCs, but I’m a simple man, and I like simple solutions. After testing several scenarios, I arrived at the following plan that adequately protects my SQL Server instance and, incidentally, saves money on my AWS bill.

The trick is to put all of my servers, applications, web, and SQL Servers, in the same Availability Zone, and then lock down the Availability Zone using a Network ACL in my Virtual Private Cloud (VPC). A simple DENY rule blocking port 1433 to my VPC accomplishes this. Any servers in the same Availability Zone and subnet will still be able to access port 1433, since the ACL only protects the subnet from anything outside the subnet, not inside.

You’ll notice that I have an ALLOW rule for my own IP right above the DENY rule that blocks all access to port 1433, the standard TCP/IP port used by SQL Server. This is so that I can still manage my SQL Server from my personal workstation. You might be wondering why you need to bother with the Network ACL, which applies to the entire subnet, rather than just setting Inbound rules in the Security Group that holds the EC2 Instance. The reason is that Security Group rules are only for ALLOWING port access. You can’t put a DENY rule in a Security Group, but you can in a Network ACL.

Save money on your AWS bill

The added benefit of having all of your servers in the same Availability Zone is that you don’t pay for bandwidth between your servers. If your servers are across different Availability Zones, then you pay $.01/GB transfer. For me, that portion of my data transfer alone has been racking up $250/month in fees recently. Actually, there are two conditions necessary for avoiding data transfer fees between your instances: 1) They must be in the same AZ; 2) They must communicate with each other via their private IP address, not a public IP address.

It’s important to note that I host my own SQL Server on an EC2 Windows instance. I don’t use RDS, but the same concepts I’m using here should also apply if your SQL Server lives on an RDS endpoint. I considered RDS but decided against it because I’m a power SQLAgent user, and SQLAgent has restricted capabilities in RDS.

You might argue that putting all of your servers in the same Availability Zone is silly since the whole point of multiple AZs is to allow for redundancy in case one zone goes offline. You’d be right, but if a poor man’s solution to locking down your SQL Server appeals to you in the first place, I’m betting you’re not worried about redundancy across Availability Zones. In my 4+ years of hosting with AWS, not one of my Availability Zones has ever gone down.

Moving my servers into the same Availability Zone

To get this setup working, I had to consolidate all my servers into one Availability Zone, which was tricky because my servers were across three of them, us-west-2a, us-west-2b, and us-west-2c. To make matters worse, my SQL Server was on us-west-2c while most of my application and web servers were in us-west-2a. I had a choice to make: switch my SQL Server to us-west-2a OR switch the rest of my servers to us-west-2c. Because changing Availability Zones results in instance downtime since you have to stop and re-start the instance, and because taking down my SQL Server brings down my entire system, I opted to switch the rest of my servers to us-west-2c, which I could do in batches to prevent a disruption in customer’s email campaigns.

Lastly, to avail myself of the cost savings by having all servers in the same AZ, the servers must also communicate with each other by their private IP, not by their public IP. This makes sense, because if all of your application servers are hitting your SQL Server by its public IP, then the traffic is first leaving your AZ and then coming back in via a public routing. If, however, you connect by a private IP, then your traffic stays inside your subnet and AZ.

I had two options to change easily from public IP access to private IP access of the SQL box: change the DNS entry for database.gmass.co, or put in a “hosts” entry into all my application servers, hard-coding database.gmass.co to the private IP address so that it resolves to the private IP rather than the public IP from the Internet’s DNS system. The TTL on database.gmass.co was low enough that I just changed the IP address of database.gmass.co from the public-facing IP on the SQL Server to its private IP. Plus, this way, if I ever set up a new server, I won’t have to remember to put in that “hosts” file entry.

So now, while my SQL Server still has a public-facing IP address, it’s impossible to know what that IP address is. Furthermore, if someone did figure out that public IP, they’d be stopped in their tracks from accessing the server because between the Network ACL, which protects the whole AZ, and the Security Group’s Inbound Rules that protect the individual instance, no inbound access is allowed. Even Remote Desktop (RDP) access is allowed only from my personal workstation.

To lock down the SQL Server even further, the EC2 Security Group allows only outbound access over port 25 so that the server can send email notifications to me about certain events, such as a BACKUP success or failure.

 

Note: By the way, database.gmass.co isn’t the real domain name for my SQL Server.

Don’t like this approach?

If you’re an enterprise user and don’t want a “hacky” solution to securing something as important as your SQL Server, see AWS’s Best Practices Guide to SQL Server deployment.