Copy Data activity from Amazon Redshift via VNet Gateway fails with ODBC Driver Error

[rldekkers]

I have been trying without success to use an Amazon Redshift database as a source in a Copy Data activity, which needs to be done through a VNet gateway, because the database allows only whitelisted IP's.

The Copy Data Activity gives the following error:

Failure happened on 'Source' side. 

ErrorCode=SystemErrorInbandOdbcDriverError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=Microsoft.DataTransfer.ClientLibrary.Odbc.Interop.OdbcException,Message=ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.Wrapper,'

From the Redshift connector docs, I understand this scenario should be supported: (https://learn.microsoft.com/en-us/fabric/data-factory/connector-amazon-redshift-overview#supported-c...  ). I understand this was not the case a few months ago, judging from older forum posts, but the documentation now lists Virtual Network as a supported capability with Redshfit in the copy data activity.

Source configuration:

server: xxx-xxx-xxx-eu-central-1-red-redshiftcluster-xxx.xxx.eu-central-1.redshift.amazonaws.com

database: customer-database (we do not connect to 'dev', because we don't have access at that level)

gateway: [Virtual Network] vnet-fabric-gateway

Troubleshooting we performed:

- ✅ Succesfully connected in a Data Flow using either VNet gateway or on-premises gateway. (on-prem gateway is not available in pipelines, as expected per the docs)

- ✅ Verified that VNet gateway routes traffic through correct IP (connection set-up fails without gateway due to IP restriction, and succesfully connected to on-premises SQL server using data gateway to confirm IP)

- ✅ Succesfully connected to Redshfit database using DBeaver with the Redshift JDBC driver

- ❌ 'Test Connection' in the Redshift source fails with "ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified " 

- ❌ 'Preview data' fails with "InternalError"

Could anyone confirm that this scenario is supported? The errors suggest that the driver may be missing or not functioning. Any help would be appreciated! 

[Anonymous]

Hi @rldekkers,

Thanks for reaching out to the Microsoft fabric community forum. The failure is happening on the source side with an ODBC Driver Manager error (IM002: Data source name not found and no default driver specified). That means the runtime used by Copy Data doesn’t have the Amazon Redshift ODBC driver available. As @Shahid12523 has already responded to your query, kindly go through his response and check if the issue can be resolbed.

I would also take a moment to thank @Shahid12523, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.

[Anonymous]

Hi @rldekkers,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround so that other users can benefit as well.  And if you're still looking for guidance, feel free to give us an update, we’re here for you.

Best Regards,

Hammad.

[Anonymous]

Hi @rldekkers,

Hope everything’s going smoothly on your end. As we haven’t heard back from you, so I wanted to check if the issue got sorted.
Still stuck? No worries just drop us a message and we can jump back in on the issue.

Best Regards,

Hammad.

[Anonymous]

Hi @rldekkers,
We noticed there hasn’t been any recent activity on this thread. If you still need support, just drop a reply here and we’ll pick it up from where we left off.

Best Regards,

Hammad.

[Shahid12523 · Accepted solution]

Solution:

Deploy a self-hosted IR inside your VNet.

Install the Amazon Redshift ODBC driver on that IR machine.

Configure your Copy Data activity to use this IR.

This will fix the driver error and let Copy Data connect via VNet.