Azure SQL DB Connectivity Troubleshooting

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

Working in support we deal daily with connectivity issues to Azure SQL DB, find below some information to help you troubleshoot for connectivity issues.

 

First you need to understand the Azure SQL DB connectivity architecture

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-connectivity-architecture

 

When connecting to Azure SQL DB depending on where is the client (Azure / On-premises) and also depending on Connection policy you set you may need extra ports than default 1433

FonsecaSergio_0-1581095602203.png

By default

 

By default the connection policy will be set as DEFAULT

 

"This is the connection policy in effect on all servers after creation unless you explicitly alter the connection policy to either Proxy or Redirect. The default policy isRedirect for all client connections originating inside of Azure (e.g. from an Azure Virtual Machine) and Proxyfor all client connections originating outside (e.g. connections from your local workstation)."

 

With this knowledge we can start troubleshooting, but it will all depend on the error message

 

For this test I have used a server name that I know that is correct from an Azure VM and was expected to work. But I receive an error

 

===================================
Cannot connect to SERVERNAME.database.windows.net.
===================================
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 name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) (.Net SqlClient Data Provider)
------------------------------
Error Number: 10060
Severity: 20
State: 0

 

If the server is not found we need to check if we can reach the first point of contact (the GATEWAY) on PORT 1433.

 

Ping is not expected to respond, so this is not a good tool to test it. If you test a simple PING you can check that you are resolving correct the gateway name to the public IP

FonsecaSergio_1-1581098074954.png

We can see this server is in West Europe and this match the documented IP

FonsecaSergio_2-1581098118191.png

To check if you can reach the Port 1433, you can use multiple tools like TELNET / PSPING / POWERSHELL

 

Find below a simple test using Powershell

 

Test-NetConnection -Port 1433 -ComputerName SERVERNAME.database.windows.net

 

We can see that I'm failing to reach 1433

FonsecaSergio_3-1581098554936.png

 

You now will need to open firewall in the path from Server to Azure SQL DB, like Windows Firewall, Corporate Firewall and/or Azure NSG (Network security group) to allow this communication.

 

In this case for Azure VM will open NSG, and you can use Service Tags to simplify this configuration. With Service TAGs you do not need to hard code the SQL Gateway IPs.

 

Check the Outbound security rules and add one to open port 1433 to Service Tag SQL.WestEurope (Only Azure SQL DBs located in West Europe)

 

FonsecaSergio_0-1582213573695.png

 

 

Testing again we receive error below

 

===================================
Cannot connect to SERVERNAME.database.windows.net.
===================================
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 name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) (.Net SqlClient Data Provider)
------------------------------
Error Number: 10060
Severity: 20
State: 0

 

The error looks similar with the port 1433 closed. But in this case we could reach 1433 (Gateway) but you could not reach the SQL Server host that the gateway REDIRECTED your connection to on some port from 11000-11999, and you still did not open it yet in the NSG

 

You can also change the connection policy to proxy, this simplify the ports usage but will be slower than redirect

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-connectivity-architecture#script-to-change-connection-settings-via-powershell

 

 

To help troubleshooting we created a Powershell script to help this kind of scenario. Just open a Powershell ISE and execute the following

 

$parameters = @{
Server = 'SERVERNAME.database.windows.net'
#Subnet = '' #Managed Instance subnet CIDR range, in case of managed instance this parameter is mandatory
Database = 'sandbox'

## Optional parameters (default values will be used if omitted)
SendAnonymousUsageData = $true #Set as $true (default) or $false
}

$ProgressPreference = "SilentlyContinue";
$scriptUrlBase = 'raw.githubusercontent.com/Azure/SQL-Connectivity-Checker/master'
Invoke-Command -ScriptBlock ([Scriptblock]::Create((iwr ($scriptUrlBase+'/AzureSQLConnectivityChecker.ps1')).Content)) -ArgumentList $parameters
#end

 

 

And using this script we can see that we can reach gateways on port 1433, but could not reach the SQL Host on ports 11000 - 11999

FonsecaSergio_0-1583170272836.png

 

Now opened ports 1433 + 11XXX, you may also want to add 1434+14XXX to use DAC connection

FonsecaSergio_1-1583170443361.png

 

We can see the ports are now open where the script will test some random ports (Not exactly yours) and you should see most of them succeeding. I will also check other ports for AAD auth, etc.

 

However there are still some error

FonsecaSergio_1-1583255771075.png

 

Testing on SSMS we can error below

 

===================================
Cannot connect to SERVERNAME.database.windows.net.
===================================
Cannot open server 'SERVERNAME' requested by the login.
Client is not allowed to access the server. (.Net SqlClient Data Provider)
------------------------------
Server Name: fb869e9ddbfc.tr4030.westeurope1-a.worker.database.windows.net,11007
Error Number: 40914
Severity: 14
State: 1

 

If the outbound ports are open the problem now is at inbound side of Azure SQL DB

 

You can use some procedures to open Azure SQL DB firewall:

 
 
 

For this test used VNET Endpoints as my client was an Azure VM and after enabled the connection was ok.

 

 

PS: Azure Portal Query Editor

When connecting using query editor at Azure Portal you are going use ports different than default 1433 as documented at https://docs.microsoft.com/en-us/azure/sql-database/sql-database-connect-query-portal

"The query editor uses ports 443 and 1443 to communicate. Please ensure you have enabled outbound HTTPS traffic on these ports. You will also need to add your outbound IP address to the server's allowed firewall rules to access your databases and data warehouses."

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.