Azure Data Factory - Copy data from AWS Redshift to Azure Synapse - Failed to connect error

Question

Hi,

I am trying to copy a table of data from AWS Redshift into Azure Synapse, below is the JSON code from the data factory:

{
    "name": "pipeline1",
    "properties": {
        "activities": [
            {
                "name": "Copy data1",
                "type": "Copy",
                "dependsOn": [],
                "policy": {
                    "timeout": "0.12:00:00",
                    "retry": 0,
                    "retryIntervalInSeconds": 30,
                    "secureOutput": false,
                    "secureInput": false
                },
                "userProperties": [],
                "typeProperties": {
                    "source": {
                        "type": "AmazonRedshiftSource",
                        "redshiftUnloadSettings": {
                            "s3LinkedServiceName": {
                                "referenceName": "AmazonS31",
                                "type": "LinkedServiceReference"
                            },
                            "bucketName": "matillion-data-dumps"
                        }
                    },
                    "sink": {
                        "type": "SqlDWSink",
                        "allowPolyBase": true,
                        "polyBaseSettings": {
                            "rejectValue": 0,
                            "rejectType": "value",
                            "useTypeDefault": true
                        }
                    },
                    "enableStaging": true,
                    "stagingSettings": {
                        "linkedServiceName": {
                            "referenceName": "DataLakeGen2pbiuksouthuk",
                            "type": "LinkedServiceReference"
                        },
                        "path": ""
                    },
                    "logSettings": {
                        "enableCopyActivityLog": true,
                        "copyActivityLogSettings": {
                            "logLevel": "Warning",
                            "enableReliableLogging": false
                        },
                        "logLocationSettings": {
                            "linkedServiceName": {
                                "referenceName": "DataLakeGen2pbiuksouthuk",
                                "type": "LinkedServiceReference"
                            }
                        }
                    }
                },
                "inputs": [
                    {
                        "referenceName": "AmazonRedshiftTable1",
                        "type": "DatasetReference"
                    }
                ],
                "outputs": [
                    {
                        "referenceName": "AzureSynapseAnalyticsTable1",
                        "type": "DatasetReference"
                    }
                ]
            }
        ],
        "annotations": []
    }
}

I get the following error after 30 mins of running the copy process:

ErrorCode=SqlFailedToConnect,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Cannot connect to SQL Database. Please contact SQL server team for further support. Server: '', Database: 'PowerBI', User: 'sqladminuser'. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server),Source=.Net SqlClient Data Provider,SqlErrorNumber=2,Class=20,ErrorCode=-2146232060,State=0,Errors=[{Class=20,Number=2,State=0,Message=A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server),},],''Type=System.ComponentModel.Win32Exception,Message=The system cannot find the file specified,Source=,'

What could this be related to?

Answer 1

@Vinny Prajapati

Thank you for posting your query!

The error you are encountering while trying to copy data from AWS Redshift to Azure Synapse suggests that there is a connectivity issue with the SQL Database.

Here are some potential causes and solutions:

Linked Service Configuration: Ensure that the linked service configuration for your Azure Synapse Analytics is correct. Check the server name, database name, and user credentials.

Firewall Settings: Verify that the SQL Database firewall settings allow access from the integration runtime being used. You may need to add the IP address of the integration runtime to the allowed list.

Remote Connections: Make sure that the SQL Server is configured to allow remote connections. This can be checked in the SQL Server Management Studio under server properties.

Network Issues: The error message indicates a possible network-related issue. Ensure that there are no network restrictions or outages that could be affecting connectivity to the SQL Database.

SQL Server Instance: Confirm that the SQL Server instance is running and accessible. If it is a named instance, ensure that the instance name is specified correctly.

By addressing these areas, you should be able to resolve the connectivity issue.

For more detailed guidance please refer to the below official documentations:

• A network-related or instance-specific error occurred while establishing a connection to SQL Server
• copying data from Amazon Redshift using Azure Data Factory or Synapse Analytics

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.