Cannot connect to Azure SQL DB from on-premise ADFS joined device

Question

Hi,

We have joined our Azure AD to our on premise Active Directory with ADFS. We would like to access a Azure SQL Db from an on premise device using Azure Integrated Authentication. 

When we attempt to connect within SSMS using the azure integrated option an error is returned saying "The attempt to connect to the server failed".

We have already been through the steps of creating an Admin user on the Azure sql server and then creating a contained database user. In our case we used a group that was created on premise. Note that we are not using a managed instance, it is just a azure sql database.

Using AD Connect I have run trouble shooting on the user we are trying to connect and no errors were returned so there is not a upn mismatch.

Any help to diagnose this issue would be appreciated.

Thanks
Kelly

Answer 1

Hi Kelly,

Firstly, do you have any error message or detail to share? 

Secondly, does any other authentication method work, such as Universal with MFA?

When you authenticate with a contained database user, you must pass the database value that user has been added to in the connection string, or in the case of SSMS, navigate to Connection Properties and ensure the Connect to database value is not <default> (see below).

If there is a firewall issue, you should experience a specific error that indicates the Public IP you are attempting to connect from is not present in the Allow List. 

If you are attempting to connect using Integrated AD Auth, is the host where SSMS is installed a domain joined host?

If you have a specific error message, that will provide insight into where we need to focus the troubleshooting process. With the information here we should get an idea of what the issue is. 

Regards,

Mike

Answer 2

Hi Kelly,

I wanted to see if you are able to move forward or if you have additional questions. If you found the solution to your issue, can you please post the root cause as it is helpful to others who are experiencing the same behavior.

Thank you,

Mike