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.
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.
> certutil -urlcache CRL delete > certutil -urlcache OCSP delete
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.
Note: When connecting through ADFS, 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.
Troubleshooting AAD connectivity issues
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
> Install-Module MSOnline > Import-Module MSOnline > $Msolcred = Get-credential # use your federated credenaials (i.e firstname.lastname@example.org + password) > Connect-MsolService -Credential $MsolCred
and check the federated authentication group
> Get-MsolGroup -MaxResults 10 –Searchstring email@example.com | format-list # displays group info as it is represented in Azure AD (i.e. mygroup or check the individual user) > Get-MsolUser -UserPrincipalName firstname.lastname@example.org | format-list
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:
> tnc login.windows.net -port 443
Check connectivity to AAD endpoint for Universal with MFA authentication:
> tnc login.microsoftonline.com -port 443
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.