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 DocsMemory errors during data extraction from SAP using Azure Data Factory SAP Table connector
Azure Data Factory (ADF) is a fully managed data integration service for cloud-scale analytics in Azure. ADF provides more than 90 out of the box connectors to integrate with your source and target system. When we think about enterprise systems, SAP play a major role.How to use Semantic Kernel Bot in-a-box to interact with data using natural language & AI
We are thrilled to discuss two new features for Semantic Kernel's AI-powered assistant - SQLPlugin and UploadPlugin. SQLPlugin uses SQL to extract insights that can transform the way professionals interact with data, while UploadPlugin lets users upload documents and retrieve knowledgeAbout Azure SQL Database
Hi, how are you? I wonder if you can help me to understand something about Azure SQL Database in the Azure Calculator. This is: I have read extensively here and here. I understand that Min and Max vCores is the range in wich the compute will be scaled and billed, but I can get: CPU Used Memory Used Duration. ¿Is this the auto pause delay (link)? I hope you can help me to understand Best regards Jona
IP Range to Allow SharePoint Online Connect to Azure SQL Database
Hello, I'm having issues pulling data from an Azure SQL DB, from SharePoint Online. We're not comfortable with allowing all IPs, so I'll like to know what IP range we need to allow at the firewall to allow for the data pull. Below is the error I get " Cannot connect to the LobSystem (External System). Reason: 'Cannot open server 'server-name' requested by the login. Client with IP address 'IP address' is not allowed to access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect." Any help will be greatly appreciated!
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
Hosted SQL advice please
I’m looking to migrate away from our on prem DC and separate SQL server into Azure. My plan is to join our Windows 10 clients to our existing Azure domain (AAD), continue to use Exchange online for email and move files and folders to OneDrive. All of the above I’m happy with so far. The bit I’m unsure about is our SQL databases which our client software uses. I know I can create a SQL instance in Azure and potentially migrate our data across to it but am concerned about latency between the client app and the azure sql instance. We have a 100mb/100mb leased line as our internet link. any advice on this please
Azure Query Editor
I'm trying (just to have a look around) to connect to my Azure DB by using the query editor in the portal. I am having problems connecting as I get the error: A connection to the server 'xxxx.database.windows.net' could not be established. This might indicate an issue with your local firewall configuration or your network proxy settings. I get the same message if I try either SQL or AD authentication. I am able to connect to the DB if I use SSMS on my PC so I'm not sure why it mentions outgoing ports?
