Within Azure Data Factory Studio, I get a Failed Connection to my SQL Database

Derek Lilley 20

Below is the error I keep getting, I've read all documentation and tried all steps to ensure identification is all there. I'm stuck and have a client deliverable. I would happily pay extra to get over this hurdle as I need to use the data warehouse asap. Thank you!!

Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'wells-analytics.database.windows.net', Database: 'wells-forecast-hub', User: '******@storiedanalytics.onmicrosoft.com'. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access.

Cannot open server "storiedanalytics.onmicrosoft.com" requested by the login. The login failed.

Chandra Boorla 8,795 Reputation points Microsoft Vendor

2025-02-14T05:08:59.3966667+00:00
@Derek Lilley

It looks like your Azure Data Factory (ADF) is using AutoResolveIntegrationRuntime, which does not have a static IP address to whitelist in the SQL Database firewall. Based on your error, here are a few steps to troubleshoot and resolve the issue:

Check Azure AD Authentication for Your SQL Server - Since you’re using the Azure AD login ******@storiedanalytics.onmicrosoft.com, make sure Azure AD authentication is properly configured --> In the Azure Portal, go to SQL Server → Active Directory Admin and confirm that an Azure AD admin is set. If your account isn’t listed, add it and try reconnecting.

Use Managed Identity Authentication in ADF - Instead of username/password, try using Managed Identity authentication in your ADF Linked Service --> In ADF, open your Linked Service to the SQL Database --> Change the Authentication Type to System-Assigned Managed Identity --> In SQL Database, grant permissions:

CREATE USER [your-adf-name] FROM EXTERNAL PROVIDER; ALTER ROLE db_owner ADD MEMBER [your-adf-name];

Test the connection again.

If You Need a Fixed IP, use a Self-Hosted Integration Runtime (SHIR) - Since AutoResolveIntegrationRuntime has a dynamic IP, you might need to use SHIR if IP whitelisting is required. Set up SHIR on an Azure VM or an on-premises machine with a fixed public IP. Add this IP to the SQL Database firewall under "Allowed IPs".

Test with SSMS or Azure Data Studio - To confirm if the issue is with Azure AD authentication, try logging into your SQL Database using SSMS or Azure Data Studio with ******@storiedanalytics.onmicrosoft.com. If login fails, the issue is likely with the Azure AD configuration.

Please refer to the below MS Q&A threads addressing similar issue, as they might offer some insights.

Cannot connect to SQL Database.

Auto Resolve Integration Runtime can't connect to SQL Database

Cannot connect to SQLMI database when using Azure Auto Resolve Integration Runtime

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.

Thank you.
Chandra Boorla 8,795 Reputation points Microsoft Vendor

2025-02-17T01:48:43.93+00:00

@Derek Lilley

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Derek Lilley 20 Reputation points

2025-02-17T05:02:40.77+00:00

Hi Chandra, thank you for the follow-up. I’ve not been able to resolve. But I also don’t have the hours to continue to try & try again, so it’s on the back burner for a weeks or two. I’m already have my data in GCP, so for immediate term this works, but my client wants me in Azure. I would like this resolved by end of Feb ideally. Thank you.
Chandra Boorla 8,795 Reputation points Microsoft Vendor

2025-02-18T15:54:51.8866667+00:00

@Derek Lilley

I completely understand the constraints you're facing with time and the current workaround using GCP. Transitioning to Azure is important for your client, and it's crucial to have a seamless experience. Based on your situation, here are a few additional suggestions that might help you move forward:

Prioritize Managed Identity - If you haven't already, consider setting up Managed Identity for your Azure Data Factory. This approach simplifies authentication and reduces issues related to IP whitelisting and credential management. It may be a quick win without requiring extensive time investment.

Plan for Self-Hosted Integration Runtime (SHIR) - If Managed Identity doesn’t resolve the issue, plan to set up SHIR. This will allow you to use a fixed IP address, which can be whitelisted in the SQL Database firewall settings. You can schedule this setup when you have a dedicated block of time.

I hope this information helps.
Chandra Boorla 8,795 Reputation points Microsoft Vendor

2025-02-19T16:00:43.1733333+00:00

@Derek Lilley

Following up to see if the above suggestion was helpful.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.

Thank you.

1 answer

Sagar Shah 0 Reputation points

2025-02-14T02:01:56.3833333+00:00
It looks like you're encountering a SQL Server connection error, specifically related to authentication and access to your Azure SQL Database. This is a common issue when setting up connections for data integration, particularly with Azure Data Factory.

Check SQL Server Firewall Settings

Ensure that the firewall on your SQL Database allows access from your Integration Runtime (IR).

Go to your SQL Database on the Azure portal.

In the Settings pane, go to Firewalls and virtual networks.

Check if your IP address or the IP range of the Integration Runtime is listed under the allowed IPs.

You can temporarily allow all Azure services to access the database to troubleshoot if the firewall settings are the problem:

In the Firewall settings, enable Allow Azure services and resources to access this server.

If everything works, narrow down the access by specifying the IP range of your Integration Runtime or virtual network.
Please sign in to rate this answer.
Derek Lilley 20 Reputation points

2025-02-14T04:42:05.14+00:00

I have a AutoResolveIntegrationRuntime connection, so the IP address in this area is blank. I don't know how to change this connection type. Also I tried to unsave & resave the 'Allow Azure services and resources to access this server' saved setting, any other hypotheses would be very welcome!
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Within Azure Data Factory Studio, I get a Failed Connection to my SQL Database

1 answer

Your answer