Timeout and ADALGetAccessToken Error with AAD connections

  • Article

Scenario:

You have created an Azure SQL Database through the Azure Portal. After opening the outgoing port 1433 on your proxy server and firewall, you are able to connect to the database, using the database administrator account that you have specified while creating the database.

To give your domain accounts access to the database, you are also adding users from your Azure Active Directory (AAD) to the database. You are assigning roles like "SQL DB Contributor" through the portal and add users to the database. See Connecting to SQL Database By Using Azure Active Directory Authentication for an overview and a complete and set of steps.

Issue:

All the attempts to connect with an Azure Active Directory account are failing with a timeout error.

For example, you may see the following message in SQL Server 2016 Management Studio:

===================================
Cannot connect to your_server_name.database.windows.net.
===================================
Failed to authenticate the user aad_user_name@yourcompany.onmicrosoft.com in Active Directory (Authentication=ActiveDirectoryPassword).
Error code 0xCAA82EE2; state 10
The request has timed out. (.Net SqlClient Data Provider)
------------------------------
Server Name: your_server_name.database.windows.net
Error Number: 0
Severity: 11
State: 0
Procedure: ADALGetAccessToken

Cause:
This error may occur if the access to the Azure Active Directory (AAD) is blocked by your proxy or firewall.

A database connection with an AAD account requires several steps for authentication. After the first step over port 1433 has succeeded, a second step follows from the ADAL (Active Directory Authentication Library) client component towards the database and AAD. This second step needs access to several AAD URLs in the Azure environment. If your proxy or firewall blocks those URLs, the authentication request will time out eventually.

Solution:

In addition to the outgoing port 1433, you need to open your proxy or firewall for additional URLs or IP ranges. The full range is described through the following article:

Troubleshoot connectivity issues with Azure AD Connect

This article explains how connectivity between Azure AD Connect and Azure AD works and how to troubleshoot connectivity issues. These issues are most likely to be seen in an environment with a proxy Server.

(...)

The proxy server must also have the required URLs opened. The official list is documented in Office 365 URLs and IP address ranges .

Of these, the following table is the absolute bare minimum to be able to connect to Azure AD at all. This list does not include any optional features, such as password writeback, or Azure AD Connect Health. It is documented here to help in troubleshooting for the initial configuration.

URL

Port

Description

mscrl.microsoft.com

HTTP/80

Used to download CRL lists.

*.verisign.com

HTTP/80

Used to download CRL lists.

*.entrust.com

HTTP/80

Used to download CRL lists for MFA.

*.windows.net

HTTPS/443

Used to sign in to Azure AD.

secure.aadcdn.microsoftonline-p.com

HTTPS/443

Used for MFA.

*.microsoftonline.com

HTTPS/443

Used to configure your Azure AD directory and import/export data.

In this list, "CRL" is referring to the Certificate Revocation List, and "MFA" relating to Multi-Factor Authentication. To resolve the immediate ADAL error from above, you need to open *.windows.net – this should allow you to connect. We recommended to open the other URLs as well to provide for the security features and basic configuration options. If you need additional features, you may have to add further URLs as described in Office 365 URLs and IP address ranges.

Further References: