CODEWITHSUNDEEP
postgresqlamazon-web-servicesamazon-rdsazure-data-factory
CodeMonkey
CodeMonkey

Reputation: 477

Azure Data Factory AWS PostgreSQL RDS as source connection

I am attempting to use an AWS PostgreSQL RDS instance as my source for a data factory pipeline. I am unable to get this connection to work in ADF v1 or v2. I have tried everything from using a PostgreSQL connection to an Azure database for PostgreSQL. Essentially I am going cloud to cloud, and this connection doesn't seem to be supported yet. Has anyone else had any luck doing this?

Upvotes: 1

Views: 2455

Answers (1)

Jason Horner
Jason Horner

Reputation: 3690

yes, this is horribly broken as you have found out. Two main problems:

1) You must install the NpgSQL 2.0.14.3. driver (chose core installation option to ensure both x86 and x64 versions are installed) this version won't validate the server certificate

2) PostgreSQL connector can only enter connection information using by uploading via PowerShell the current GUI does not support the full configuration of the data source:

Here is the example json:

{
    "name": "PostgreSqlLinkedService",
    "properties": {
        "type": "PostgreSql",
        "typeProperties": {
            "server": "<server>",
            "database": "<database>",
            "username": "<username>",
            "password": {
                "type": "SecureString",
                "value": "<password>"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

alternatively, the ODBC driver can work around this issue as you need to specify additional properties on the connection string that are not exposed by the pg connector. you need to add the following value to the DSN: **sslmode=Require;Trust Server Certificate=true*

and that should resolve the error

note: the ODBC nor the Postgresql Connector's currently work with the ADF v2 Lookup activity.

Upvotes: 1

Related Questions