Secure Remote Management Studio access to SQL Server on Azure IaaS

If you have ever provisioned a SQL Server instance running on Azure IaaS and not used a VPN solution you will find that by default you are unable to connect to it from a local Management Studio instance.  By default all Virtual Machines are wrapped by a Cloud Service which behaves to a degree like ingress Security Groups do on AWS.  In this blog post I'll show you how you can open up a connection and then connect securely to it using SQL Authentication.

Note: making this change effectively opens your SQL Server up to traffic from the Internet though it is on a non-standard TCP port.  If you don't want this you should consider using an Azure Virtual Network and a VPN to protect connections to / from SQL server and a known location or device.  Alternatively you could setup a bastion or jump host that you first RDP to before connecting to SQL Server.

Updated: The release of the Azure SDK 2.0 introduces the concept of ACL on exposed endpoints and the 2.1 SDK exposes the setting of these values via PowerShell (see Set-AzureAclConfig). Awesome!

When you provision a new Virtual Machine by default it will provide two default TCP endpoints: Remote Desktop (RDP) and PowerShell.  As a first step we need to open access to port 1433 - we can do this using on the following two methods:

1. Via the Azure Management Portal:

• Click on Virtual Machines in the left navigation and select your shiny new SQL VM.
• Click on ENDPOINTS in the top navigation.  You should see a view similar to below:

• Now click the + ADD button at the bottom and select "Add Endpoint".
• On the Add Endpoint page complete:
  • Name: SQL TDS
  • Protocal: TCP
  • Public Port: Random number > 49152 (and not already in use on this Cloud Service or VM)
  • Private Port: 1433.
  • Click Tick to save new endpoint.

1. Via Powershell with the Azure Powershell Module.  Note that you will need to setup your Powershell environment to know about and connect to your Azure Subcription.  More information on this topic can be found on MSDN.

Get-AzureVM -ServiceName "yourcloudsevice" `
  -Name "yourvmhostname" | Add-AzureEndpoint -Name "SQL TDS" `
  -Protocol tcp -LocalPort 1433 -PublicPort 57153 | Update-AzureVM

Now that you completed the above you can connect to your SQL Server using Management Studio and encrypt the connection.  Open Management Studio and in the Connect to Server make the following changes:

1. Under "Server name" put the Cloud Service Public Virtual IP (VIP) address of your VM (find it on the Dashboard screen for the VM), a comma and then include the Public Port you mapped previously.  Your resulting input should look like "123.123.123.123,57153".

1. Click on the Options>> button and on the Connection Properties tab select "Encrypt connection".

1. Finally we need to tell Management Studio to trust the SSL certificate on the server. Click on the "Additional Connection Parameters" tab and enter "TrustServerCertificate=true". If you don't do this you will get an error and be unable to connect using encryption.

You should find that you can now connect to the VM.

I had a look to see if you could use Windows Firewall to restrict the traffic coming into your SQL Server by remote IP but at first glance it looks like it's not possible due to the NAT occuring at the cloud service interface.  I haven't had time to inspect the TCP traffic to see what's coming into the host but I suspect you can probably create a firewall rule to protect your machine, though as I said up front - use a VPN and Virtual Network if you really want to be protected.

Updated: The release of the Azure SDK 2.0 introduces the concept of ACL on exposed endpoints and the 2.1 SDK exposes the setting of these values via PowerShell (see Set-AzureAclConfig). Awesome!

HTH.