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
By default
- Clients connect to the gateway, that has a public IP addresses and listens on port 1433.
- This public IPs are a shared resource per regions and IPs documented at https://docs.microsoft.com/en-us/azure/sql-database/sql-database-connectivity-architecture#azure-sql...
- The gateway, depending on the effective connection policy, redirects or proxies the traffic to the right database cluster.
- Inside the database cluster traffic is forwarded to the appropriate Azure SQL database that will use ports 11000 - 11999.
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
We can see this server is in West Europe and this match the documented IP
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
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)
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
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 = '.database.windows.net' Database = '' # Set the name of the database you wish to test, 'master' will be used by default if nothing is set User = '' # Set the login username you wish to use, 'AzSQLConnCheckerUser' will be used by default if nothing is set Password = '' # Set the login password you wish to use, 'AzSQLConnCheckerPassword' will be used by default if nothing is set ## Optional parameters (default values will be used if ommited) SendAnonymousUsageData = $true # Set as $true (default) or $false RunAdvancedConnectivityPolicyTests = $true # Set as $true (default) or $false, this will download the library needed for running advanced connectivity tests CollectNetworkTrace = $true # Set as $true (default) or $false #EncryptionProtocol = '' # Supported values: 'Tls 1.0', 'Tls 1.1', 'Tls 1.2'; Without this parameter operating system will choose the best protocol to use } $ProgressPreference = "SilentlyContinue"; $scriptUrlBase = 'raw.githubusercontent.com/Azure/SQL-Connectivity-Checker/master' Invoke-Command -ScriptBlock ([Scriptblock]::Create((iwr ($scriptUrlBase+'/AzureSQLConnectivityChecker.ps1')).Content)) -ArgumentList $parameters #end
*Last Version at https://github.com/Azure/SQL-Connectivity-Checker
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
Now opened ports 1433 + 11XXX, you may also want to add 1434+14XXX to use DAC connection
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
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:
- IP firewall rule (Great for external resources)
- VNET Endpoints (Great for Azure resources that are VNET integrated)
- Private Endpoint (Great for environment with more restrictions)
For this test used VNET Endpoints as my client was an Azure VM and after enabled the connection was ok.
*Update 2020-03-26
VNET endpoint only works for Azure Resources and its more secure because all traffic flows inside Azure Backbone. And its simpler because you just need to open connections from clients coming from specific subnet, instead of using IP range.
*OnPrem resources still going to use Public endpoint and the company public oubound IP range need to be added to IP firewall rules
Even though you are going through private network, For VNET endpoint you need to keep "Deny public network access" as NO (Check image below). This option is only used for private link
To make this connection flow using Azure Backbone you will need to enable service endpoint in the client subnet
And you can check status of the endpoint in the SQL firewall
For full procedure on enabling VNET Endpoint check documentation below
- https://docs.microsoft.com/en-us/azure/sql-database/sql-database-vnet-service-endpoint-rule-overview
- https://azure.microsoft.com/pt-pt/blog/vnet-service-endpoints-for-azure-sql-database-now-generally-a...
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."