Additional Endpoints Required for AAD Authentication and CRL Checks for Azure SQL DB
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) *.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 Azure Active Directory certificate authorities | Microsoft Docs Azure TLS Certificate Changes | Microsoft Docs Azure AD Connect: Troubleshoot Azure AD connectivity issues | Microsoft Docs Revocation of the SSL certificate failed for AAD authentication - Microsoft Tech Community Microsoft 365 endpoints - Microsoft 365 Enterprise | Microsoft Docs
Dataflow
Hi, Urgently need help - how to read 120gb (3.4billion rows from a table) at lightening data from azure SQL server database to azure data Lake. I tried to two options: Copy activity with parallelism and highest DIU - this gives time out error after long running hours Data flow - this takes 11 hours long time to read data Please suggest
