Additional Endpoints Required for AAD Authentication and CRL Checks for Azure SQL DB

Published Apr 21 2022 12:27 PM 1,304 Views
Microsoft

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: 

 

An example of the ‘Revocation of the SSL certificate failed’ error in SQL Server Management Studio (SSMS): 

pabrewer_0-1650568880636.png

 

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) 
*.graph.windows.net (Azure AD Graph) 

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://crl.microsoft.com 

HTTP/80 

Used to verify certificates. 

http://crl3.digicert.com 

HTTP/80 

Used to verify certificates. 

http://crl4.digicert.com 

HTTP/80 

Used to verify certificates. 

http://ocsp.digicert.com 

HTTP/80 

Used to verify certificates. 

http://www.d-trust.net 

HTTP/80 

Used to verify certificates. 

http://root-c3-ca2-2009.ocsp.d-trust.net 

HTTP/80 

Used to verify certificates. 

http://crl.microsoft.com 

HTTP/80 

Used to verify certificates. 

http://oneocsp.microsoft.com 

HTTP/80 

Used to verify certificates. 

http://ocsp.msocsp.com 

HTTP/80 

Used to verify certificates. 

http://www.microsoft.com/pkiops 

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 

Co-Authors
Version history
Last update:
‎Apr 21 2022 12:27 PM
Updated by: