Principle 'XYZ' could not be found or this principal type is not supported - Azure SQL DB and MI
In this blog article, we will be discussing the possible scenarios to get the error "Principle 'XYZ' could not be found or this principal type is not supported" while adding an AAD user or group to your Azure SQL database or Azure SQL managed instance.
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 DocsLesson Learned #380:Unable to load adalsql.dll (Authentication=XYZ).Error code:0x2
Analyzing a support case that our customer got the following error faced: "Unable to load adalsql.dll (Authentication=ActiveDirectoryPassword). Error code: 0x2" using an older application to the Azure SQL database. Following I would like to share my lessons learned about it.Lesson Learned #374: Execution of class com.microsoft.aad.adal4j.AcquireTokenCallable failed.
Today, we encountered an interesting case while obtaining the Azure Active Directory token using ActiveDirectoryPassword authentication using the latest version of MSAL in Java. Finally, the issue was resolved by opening the port 443 and the necessary endpoints to obtain the Azure Active Directory token. Following, I would like to share the lessons learned of this troubleshooting.Azure SQL Database Token-based authentication with PowerShell
First published on MSDN on Oct 26, 2018 How to connect to Azure SQL Database using token-based authentication in PowerShell native appsThis guide assumes you already have a deployment of an Azure SQL Database, your PowerShell environment configured and you have an app registration for a native app in Azure Active Directory.How to create a native PowerShell app registration to connect to Azure SQL DB
First published on MSDN on Oct 30, 2018 How to create a native app registrationThis guide assumes that you already own an Azure Subscription, an Azure Active Directory properly setup and your PowerShell environment properly set for running scripts.
