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:
- 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.
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:
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:
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:
This will open up 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 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:
Voila!
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.
daytrading zoo says
Great, thanks for explaining how to setup the firewall on Azure. Works like a charm!
Veronika Milcheva says
Best explanation that I found on the web! Thank you
cdevpro says
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?
ThomasLaRock says
Can you verify the network properties in config manager? The error message says you are having issue using named pipes, which seems odd.
cdevpro says
Hi Thomas, thanks for your prompt reply. I’ve also enabled Named Pipes at config manager. However it does not work.
ThomasLaRock says
I’d suggest you check all the common things, such as firewalls, etc. Try connecting to the instance in different ways (sqlcmd, SSMS, etc.) and from different machines. Eventually you’ll narrow down where the disconnect is between your client and the Azure VM.
Diomedes Ignacio Domínguez Ure says
Hi, I just got the same problem. To resolved you must check your scope selection.
Aaron Shumaker says
Same problem. Checked all of these things and no clue what problem is :/
Frank Szendzielarz says
Use mixed mode authentication and connect using the SA account.
T Tan says
I am having the same problem. It might be a problem unique to SQL Express. Try solution here http://support.webecs.com/kb/a868/how-do-i-configure-sql-server-express-to-allow-remote-tcp-ip-connections-on-port-1433.aspx
cdevpro says
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?
arham alam says
hey im finding simple way to connect SQL azure to ssms
http://www.imritz.com/sql/connect-to-sql-azure-with-ssms/
Sandip Nandi says
Nice article, appreciate the efforts
Miguel M. Anastacio Velásquez says
Have you try this?
tcp:servername.database.windows.net,1433
Monish Jain says
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
Quist says
Is it possible to configure ports using trial subscription? its kinda difficult locating where the configuration for port and protocol are as stated above.
Ano says
Excellent!! it worked like charm