ODBC Connection to Synapse Linked Service

Question

ODBC Connection to Synapse Linked Service

Harikrishnan Arayakeel 25

HI, I am trying to connect an ODBC connection to a Synapse-linked service, but I am encountering the following error: [HYT00] [IBM][System i Access ODBC Driver] Connection login timed out.

Here are the details of my setup:

Integration Runtime: Self-hosted
ODBC Driver: Created an ODBC64 driver for the server using the IP and user details on a self-hosted machine.
Firewall Configuration: Our IT team has been requested to open the Azure Firewall to allow connections from the self-hosted machine to 10.xx.xx.xx via Port xx.

The linked service settings are:

Connection String: DSN=ODBCname
Authentication: Basic

Could you please confirm if there are any mandatory settings or prerequisites that need to be configured before establishing this connection? attaching image.42.png

Ganesh Gurram 7,295 Reputation points Microsoft External Staff Moderator

2024-12-16T07:35:57.03+00:00

@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.

2 answers

Your answer

Ganesh Gurram 7,295 Reputation points Microsoft External Staff Moderator

2024-12-16T07:35:57.03+00:00

@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.

Answer 1

Hi ,

Thanks for reaching out to Microsoft Q&A.

To resolve the issue of using an ODBC connection in an Azure Synapse linked service:

Verify Connection Parameters: Ensure that the server name, database name, and authentication method (Azure Active Directory or SQL authentication) in the ODBC driver are correctly configured.
Firewall Rules: Confirm that your Synapse SQL pool allows the client's IP address by configuring the firewall rules in the Synapse workspace.
Driver Version: Use the latest ODBC driver for SQL Server or Synapse Analytics to avoid compatibility issues.
Linked Service Configuration: In the Azure Data Factory linked service, select the "ODBC" connection type and provide the connection string, user credentials, and any required authentication details.
Test the Connection: Use the "Test Connection" button in the ADF linked service configuration to ensure everything is working properly.
Network Considerations: If your Synapse workspace is in a private network, ensure the client is connected via ExpressRoute, VPN, or a private endpoint.

But pls keep in mind these are generic advises. You should make sure to spend some time to see which solution would best fix your issue.

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.

Harikrishnan Arayakeel 25 Reputation points

2024-12-16T08:52:34.3633333+00:00

Hi,

Checked Connection Parameters: Confirmed these with IT, and the server name, database name, and authentication method have been verified.

Driver Version: We are using the IT-recommended ODBC driver on the self-hosted machine.

Firewall Rules: I’m not sure how to verify these. Could you provide guidance on how to check if the Synapse SQL pool firewall is configured to allow the client’s IP address?

Network Considerations: Could you advise on how we can test the network configuration, particularly in relation to private networks, VPN, or ExpressRoute?

Answer 2

Harikrishnan Arayakeel 25

Hi, I have checked these settings with our team,

Checked Connection Parameters: Confirmed these with IT, and the server name, database name, and authentication method have been verified.

Firewall Rules: I’m not sure how to verify these. Could you provide guidance on how to check if the Synapse SQL pool firewall is configured to allow the client’s IP address?

Driver Version: We are using the IT-recommended ODBC driver on the self-hosted machine.

Network Considerations: Could you advise on how we can test the network configuration, particularly in relation to private networks, VPN, or ExpressRoute? Looking towards for your response

Ganesh Gurram 7,295 Reputation points Microsoft External Staff Moderator

2024-12-17T13:48:35.24+00:00
Hello @Harikrishnan Arayakeel

Thank you for the update.

To check and configure firewall rules for your Synapse SQL pool, follow these steps:

Go to the Azure Synapse Analytics workspace in the Azure portal.

Under the Security section, select Firewalls and virtual networks.

In this section, you can see a list of IP addresses that are allowed to connect to your Synapse SQL pool.

Ensure the IP address of your self-hosted integration runtime machine is added here. You can add it by clicking Add client IP or manually entering the required IP address.

If you are using a Virtual Network (VNet) for private connections, make sure the VNet and subnet settings allow inbound traffic to your Synapse workspace.

To confirm your network configuration and connectivity:

Private Endpoint - If your Synapse workspace is connected via a Private Endpoint, ensure that the self-hosted integration runtime machine can access this private endpoint. You can test this by using the ping or telnet command from the self-hosted machine to the Synapse private endpoint’s IP address on the required port.

VPN/ExpressRoute - If you're using VPN or ExpressRoute, ensure that the self-hosted machine is properly connected to the Azure network. You can test the connection by running a ping or traceroute command from the self-hosted machine to the Synapse IP address.

Checking if the relevant ports (for example, 1433 for SQL connections) are open on both ends of the VPN or ExpressRoute connection.

Increase Timeout Settings - If network latency is an issue, you may want to adjust the connection timeout settings in the ODBC driver configuration. In the DSN settings, you can set a higher timeout value (e.g., 30 seconds or more) to account for any delay in establishing the connection.

Hope this helps. Do let us know if you have any further queries.
Harikrishnan Arayakeel 25 Reputation points

2024-12-18T09:26:45.4033333+00:00
Hi @Ganesh Guram

I performed some tests and configurations to resolve the connectivity issue:

Network Rules:

Added rules in Synapse Workspace > Networking.

Included the IP address of the self-hosted integration runtime machine.

Entered the start IP with IPv4 and the end IP with the subnet mask.

Added the list of IPs that need to be allowed.

Port Check:
- How can we verify if the necessary ports (e.g., 1433 for SQL connections) are open on both ends of the VPN or ExpressRoute connection?

Private Connection Settings:

Currently, our private endpoint connections are blank.

I’m unsure if the workspace is using a Private Endpoint or VPN/ExpressRoute. Could you guide me on how to test this?

Domain Issue Identified:

The SQL connection we want to establish is in a different domain than Synapse. The Synapse test connection fails in this scenario.

Is there any specific rule or configuration needed to make this connection active? Interestingly, we can connect to the SQL server via SSMS using the same credentials.

Looking forward to your advice on resolving these issues.
Ganesh Gurram 7,295 Reputation points Microsoft External Staff Moderator

2024-12-18T10:29:36.1933333+00:00

@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?
Harikrishnan Arayakeel 25 Reputation points

2024-12-18T15:30:54.57+00:00

Hi @ganesh gurram , I'm catching up with our IT to understand support level with Microsoft. Will share the update once ticket is in progress.

Share via

ODBC Connection to Synapse Linked Service

2 answers

Your answer