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!
