In 2013, Microsoft made it available for customers to utilize virtual machines running in Windows Azure. This allowed customers to run an on-premises version of SQL Server, but have the instance hosted in the cloud. The natural question then became, “How do I connect to the Azure VM using SQL Server Management Studio (SSMS)?"
Connecting to an instance of SQL Server running inside of an Azure VM can be completed in just a few steps:
- Create your VM
- Open a port for the VM inside the Azure management portal
- Open a port in the Windows firewall on the Azure VM
- Configure security for the instance; verify TCP is enabled
- Connect remotely with SSMS
It’s that easy. Let’s take a look at the steps in more detail.
Create your VM
First we will create an Azure VM. Here I will choose the “Quick Create” option that has an image of SQL 2012 SP1 running on Windows Server 2012:
Open a port in the Azure management portal
Once the VM is created, you will need to open up a port to allow access to the instance of SQL Server. I’m going to stick with the default port of 1433 and create a new endpoint here:
Open a port in the Windows firewall
Next up, we need to configure the firewall on the server itself. I will use Remote Desktop to connect to the server and get this part done. From the dashboard, I can click on "Connect" in the lower left:
This will open up an RDP session
for me. From there I will configure a new inbound rule in the Windows firewall for port 1433:
I will select a rule type of ‘Port,’ as shown above. The port will be for 1433 because I’m showing a trivial example here. I’d encourage anyone actually opening up an instance of SQL Server like this to use a non-default port.
After clicking next, the options I will select are as follows (sparing you from a bunch of screenshots):
- Select TCP and specify port 1433
- Next, select ‘Allows This Connection’
- Then, select when you want the rule applied (I chose ‘Public,’ choose what is right for you)
- Lastly, assign this rule a name (I chose SQL because I’m lazy)
Here is where I also would advise you to take a moment to verify that SQL Server is enabled to use the TCP protocol:
Next, we will configure SQL Server to use mixed mode authentication for the purpose of a simple example. Note that Windows Azure does support Active Directory at this time, so this may be an option for you.
We will change the security to ‘Mixed Mode’ and create a new SQL login:
After a restart to the instance we should be able to connect:
Connecting to an instance of SQL Server running in an Azure VM is very simple and straightforward process. You are just a few clicks away from being able to access your instance running in an Azure VM from the client tools installed on your desktop.