Connect to a SQL Server Virtual Machine on Azure

Overview

This topic describes how to connect to your SQL Server instance running on an Azure virtual machine. It covers some general connectivity scenarios and then provides steps in the portal for changing connectivity settings. If you need to troubleshoot or configure connectivity outside of the portal, see the manual configuration at the end of this topic.

If you would rather have a full walk-through of both provisioning and connectivity, see Provisioning a SQL Server Virtual Machine on Azure.

Connection scenarios

The way a client connects to SQL Server running on a Virtual Machine differs depending on the location of the client and the networking configuration.

If you provision a SQL Server VM in the Azure portal, you have the option of specifying the type of SQL connectivity.

Public SQL connectivity option during provisioning

Your options for connectivity include:

Option Description
Public Connect to SQL Server over the internet
Private Connect to SQL Server in the same virtual network
Local Connect to SQL Server locally on the same virtual machine

The following sections explain the Public and Private options in more detail.

Connect to SQL Server over the Internet

If you want to connect to your SQL Server database engine from the Internet, select Public for the SQL connectivity type in the portal during provisioning. The portal automatically does the following steps:

  • Enables the TCP/IP protocol for SQL Server.
  • Configures a firewall rule to open the SQL Server TCP port (default 1433).
  • Enables SQL Server Authentication, required for public access.
  • Configures the network security group on the VM to all TCP traffic on the SQL Server port.

Important

The virtual machine images for the SQL Server Developer and Express editions do not automatically enable the TCP/IP protocol. For Developer and Express editions, you must use SQL Server Configuration Manager to manually enable the TCP/IP protocol after creating the VM.

Any client with internet access can connect to the SQL Server instance by specifying either the public IP address of the virtual machine or any DNS label assigned to that IP address. If the SQL Server port is 1433, you do not need to specify it in the connection string. The following connection string connects to a SQL VM with a DNS label of sqlvmlabel.eastus.cloudapp.azure.com using SQL Authentication (you could also use the public IP address).

Server=sqlvmlabel.eastus.cloudapp.azure.com;Integrated Security=false;User ID=<login_name>;Password=<your_password>

Although this enables connectivity for clients over the internet, this does not imply that anyone can connect to your SQL Server. Outside clients have to the correct username and password. However, for additional security, you can avoid the well-known port 1433. For example, if you configured SQL Server to listen on port 1500 and established proper firewall and network security group rules, you could connect by appending the port number to the Server name. The following example alters the previous one by adding a custom port number, 1500, to the server name:

Server=sqlvmlabel.eastus.cloudapp.azure.com,1500;Integrated Security=false;User ID=<login_name>;Password=<your_password>"

Note

When you query SQL Server in a VM over the internet, all outgoing data from the Azure datacenter is subject to normal pricing on outbound data transfers.

Connect to SQL Server within a virtual network

When you choose Private for the SQL connectivity type in the portal, Azure configures most of the settings identical to Public. The one difference is that there is no network security group rule to allow outside traffic on the SQL Server port (default 1433).

Important

The virtual machine images for the SQL Server Developer and Express editions do not automatically enable the TCP/IP protocol. For Developer and Express editions, you must use SQL Server Configuration Manager to manually enable the TCP/IP protocol after creating the VM.

Private connectivity is often used in conjunction with Virtual Network, which enables several scenarios. You can connect VMs in the same virtual network, even if those VMs exist in different resource groups. And with a site-to-site VPN, you can create a hybrid architecture that connects VMs with on-premises networks and machines.

Virtual networks also enable you to join your Azure VMs to a domain. This is the only way to use Windows Authentication to SQL Server. The other connection scenarios require SQL Authentication with user names and passwords.

Assuming that you have configured DNS in your virtual network, you can connect to your SQL Server instance by specifying the SQL Server VM computer name in the connection string. The following example also assumes that Windows Authentication has also been configured and that the user has been granted access to the SQL Server instance.

Server=mysqlvm;Integrated Security=true

Change SQL connectivity settings

Note

The following screenshots are from the SQL virtual machines resource within the Azure portal. For end-of-support (EOS) SQL server VMs, and SQL Server VMs that have not been registered with the SQL VM resource provider, use the SQL Server configuration tab to manage your SQL Server VM instead.

You can change the connectivity settings for your SQL Server virtual machine in the Azure portal.

  1. In the Azure portal, select SQL virtual machines.

  2. Select your SQL Server VM.

  3. Under Settings, select Security.

  4. Change the SQL connectivity level to your required setting. You can optionally use this area to change the SQL Server port or the SQL Authentication settings.

    Change SQL connectivity

  5. Wait several minutes for the update to complete.

    SQL VM update notification

Enable TCP/IP for Developer and Express editions

When changing SQL Server connectivity settings, Azure does not automatically enable the TCP/IP protocol for SQL Server Developer and Express editions. The steps below explain how to manually enable TCP/IP so that you can connect remotely by IP address.

First, connect to the SQL Server machine with remote desktop.

  1. After the Azure virtual machine is created and running, click the Virtual Machines icon in the Azure portal to view your VMs.

  2. Click the ellipsis, ..., for your new VM.

  3. Click Connect.

    Connect to VM in portal

  4. Open the RDP file that your browser downloads for the VM.

  5. The Remote Desktop Connection notifies you that the publisher of this remote connection cannot be identified. Click Connect to continue.

  6. In the Windows Security dialog, click Use a different account. You might have to click More choices to see this. Specify the user name and password that you configured when you created the VM. You must add a backslash before the user name.

    Remote desktop authentication

  7. Click OK to connect.

Next, enable the TCP/IP protocol with SQL Server Configuration Manager.

  1. While connected to the virtual machine with remote desktop, search for Configuration Manager:

    Open SSCM

  2. In SQL Server Configuration Manager, in the console pane, expand SQL Server Network Configuration.

  3. In the console pane, click Protocols for MSSQLSERVER (the default instance name.) In the details pane, right-click TCP and click Enable if it is not already enabled.

    Enable TCP

  4. In the console pane, click SQL Server Services. In the details pane, right-click SQL Server (instance name) (the default instance is SQL Server (MSSQLSERVER)), and then click Restart, to stop and restart the instance of SQL Server.

    Restart Database Engine

  5. Close SQL Server Configuration Manager.

For more information about enabling protocols for the SQL Server Database Engine, see Enable or Disable a Server Network Protocol.

Connect with SSMS

The following steps show how to create an optional DNS Label for your Azure VM and then connect with SQL Server Management Studio (SSMS).

Configure a DNS Label for the public IP address

To connect to the SQL Server Database Engine from the Internet, consider creating a DNS Label for your public IP address. You can connect by IP address, but the DNS Label creates an A Record that is easier to identify and abstracts the underlying public IP address.

Note

DNS Labels are not required if you plan to only connect to the SQL Server instance within the same Virtual Network or only locally.

To create a DNS Label, first select Virtual machines in the portal. Select your SQL Server VM to bring up its properties.

  1. In the virtual machine overview, select your Public IP address.

    public ip address

  2. In the properties for your Public IP address, expand Configuration.

  3. Enter a DNS Label name. This name is an A Record that can be used to connect to your SQL Server VM by name instead of by IP Address directly.

  4. Click the Save button.

    dns label

Connect to the Database Engine from another computer

  1. On a computer connected to the internet, open SQL Server Management Studio (SSMS). If you do not have SQL Server Management Studio, you can download it here.

  2. In the Connect to Server or Connect to Database Engine dialog box, edit the Server name value. Enter the IP address or full DNS name of the virtual machine (determined in the previous task). You can also add a comma and provide SQL Server's TCP port. For example, mysqlvmlabel.eastus.cloudapp.azure.com,1433.

  3. In the Authentication box, select SQL Server Authentication.

  4. In the Login box, type the name of a valid SQL login.

  5. In the Password box, type the password of the login.

  6. Click Connect.

    ssms connect

Manual configuration and troubleshooting

Although the portal provides options to automatically configure connectivity, it is useful to know how to manually configure connectivity. Understanding the requirements can also aid troubleshooting.

The following table lists the requirements to connect to SQL Server running in an Azure VM.

Requirement Description
Enable SQL Server Authentication mode SQL Server Authentication is needed to connect to the VM remotely unless you have configured Active Directory on a Virtual Network.
Create a SQL login If you are using SQL Authentication, you need a SQL Login with a user name and password that also has permissions to your target database.
Enable TCP/IP protocol SQL Server must allow connections over TCP.
Enable firewall rule for the SQL Server port The firewall on the VM must allow inbound traffic on the SQL Server port (default 1433).
Create a network security group rule for TCP 1433 You must allow the VM to receive traffic on the SQL Server port (default 1433) if you want to connect over the internet. Local and virtual-network-only connections do not require this. This is the only step required in the Azure portal.

Tip

The steps in the table above are done for you when you configure connectivity in the portal. Only use these steps to confirm your configuration or to setup connectivity manually for SQL Server.

Next Steps

To see provisioning instructions along with these connectivity steps, see Provisioning a SQL Server Virtual Machine on Azure.

For other topics related to running SQL Server in Azure VMs, see SQL Server on Azure Virtual Machines.