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


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

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




Wednesday, January 6, 2021 - 2:39:28 PM - DANIEL WESTERDALE Back To Top (88010)
Thanks for the reply. Yes, the error is confusing as there no network share involved, just a Azure vm running SQLExpress. After much swearing, I managed to track down the issue. On SQL Server network settings, click on TCP/IP ( which I had already enabled) , scroll down to AllIps section .next, enter the Port No 1433 and then at last I can connect with SQL Man Studio. I added the public IP address earlier but forgot the port , this new setting removes the need for the public IP.

Wednesday, January 6, 2021 - 12:57:29 PM - Vitor Montalvão Back To Top (88009)
Hi Daniel. Network error 53 is related to Windows share. Not sure why you need a network share in your case but you should check any share that you are using in your SQL Server instance.

Wednesday, January 6, 2021 - 10:29:44 AM - DANIEL WESTERDALE Back To Top (88008)
I have installed SQLExpress on Windows 2016 VM which works fine when I rdp on to it and use SQL Man Studio. I get the dreaded network error 53 if I try attempt to connect from laptop. I notice in Azure that the prod instance I am trying replicate has a SQL Server resource as mentioned in your article. I don't have that form Dev instance as In Azure just sees the VM like any other. Is this just the way the prod instance was created?


download





Recommended Reading

Adding Users to Azure SQL Databases

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

Process Blob Files Automatically using an Azure Function with Blob Trigger

Reading and Writing data in Azure Data Lake Storage Gen 2 with Azure Databricks

Create a Python Wheel File to Package and Distribute Custom Code














get free sql tips
agree to terms