Assistance Needed: Error Setting Up Azure Linked Service for On-Premises SQL Server

Harikrishnan Arayakeel 20

Hi, I am trying to set up an Azure Linked Service to connect to an on-premises SQL Server. I attempted to use the SQL Server connector but encountered an error. I have attached the error details for your reference.

The settings I used are as follows:

Integration Runtime: AutoResolveIntegrationRuntime
Version: Recommended Attaching image: 41.png

Could you please assist in resolving this issue?

Sai Raghunadh M 1,605 Reputation points Microsoft Vendor

2024-12-16T02:35:38.22+00:00

Hi @Harikrishnan Arayakeel

Just checking in to see if the below answer provided by @VINODH KUMAR T D helped.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Harikrishnan Arayakeel 20 Reputation points

2024-12-16T11:00:02.81+00:00

Hello,

I had a catch-up with my team today, and I have responded to the queries with Vinodh Kumar.

2 answers

VINODH KUMAR T D 26,141 Reputation points MVP

2024-12-13T17:46:38.03+00:00
Hi ,

Thanks for reaching out to Microsoft Q&A.

The error you’re encountering typically indicates a configuration issue with the linked service in ADF:

Check Auth Settings:

Ensure you have provided the correct authentication method (ex: managed identity, service principal, or SQL authentication) based on your Azure SQL Database or other linked service requirements.

If using a managed identity, verify that it has the required permissions on the target resource (ex: Azure SQL or Storage).

Validate Connection Details:

Double-check the server name, database name, and any other credentials.

Ensure the server firewall rules allow access from ADF’s IP address or that the ADF is integrated with a VNet that has access.

Enable Integration Runtime:

Confirm that the correct IR is selected and configured properly. For Azure SQL DB, an Azure IR is usually sufficient.

Test the Connection:

Use the "Test Connection" feature in ADF to verify if the linked service can connect successfully. This will often provide more specific error messages if something is misconfigured.

Firewall and Network:

Ensure your database or service has the required firewall rules to allow ADF access.

If these steps don’t resolve the issue, providing specific details of the error message or the linked service configuration can help narrow down the problem further.

Please feel free to click the 'Upvote' (Thumbs-up) button and 'Accept as Answer'. This helps the community by allowing others with similar queries to easily find the solution.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Harikrishnan Arayakeel 20 Reputation points

2024-12-16T09:56:08.3166667+00:00

Hi, I have checked these setting with our team,

Check Authentication Settings: Confirmed with IT that the correct authentication method is configured.

Validate Connection Details: Confirmed the server name, database name, and credentials with IT.

I’m not sure how to verify if the server’s firewall rules allow access from ADF’s IP address or if ADF is integrated with a VNet that has access ?

Enable Integration Runtime: We are using Azure Integration Runtime (IR), and it’s already working successfully with other connections for testing.

Firewall and Network: How can I confirm if the database or service has the required firewall rules to allow ADF access ? Looking towards for your response
Please sign in to rate this answer.
Sai Raghunadh M 1,605 Reputation points Microsoft Vendor

2024-12-17T02:45:14.8333333+00:00

Hi @Harikrishnan Arayakeel

For ADF to connect to your on-premises SQL Server, make sure the SQL Server’s firewall allows inbound traffic on the SQL Server port (default is TCP port 1433).

Since you’re trying to connect to an on-premises resource, I recommend using the Self-Hosted Integration Runtime (SHIR) instead of the Azure Integration Runtime (IR). This IR is designed specifically for scenarios where you need to connect to on-premises resources.

Harikrishnan Arayakeel 20 Reputation points

2024-12-17T09:02:46.52+00:00

Hi Sai,

I just checked with IT, and they confirmed that no rules have been created and the firewall for server XSQLXX is currently off.

I attempted to connect via on-premise, but the ODBC creation failed. Could you please clarify which rules need to be applied to/from the host machine to enable this connection?

Sai Raghunadh M 1,605 Reputation points Microsoft Vendor

2024-12-17T09:26:01.9533333+00:00

Hi @Harikrishnan Arayakeel

By default, SQL Server utilizes TCP port 1433 for connections. To enable these connections, please configure a firewall rule on the machine hosting SQL Server to permit inbound traffic on this port.

Open Windows Firewall (or your firewall management tool).

Create a new Inbound Rule to allow TCP traffic on port 1433 (default SQL Server port).

If you are trying to connect from a machine other than the SQL Server, please ensure that the firewall rules permit traffic from this machine to the SQL Server.

Please ensure that the firewall on the machine where the ODBC client or Self-Hosted Integration Runtime (SHIR) is installed permits outbound traffic to the SQL Server on port 1433.

Create an outbound rule for TCP port 1433 on the firewall of the client machine (ODBC host).

Ensure the client machine (ODBC host) can route traffic to the SQL Server, especially if they are on different subnets or if you're using a VPN.

I hope this information helps, please do let us know if you have any Queries.

Harikrishnan Arayakeel 20 Reputation points

2024-12-17T09:43:19.85+00:00

Hi Sai,

For XSQLXX, the firewall is disabled, so rules are not applicable, and I believe it should allow any connections. However, the Azure server is still unable to access XSQLXX. Are there any specific rules that our network team needs to configure to allow this?

Additionally, we’re facing an issue with another server, Despite configuring the specific rules as mentioned for this server (since the firewall is enabled), the Azure ODBC connection still fails to establish.

Harikrishnan Arayakeel 20 Reputation points

2024-12-17T10:04:34.24+00:00

Hi Sai,

For XSQLXX, the firewall is disabled, so rules are not applicable, I believe it should allow any connections. However, the Azure server is still unable to access XSQLXX. Are there any specific rules that our network team needs to configure to allow this?

Additionally, we’re facing an issue with another server, Despite configuring the specific rules as mentioned for this server (since the firewall is enabled), the Azure ODBC connection still fails to establish.

Sai Raghunadh M 1,605 Reputation points Microsoft Vendor

2024-12-18T18:33:33.6633333+00:00

Hi @Harikrishnan Arayakeel
If you have a support plan could you please file a support ticket for deeper investigation and do share the SR# with us?

Sai Raghunadh M 1,605 Reputation points Microsoft Vendor

2024-12-19T19:00:25.61+00:00

Hi @Harikrishnan Arayakeel

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.
Sign in to comment

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

Share via

Assistance Needed: Error Setting Up Azure Linked Service for On-Premises SQL Server

2 answers

Your answer