Access SQL Server instance on Azure VM using local copy of Management Studio

By:   |   Updated: 2016-06-20   |   Comments   |   Related: > Azure


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?


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

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

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

On the "Windows Firewall with Advanced Security" window, click on "Inbound Rules" and in the right pane select "New Rule".

Windows Firewall Inbound Rules

In the "New Inbound Rule Wizard", click on "Port" and click "Next".

Port for the new Inbound Rule Wizard

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

In the "Action" window, select "Allow the connection" option and click "Next".

Allow the connection for the New Inbound Rule Wizard

In the "Profile" window, leave the default selections as shown below and click "Next".

Profile for the New Inbound Rule Wizard

In the "Name" window, enter details as shown below and click "Finish".

Finish Windows Inbound Rule Wizard

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

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

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

On the Azure portal, click on the Virtual Machine, go to "Settings" and select "Endpoints" as shown.

Windows Azure Portal Settings Endpoints

Click on "Add" and enter the details as shown and save.

Add SQL Server Endpoint

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

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

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.

Last Updated: 2016-06-20

get scripts

next tip button

About the author
MSSQLTips author Mohammed Moinudheen Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

View all my tips
Related Resources

Comments For This Article


Recommended Reading

Adding Users to Azure SQL Databases

Connect to On-premises Data in Azure Data Factory with the Self-hosted Integration Runtime - Part 1

Transfer Files from SharePoint To Blob Storage with Azure Logic Apps

Reading and Writing data in Azure Data Lake Storage Gen 2 with Azure Databricks

Create a Python Wheel File to Package and Distribute Custom Code

get free sql tips
agree to terms