By: Mohammed Moinudheen | Comments | Related: > Azure
Problem
I have a SQL Server instance installed on an Azure Virtual Machine (VM). Is there a way I can access this instance using SQL Server Management Studio (SSMS) on my local desktop?
Solution
This tip assumes that you already have a SQL Server instance installed on an Azure VM. You could use this link for setting up your own SQL Server on an Azure VM. For further reading on Azure, refer to these useful SQL Azure tips on MSSQLTips.com.
Connecting to Azure using SSMS locally
From the Azure portal as shown below, connect to the Azure Virtual machine where you have the SQL Server instance installed.
![Azure Portal](/tipimages2/4328_Azure_Portal.jpg)
Next, configure the firewall rules on this virtual machine to open port 1433. Go to Control Panel > Windows Firewall. On the Windows Firewall window, click on "Advanced settings".
![Windows Firewall Advanced Settings](/tipimages2/4328_Windows_Firewall_Advanced_Setting.jpg)
On the "Windows Firewall with Advanced Security" window, click on "Inbound Rules" and in the right pane select "New Rule".
![Windows Firewall Inbound Rules](/tipimages2/4328_Windows_Firewall_Inbound_Rules.jpg)
In the "New Inbound Rule Wizard", click on "Port" and click "Next".
![Port for the new Inbound Rule Wizard](/tipimages2/4328_Port.jpg)
In the "Protocol and Ports" window, select "TCP" and enter "Specific Local Ports" as 1433 and click "Next".
![TCP Port for the New Inbound Rule Wizard](/tipimages2/4328_TCP_Port.jpg)
In the "Action" window, select "Allow the connection" option and click "Next".
![Allow the connection for the New Inbound Rule Wizard](/tipimages2/4328_Allow_The_Connection.jpg)
In the "Profile" window, leave the default selections as shown below and click "Next".
![Profile for the New Inbound Rule Wizard](/tipimages2/4328_Profile.jpg)
In the "Name" window, enter details as shown below and click "Finish".
![Finish Windows Inbound Rule Wizard](/tipimages2/4328_Finish_Window_Inbound_Rule.jpg)
Next, verify the TCP/IP protocol is enabled on the virtual machine. Go to the SQL Server Configuration Manager console. From Windows Explorer type "SQLServerManager13.msc" which will take you to the SQL Server 2016 Configuration Manager. Under "SQL Server Network Configuration", ensure that TCP/IP is enabled. Refer to the screenshot below. If this is disabled, change the status to "Enabled" and then restart the SQL Server services.
![Verify TCP/IP is running in the SQL Server Configuration Manager](/tipimages2/4328_TCP_IP.jpg)
As we will be connecting to the SQL Server instance locally outside the Azure VM, we need to use SQL authenticated logins. For this make sure that mixed mode authentication is enabled on the SQL Server instance. Refer to the screenshot below. If mixed mode is not enabled, make sure you enable this option and then restart the SQL Server services.
![SQL Server Mixed Mode Authentication](/tipimages2/4328_Mixed_Mode_Authentication.jpg)
Ensure that a SQL authenticated login is created with sufficient permissions. This is the login you will be using for connecting locally to the Azure SQL instance using SSMS.
![SQL Server Login](/tipimages2/4328_SQL_Login.jpg)
On the Azure portal, click on the Virtual Machine, go to "Settings" and select "Endpoints" as shown.
![Windows Azure Portal Settings Endpoints](/tipimages2/4328_Portal_Settings_Endpoints.jpg)
Click on "Add" and enter the details as shown and save.
![Add SQL Server Endpoint](/tipimages2/4328_Add_SQL_Endpoint.jpg)
To connect to your SQL instance locally, you will need the DNS name which you can get from the Azure portal. Refer to this screenshot.
![DNS Name for the Virtual Machine](/tipimages2/4328_DNS_Name.jpg)
With SQL Server 2016, we can download just SQL Server Management Studio which is free and does not require a license to use. If SSMS is not installed locally, you can download SSMS from this link. In SSMS, provide the full DNS name with the public port you provided in the endpoints and the credentials used for the SQL login you created. Refer to the screenshot below.
![Connect Azure VM with SQL Server Management Studio Locally](/tipimages2/4328_SSMS_Connect.jpg)
With this, you should be able to connect to the SQL Server instance installed on an Azure VM using your local copy of SSMS.
Next Steps
- Try this tip by creating your own SQL Server instance on Azure and connecting locally using SSMS.
- If you don't have access to Azure, try using the trial version from here where you will get access for a month and a credit up to USD $200
- Refer to all the useful Azure related tips on MSSQLTips.
About the author
![MSSQLTips author Mohammed Moinudheen](/images/MohammedMoinudheen.jpg)
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips