How To Connect Remotely to SQL Server on an Azure Virtual Machine

By: Vitor Montalvao   |   Updated: 2020-03-24   |   Comments (3)   |   Related: > Azure

Problem

When using a local SQL Server Management Studio, I get an error when trying to connect to my SQL Server instance that is installed in an Azure VM. How can I solve this issue?

Solution

This article explains what issues you might have when trying to connect to a SQL Server instance in an Azure VM and how to solve those issues.

Control access to SQL Server Azure VM

When creating your Azure VM, where you will install SQL Server, you need to also configure access. If you have had created the Azure VM in your Private Azure Cloud or if you don’t want to allow connections from other networks, then it isn’t recommended that you create SQL credentials to access your SQL Server databases, but if you need to allow it, you should keep the sa login disabled as it is very well known and often a target for malicious users.

On the other hand, if you created the Azure VM in the Public Azure Cloud and you need to access SQL Server remotely then you’ll need to create SQL Server credentials to access the SQL Server instance. Once more, it’s recommended to not enable the sa login.

SQL Credentials

If you didn’t configure the Azure SQL Server instance to allow SQL credentials you can configure it afterwards in the Azure Portal by selecting the Security option of your SQL virtual machine instance and Enable SQL Authentication.

If you want to provide a sysadmin role for a SQL credential, you can type the new login name and provide a respective password (if you don’t provide a password it will use the same password as the administrator user of the respective Azure VM).

Note that you can use the above screen to disable SQL Credentials.

Another option is to configure it by connecting to the SQL Server VM and in SSMS edit the SQL Server instance properties and in Security enable the SQL Server and Windows Authentication mode.

Restart the SQL Server service and then you can create SQL credentials to access the SQL Server instance databases.

Errors connecting to SQL Server

If you get the following error (i.e. A network-related or instance-specific error occurred while establishing a connection to SQL Server.  The Server was not found or was not accessible.  Verify that the instance is correct and that SQL Server is configured to allow remote connections.) when trying to connect remotely to a SQL Server Azure instance

then in the Azure Portal check the security configuration for the respective SQL Server instance.

By default, a new SQL Server instance in Azure is configured for Private SQL connectivity (only let connections from the same Virtual Network connect to the SQL Server instance). Since we need to connect to it remotely from another network, we’ll need to change this configuration to Public (internet).

When changing this configuration, an automatic firewall rule is added for the Azure VM for this SQL Server instance. You can see that in the Azure VM Networking configuration.

TIP: You can edit this rule to add specific IPs for the machines that you want to allow to connect to this SQL Server instance.

Next Steps

• Check out Security Considerations for SQL Server in Azure Virtual Machines

About the author

Vitor Montalvão is a SQL Server engineer with 20 years of experience in SQL Server, specializing in performance & tuning, data modelling, migration and security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips