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


By:   |   Updated: 2016-06-20   |   Comments   |   Related: More > 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

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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools