Connect to SQL Server on Azure VM via Local SSMS

After you provision a Microsoft Azure VM with SQL Server there are a few more steps that you need to take to make remote connections. The procedure below starts with a fresh Azure VM provisioned and walks through the process of establishing a connection via SQL Server Management Studio, installed on an on-premises work station.

NOTE: This article is applicable to azure classic deployments only.

Create a new Azure TCP/IP endpoint

Start by accessing the Azure portal and navigating to your new VM.

azure-tcp-endpoint-1

Drill into your VM, navigate to the ENDPOINTS tab, and click ADD to create a new endpoint.

azure-tcp-endpoint-2
A wizard will appear. Select ADD A STAND-ALONE ENDPOINT and click the right-arrow.

azure-tcp-endpoint-3
Use the drop-down box to select MSSQL and edit the ports, if you choose.

azure-tcp-endpoint-4

Click the check mark to complete and then wait for the Azure portal to tell you that the endpoint has been created.
azure-tcp-endpoint-5

Remote desktop into your VM

Once our endpoint is created we will need to do some work with Windows and SQL Server. Navigate to your Azure VM Dashboard and download your customized .rdp file with the CONNECT button.

azure-rdp-1

Connect to your VM via the downloaded .rdp file.

azure-rdp2

Verify TCP/IP is enabled for SQL Server

Open up SQL Server Configuration Manager and enable the TCP/IP protocol, if it is not already. In the VM image that I provisioned for SQL Server 2016 CTP 3.0 the TCP/IP protocol was enabled but it is always good to verify.

azure-sql-tcp-1

Configure SQL Server for Mixed Mode authentication

Open SQL Server Management Studio, right-click on your instance in the object explorer and select Properties. On the Security page, select the SQL Server and Windows Authentication mode radio button and hit OK. Finish up by restarting your SQL Server instance for the setting to take effect.

azure-sql-tcp-2

Open your SQL Server connection port

Finally we have to open up the SQL Server connection port in the Windows Firewall with Advanced Security by creating a new inbound rule.

azure-firewall-rule-1

When the wizard opens, select the rule type Port and click Next.

azure-firewall-rule-2

Specify your port and click Next.

azure-firewall-rule-3

Allow the connection and then click Next.

azure-firewall-rule-4

Another Next…

azure-firewall-rule-5

…followed by a name for your rule and click Finish.

azure-firewall-rule-6

Connect

Now it is time to test. Disconnect from your remote desktop session and launch SSMS on your work station. Connect using your Azure DNS name…

azure-ssms-1

…SUCCESS!

azure-ssms-2

This article has 2 comments

  1. […] Derik Hammer shows you how to connect to an Azure VM hosting SQL Server: […]

  2. … [Trackback]

    […] Read More: sqlhammer.com/connect-to-sql-server-on-azure-vm-via-local-ssms/ […]

Leave a Reply