神刀安全网

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

By:Mohammed Moinudheen |  |   Related Tips: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 usefulSQL 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.

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

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".

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

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

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

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

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

In the "Protocol and Ports" window, select "TCP" and enter "Specific Local Ports" as 1433 and click "Next".

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

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

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

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

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

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

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

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.

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

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.

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

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.

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

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

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

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

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

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.

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

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.

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

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 Update:

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

About the author

Access SQL Server instance on Azure VM using local copy of Management Studio 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

转载本站任何文章请注明:转载至神刀安全网,谢谢神刀安全网 » Access SQL Server instance on Azure VM using local copy of Management Studio

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址