Connect to Windows Azure Virtual Machine Using SSMS

By: Thomas LaRock


In 2013, Microsoft made available the ability for customers to utilize virtual machines running in Windows Azure. This allowed for customers to run 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:

  1. Create your VM
  2. Open a port for the VM inside the Azure management portal
  3. Open a port in the Windows firewall on the Azure VM
  4. Configure security for the instance; verify TCP is enabled
  5. Connect remotely with SSMS

It’s that easy. Let’s take a look at the steps in more detail.

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:

Connect Azure VM SSMS 1

 

Once the VM is created you will need to open up a port in order 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:

Connect Azure VM SSMS 2

 

Next up we need to configure the firewall on the server itself. I will use Remote Desktop to connect to the server to get this part done. From the dashboard, I can click on ‘Connect” in the lower left:

Connect Azure VM SSMS 3

 

This will open up RDP session for me. From there I will configure a new inbound rule in the Windows firewall for port 1433:

Connect Azure VM SSMS 4

 

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 take a moment to verify that SQL Server is enabled to use the TCP protocol:

Connect Azure VM SSMS 5

 

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:

Connect Azure VM SSMS 6

 

After a restart to the instance we should be able to connect:

Connect Azure VM SSMS 7

 

Voila!

 

Connect Azure VM SSMS 8

 

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.

 

Comments

  1. I followed exactly the steps above but still failed to connect sql server in azure vm. Always encounter this error.The network path was not found. Please advise what else i should configure?

  2. I followed exactly the steps above but still failed to connect sql server in azure vm. Always encounter this error.The network path was not found. Please advise what else i should configure?

  3. 1. Run following commands on Azure VM SQL Server:

    use master
    go
    xp_readerrorlog 0, 1, N’Server is listening on’
    go

    2. Take first entry (such as 49267) and open port in firewall on Azure VM.

    3. Create new Endpoint on Azure Portal with same Public and Private port.

    4. Connect to SQL Server on Azure VM from your SSMS on desktop:
    my-vm.cloudapp.net, Port
    or
    my-vm.cloudapp.netSQL instance, Port

  4. Is it possible to configure ports using trial subscription? its kinda difficult locating where the configuration for port and protocol are as stated above.

Leave a Reply