You may have attempts to connect to Azure SQL Database with an Azure Active Directory (AAD) account that are failing with a timeout error, but SQL Authentication works as expected. Alternatively you could be encountering generic connection errors with the inner exception being ‘Revocation of the SSL certificate failed’.
If the following workarounds are not sufficient, you may have to whitelist specific endpoints on your Firewalls or network infrastructure:
- Disabling the Revocation of the SSL Certificate Check: Revocation of the SSL certificate failed for AAD authentication - Microsoft Tech Community
- Allowing all SSL traffic from that server on their firewall
An example of the ‘Revocation of the SSL certificate failed’ error in SQL Server Management Studio (SSMS):
You can take a network trace in order to see where traffic is being blocked, or to grab the certificate that is being passed back. Having the certificate will allow you to examine the CRL endpoints listed in the certificate itself. Instead of taking a network trace, you may want to watch blocked traffic on your Firewall and whitelist trusted endpoints that appear in order to expedite troubleshooting. You can test connectivity to ports via PowerShell using, for example, “tnc login.windows.net -port 443”.
If you have an ADFS setup, then to authenticate you will need to whitelist your ADFS endpoint on port 443. If you have multiple ADFS endpoints you will need to whitelist the one that your DNS server is resolving you to.
This is a non-exhaustive list of endpoints that may be required depending on the authentication type. Additional endpoints may be required to communicate with Active Directory Federation Services (ADFS), or depending on your networking path and if you are using a public Certification Authority (CA), endpoints for whoever has issued your certificate for ADFS.
Please note not all Firewalls can be configured to accept wildcards, and while the below list has as many explicitly defined URLs as possible, you may need to see what traffic is being blocked on the Firewall to get definite URLs for those not fully defined here. This is also how you can determine if something not on this list is being blocked and is required for your particular networking setup (perhaps the ADFS endpoint your machine is reaching out to is not the one you expect, etc).
|
URL |
Port |
Description |
|
mscrl.microsoft.com |
HTTP/80 |
Used to download CRL lists. |
|
*.verisign.com |
HTTP/80 |
Used to download CRL lists. |
|
*.entrust.net |
HTTP/80 |
Used to download CRL lists for MFA. |
|
*.management.core.windows.net (Azure Storage) |
HTTPS/443 |
Used for the various Azure services |
|
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. |
|
login.microsoftonline.us |
HTTPS/443 |
Used by US Gov for AD Login. |
|
login.microsoftonline.com |
HTTPS/443 |
Used by Public cloud for AD login for MFA. |
|
login.windows.net |
HTTPS/443 |
Used by Public cloud for AD login for Password and Integrated. |
|
HTTP/80 |
Used to verify certificates. |
|
|
HTTP/80 |
Used to verify certificates. |
|
|
HTTP/80 |
Used to verify certificates. |
|
|
HTTP/80 |
Used to verify certificates. |
|
|
HTTP/80 |
Used to verify certificates. |
|
|
HTTP/80 |
Used to verify certificates. |
|
|
HTTP/80 |
Used to verify certificates. |
|
|
HTTP/80 |
Used to verify certificates. |
|
|
HTTP/80 |
Used to verify certificates. |
|
|
HTTP/80 |
Used to verify certificates. |
|
|
cacerts.digicert.com |
HTTP/80 |
Used to verify certificates. |
|
ctldl.windowsupdate.com |
HTTP/80 |
Used to verify certificates. |
References