Issues Upgrading linked service for PostgreSQL - Data Factory

Question

I´m using the legacy LS for Postres SQL (SHIR) and the connection is working fine, with no issues until today. I need to update the linked service (MS recomend to do it until 31/10/2024) but I´m receiving an error while testing the connection for the new one.

Everythin is passing via an Self-Hosted Integration Runtime installed on a local virtual machine.

Here´s the LS legacy json running ok:

{
    "name": "telemetria",
    "type": "Microsoft.DataFactory/factories/linkedservices",
    "properties": {
        "type": "PostgreSql",
        "typeProperties": {
            "server": "abcdefgh.rds.amazonaws.com",
            "database": "mydb",
            "username": "myuser",
            "encryptedCredential": "------------------"
        },
        "connectVia": {
            "referenceName": "shir",
            "type": "IntegrationRuntimeReference"
        },
        "annotations": []
    }
}

I'm trying to create with the new connector, but the connection test it´s failing.

Code:

{
    "name": "telemetria_new",
    "type": "Microsoft.DataFactory/factories/linkedservices",
    "properties": {
        "annotations": [],
        "type": "PostgreSqlV2",
        "typeProperties": {
            "server": "abcdefgh.rds.amazonaws.com",
            "port": 5432,
            "database": "mydb",
            "username": "myuser",
            "sslMode": 0,
            "authenticationType": "Basic",
            "encryptedCredential": "------------------"
        },
        "connectVia": {
            "referenceName": "shir",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Info:

• Error code: 11402
• Details: The value of the property 'passwordmypassword;ssl mode' is invalid: 'Couldn't set passwordmypassword;ssl mode Parameter name: passwordmypassword;ssl mode'. Couldn't set passwordmypassword;ssl mode Parameter name: passwordmypassword;ssl mode The given key was not present in the dictionary.
• Additional info: the text 'mypassword' is the current password

I´m not the dba.

Connection from SelfHosted Integration Runtime is ok for that serve, database, user and password.

Can someone help me please?

Thanks in advance!

Answer 1

The issue you're encountering with the new PostgreSqlV2 connector in Azure Data Factory could be due to how the password and SSL mode are being passed. Based on the error message, it seems like the new connector might be parsing the password and SSL mode as a single string, which is likely causing the problem.

Here are a few things to check and update:

1. SSL Mode Setting: In the new PostgreSqlV2 connector, the SSL mode must be correctly specified. Instead of using sslMode: 0, you should use one of the recognized SSL modes: Disable, Require, VerifyCA, or VerifyFull. For example:

   
      "sslMode": "Disable"
   
   

2. Password Field: Make sure that the password is passed correctly in the encryptedCredential field. Azure Data Factory encrypts this field, so ensure that it is correctly generated. You may need to regenerate the encrypted credential using the Azure Data Factory portal or any method provided by ADF.
3. Remove extraneous properties in password: The error suggests that something like "passwordmypassword;ssl mode" is being passed, which likely means there is a formatting or parsing issue in your configuration. Ensure that the encryptedCredential does not include SSL or other parameters.

Here’s a revised version of your JSON configuration for the linked service:

{

    "name": "telemetria_new",

    "type": "Microsoft.DataFactory/factories/linkedservices",

    "properties": {

        "annotations": [],

        "type": "PostgreSqlV2",

        "typeProperties": {

            "server": "abcdefgh.rds.amazonaws.com",

            "port": 5432,

            "database": "mydb",

            "username": "myuser",

            "sslMode": "Disable",  // Adjust SSL mode as per your requirement

            "authenticationType": "Basic",

            "encryptedCredential": "------------------"

        },

        "connectVia": {

            "referenceName": "shir",

            "type": "IntegrationRuntimeReference"

        }

    }

}

Next Steps:

1. Regenerate your encryptedCredential to make sure it’s correctly encoded.
2. Adjust the sslMode to match your database requirements.
3. Test the connection again after these changes.

Let me know how it goes!

Answer 2

Thank you very much for the answer Amira!

Unfortunately the option to alter the 'sslmode' to receive a string returns error as it is configured to receive integer. This is something I had already tried.

What did give me an important clue to go deeper was the password issue, and that there was nothing corrupt in the encryption. So I asked the company's dba for a new user and password for the database, and everything worked correctly. I am now connected and it works fine.

Thank you very much again for the help and the quick response!

Translated with DeepL.com (free version)