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


By:   |   Updated: 2020-03-24   |   Comments   |   Related: More > 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).

azure sql authentication settings

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.

azure sql authentication settings

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

sql server connection error

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

azure sql authentication settings

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

azure sql authentication settings

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.

azure sql authentication settings

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.

azure sql authentication settings
Next Steps


Last Updated: 2020-03-24


get scripts

next tip button



About the author
MSSQLTips author Vitor Montalvao 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.

View all my tips
Related Resources





Comments For This Article





download


Recommended Reading

Adding Users to Azure SQL Databases

Azure Data Factory vs SSIS vs Azure Databricks

Azure Data Factory Pipeline Email Notification – Part 1

Connect to On-premises Data in Azure Data Factory with the Self-hosted Integration Runtime - Part 1

Continuous database deployments with Azure DevOps





get free sql tips
agree to terms


Learn more about SQL Server tools