Upgrade linked service for PostgreSQL - Data Factory

Question

Currently I´m using the legacy LS for Postres SQL (SHIR) and the connection is working fine. 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.

Here´s the LS legacy, using encryption method SSL and Not validate server certificate:

{
    "name": "PostgreSqlLegacy",
    "properties": {
        "annotations": [],
        "type": "PostgreSql",
        "typeProperties": {
            "connectionString": "Host=xxx.postgres.database.azure.com;Port=5432;Database=xxx;UID=xx;EncryptionMethod=1;ValidateServerCertificate=0",
            "password": {
                "type": "AzureKeyVaultSecret",
                "store": {
                    "referenceName": "KEYVAULT",
                    "type": "LinkedServiceReference"
                },
                "secretName": "xx"
            }
        },
        "connectVia": {
            "referenceName": "SHIR",
            "type": "IntegrationRuntimeReference"
        }
    }
}

I´m trying to create the new one with sslMode: Required and trustServerCertificate = True like this

{
    "name": "PostgreSql1",
    "properties": {
        "annotations": [],
        "type": "PostgreSqlV2",
        "typeProperties": {
            "server": "xxx.postgres.database.azure.com",
            "port": "5432",
            "database": "xx",
            "username": "xx",
            "password": {
                "type": "AzureKeyVaultSecret",
                "store": {
                    "referenceName": "KEYVAULT",
                    "type": "LinkedServiceReference"
                },
                "secretName": "xx"
            },
            "sslMode": 3,
            "authenticationType": "Basic",
            "trustServerCertificate": true
        },
        "connectVia": {
            "referenceName": "SHIR",
            "type": "IntegrationRuntimeReference"
        }
    }
}

But the connection test it´s failing. Thisis the error message:

Exception while performing SSL handshake Check network connectivity setting of your Azure Postgre SQL flexible server, if Private Access(VNet integration) is selected, please use SHIR to connect.
Exception while performing SSL handshake
A call to SSPI failed, see inner exception.
The message received was unexpected or badly formatted

Can someone help me please?

Answer 1

I think you need to verify if your PostgreSQL SSL/TLS configuration matches what is expected by

the linked service configuration because from the message I can see that you have handshake issues.

Since you are using SHIR, verify that it has the necessary network connectivity and permissions to access your PostgreSQL server. Ensure that the SHIR is up-to-date and configured correctly to handle SSL connections.

Can you try the following ?

{

    "name": "PostgreSql1",

    "properties": {

        "annotations": [],

        "type": "PostgreSqlV2",

        "typeProperties": {

            "server": "xxx.postgres.database.azure.com",

            "port": "5432",

            "database": "xx",

            "username": "xx",

            "password": {

                "type": "AzureKeyVaultSecret",

                "store": {

                    "referenceName": "KEYVAULT",

                    "type": "LinkedServiceReference"

                },

                "secretName": "xx"

            },

            "sslMode": "require",  // Ensure this is set correctly

            "authenticationType": "Basic",

            "trustServerCertificate": true  // Ensure this is set correctly

        },

        "connectVia": {

            "referenceName": "SHIR",

            "type": "IntegrationRuntimeReference"

        }

    }

}