This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.
User may get the following security alert on certificate resigning SSL when trying to connect with any of the AAD options from SSMS:
"Revocation information for the security certificate for this site is not available. Do you want to proceed?"
Yes \ No \ View Certificate
This happens when a client using Internet Explorer (IE) sends a request to an Online Certificate Status Protocol (OCSP ) server to verify if the certificate has been revoked. If the IE browser is configured to expect an OCSP response and it's not able to determine the revocation status of the certificate, the user gets prompted with the above security alert. Chrome is not affected because it disabled OCSP checks by default in 2012, due to latency and privacy issues.
Mitigation steps - in case you are using Azure MFA:
To fix Server certificate revocation failed problems, a workaround is to turn off this setting - "Check for server certification revocation" in IE options, which will disable this for all OAUTH negotiations system-wide. To disable this option, perform the following steps.
- Type gpedit.msc in windows search and click OK.
- Navigate to Computer Configuration > Administrative Templates > Windows Components > Internet Explorer > Internet Control Panel > Advanced Page or Internet Explorer > Tools > Internet options > Advanced
check for server certificate revocation
- Uncheck "Check for server certificate revocation".
- Reboot the server. *IMPORTANT: It takes effect after you restart your computer.
- Remove CRL/OCSP disk cache entries on the client machine. From the Windows command line run:
> certutil -urlcache CRL delete > certutil -urlcache OCSP delete - Perform "Clear SSL state" in Internet Explorer > Internet Options > Content.
-
On the client machine run gpupdate /force in the CMD window to force update the group policy. You can apply the GPO under user configuration, so the corresponding registry change will be under HKEY_CURRENT_USER.
-
Open Registry Editor and go to the path HKEY_CURRENT_USER\SOFTWARE\Policies\Microsoft\Windows\CurrentVersion\Internet Settings\CertificateRevocation with REG_DWORD 0
-
Open IE and check the setting, it should be disabled.
Mitigation steps - in case you are using third-party MFA tool:
Third party MFA enforce some policies in ADFS. Usual process for integrated authentication is that ADFS receives service ticket generated by on-prem ticket granting server, creates signed SAML token and sends it to AAD. In some cases when using third party tools, ADFS refuse service ticket. The solution is to disable third-party MFA for accounts accessing the database and enforce Azure universal MFA. Any third party tools for MFA authentication may currently not be supported by all tools and applications i.e. SSMS.
Troubleshooting connectivity with AAD options:
Open a PowerShell with administrative rights from the troublemaking machine and run below commands.
#OPTION 1 - bypass SQL Azure DB to see if your communication works with Azure AD from your machine
and check the federated authentication group
You should see what is stored in Azure AD under a specific user or group alias/name.
#OPTION 2 - Check the minimum connectivity requirement
Check connectivity to AAD endpoint for Password and Integrated authentication:
Check connectivity to AAD endpoint for Universal with MFA authentication:
Note that additional endpoints might be required, depending on AAD and on-premises AD setup. Capturing and debugging network or Fiddler traces is what usually helps in those situations.
Additional points to check - Make sure the firewall configuration is correctly set up
- Check your firewall settings and make sure it allows communication with the above AAD endpoints: login.windows.net and login.microsoftonline.com.
- Ensure the AAD required ports are not blocked by the firewall.
Note: The above error is mostly triggered when using SSMS. Azure Data Studio doesn’t have this issue because it has a custom MFA implementation that doesn't use an old embedded IE browser.