By: Mohammed Moinudheen | Updated: 2016-06-20 | 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.
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".
On the "Windows Firewall with Advanced Security" window, click on "Inbound Rules" and in the right pane select "New Rule".
In the "New Inbound Rule Wizard", click on "Port" and click "Next".
In the "Protocol and Ports" window, select "TCP" and enter "Specific Local Ports" as 1433 and click "Next".
In the "Action" window, select "Allow the connection" option and click "Next".
In the "Profile" window, leave the default selections as shown below and click "Next".
In the "Name" window, enter details as shown below and click "Finish".
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.
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.
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.
On the Azure portal, click on the Virtual Machine, go to "Settings" and select "Endpoints" as shown.
Click on "Add" and enter the details as shown and save.
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.
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.
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
Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2016-06-20
