Edit

Share via

Connect Azure Database for PostgreSQL to Azure Data Factory and Synapse Analytics

APPLIES TO: Azure Database for PostgreSQL - Flexible Server

Important

The Azure Database for PostgreSQL version 2.0 provides an improved native Azure Database for PostgreSQL support. If you use the Azure Database for PostgreSQL version 1.0 in your solution, you should upgrade your Azure Database for PostgreSQL linked service at your earliest convenience.

What is Azure Data Factory

Azure Data Factory is a fully managed, serverless, data integration service built to orchestrate and operationalize complex hybrid extract-transform-load (ETL), extract-load-transform (ELT), and data integration projects. An Azure integration runtime supports connecting data stores and computing services with publicly accessible endpoints. If you enable the managed virtual network feature of an Azure integration runtime, it supports connecting to data stores using Azure Private Link service in private network environments.

Azure Data Factory offers an Azure Database for PostgreSQL connector with support for various capabilities, depending on the integration runtime selected.

Linked service

In Azure Data Factory, a linked service is a connection to a data source. When working with Azure Database for PostgreSQL, you can define a linked service using JSON to specify the connection details programmatically. This approach is useful for automation, version control, and deployment scenarios. The JSON definition includes properties such as the server name, database name, authentication type, and other connection parameters. By creating a linked service, you can easily connect to your Azure Database for PostgreSQL instance and perform data integration tasks within Azure Data Factory.

You can create a linked service using the Azure Data Factory UI or programmatically using JSON. The linked service allows you to connect to your Azure Database for PostgreSQL instance and perform data integration tasks within Azure Data Factory.

Prerequisites

Create a linked service via the Azure portal

You can create a linked service using the Azure Data Factory UI or programmatically using JSON. The linked service allows you to connect to your Azure Database for PostgreSQL instance and perform data integration tasks within Azure Data Factory.

  1. Browse to the Manage tab in your Azure Data Factory or Synapse workspace and select Linked Services, then select New:

  2. From the linked services page, create a new linked service by selecting + New. It opens a window with a list of all Azure Data Factory connectors. Search for Azure Database for PostgreSQL, select it, and then select Continue.

    Screenshot of Searching for 'Azure Database for PostgreSQL' in new linked service field.

  3. Make sure that Version 2.0 is selected.

    Screenshot of New linked service window for Azure Database for PostgreSQL connector.

Authentication

There are four supported methods for authentication: basic authentication, service principal, System-assigned managed identity and User-assigned managed identity.

Basic Authentication

  1. Select Basic auth as the Authentication type and make sure to enter your Azure PostgreSQL flexible server connection details, including Server name, Username, and Password.

    Screenshot of a new linked service window for Azure Database for PostgreSQL connector with basic authentication type.

  2. Select between From Azure subscription or Enter manually in Account selection method

    Select the Azure subscription, Server name, and Database name. Also, enter the Port.

    Screenshot of Account selection method Azure subscription.

  3. Enter the Username and Password.

    Screenshot of Username and password.

  4. Now you can Test the connection and create the linked service

Service principal authentication

Following the service principal authentication setup steps requires setting up a linked service in Azure Data Factory or Synapse Analytics to connect to your Azure Database for PostgreSQL. The process involves selecting the appropriate authentication method, configuring connection details, and verifying the connection. Ensure you have the necessary prerequisites and permissions before proceeding.

  1. Register a Microsoft Entra app and create a service principal.

  2. Select Service Principal on Authentication type.

    There are two types of Service principal credential types, and both service principal methods require a "Tenant", "Service principal ID", and "Azure Cloud Type" values.

    Screenshot of New linked service window for Azure Database for PostgreSQL connector with service principal authentication type.

  3. Select between From Azure subscription or Enter manually in Account selection method.

    Select the Azure subscription, Server name, and Database name. Also, enter the Port.

    Screenshot of Account selection method Azure subscription.

  4. Enter your Service Principal Name, also shown as the Display Name for your service principal key.

  5. Select Inline in the Authentication reference method.

    Screenshot of Service principal inline authentication reference method.

  6. Enter the Tenant. The tenant ID is in Azure, where the Service Principal Name was created.

    Screenshot of Service principal tenant on Azure.

  7. Enter the Service principal ID. You can find the client ID in Azure, where the Service Principal Name was created.

    Screenshot of Service principal ID on Azure.

  8. Select Service Principal Certificate or Service Principal Key in the Service principal credential type.

    Service principal certificate authentication requires a service principal certificate and optionally a service principal password.

    Screenshot of New linked service window for Azure Database for PostgreSQL connector with service principal certificate authentication type.

  9. Select your Azure cloud type.

  10. Now you can Test the connection and create the linked service

System-assigned Managed Identity authentication

Using System-assigned managed identity as the authentication type in the linked service in Azure Data Factory or Synapse Analytics to connect to your Azure Database for PostgreSQL requires the following steps. The process involves selecting the appropriate authentication method, configuring connection details, and verifying the connection. Ensure you have the necessary prerequisites and permissions before proceeding.

  1. In your Azure Database for PostgreSQL resource under Security

    1. Select Identity

    2. Make sure that System assigned managed identity is On

      Screenshot of the system assigned managed identity configuration in the Azure database for PostgreSQL server resource.

  2. In your Azure Data Factory resource,

    1. Select Settings and then Managed Identities

    2. Under the System assigned tab Make sure that status is On

      Screenshot of the system assigned managed identity configuration in the Azure Data Factory resource.

  3. In your Azure Database for PostgreSQL resource under Security

    1. Select Authentication

    2. Select either Microsoft Entra authentication only or PostgreSQL and Microsoft Entra authentication Authentication method.

    3. Select + Add Microsoft Entra administrators

    4. Add the system-assigned managed identity for the Azure Data Factory resource as one of the Microsoft Entra Administrators

      Screenshot of adding the Azure Data Factory system assigned managed identity configuration in the Azure Database for PostgreSQL resource.

  4. In the Linked Service of the Azure Data Factory, select System-assigned managed identity as the Authentication type.

    Screenshot of the system assigned managed identity selected.

  5. Select between From Azure subscription or Enter manually in Account selection method.

    Select the Azure subscription, Server name, and Database name. Also, enter the Port.

    Screenshot of Account selection method Azure subscription.

  6. Now you can Test the connection and create the linked service

User-assigned Managed Identity authentication

Using User-assigned managed identity as the authentication type in the linked service in Azure Data Factory or Synapse Analytics to connect to your Azure Database for PostgreSQL requires the following steps. The process involves selecting the appropriate authentication method, configuring connection details, and verifying the connection. Ensure you have the necessary prerequisites and permissions before proceeding.

  1. Create a User-assigned Managed Identity resource on Azure portal. To learn more, go to Manage user-assigned managed identities

  2. Assign the User-assigned Managed Identity to your Azure database for PostgreSQL resource

    1. In your Azure database for PostgreSQL server resource, under Security

    2. Select Authentication

    3. Select either Microsoft Entra authentication only or PostgreSQL and Microsoft Entra authentication Authentication method.

    4. Click on + Add Microsoft Entra administrators and select your user-assigned managed identity

      Screenshot of the user-assigned managed identity configuration in the Azure database for PostgreSQL server.

  3. Assign the User-assigned Managed Identity to your Azure Data Factory resource

    1. Select Settings and then Managed Identities

    2. Under the User assigned tab. Click on the + Add and select your user-managed identity

      Screenshot of the user-assigned managed identity configuration in the Azure Data Factory resource.

  4. In the Linked Service of the Azure Data Factory, select User-assigned managed identity as the Authentication type

    Screenshot of the user assigned managed identity selected under the authentication type in the linked service.

  5. Select between From Azure subscription or Enter manually in Account selection method.

    Select the Azure subscription, Server name, and Database name. Also, enter the Port.

    Screenshot of Account selection method Azure subscription.

  6. On Credentials or select a user-assigned managed identity credential or create a new one with + New

    Screenshot of the credentials drop menu selected.

    1. If you need to create a new credential, select + New

    2. Make sure that Type is User-assigned managed identity

    3. Select your Azure subscription

    4. In User-assigned managed identities. Select under Assigned to Data Factory your managed identity and click on Create

      Screenshot of the Create credential with your user managed identity selected.

  7. Now you can Test the connection and create the linked service

Test Connection

  1. Once all required connection fields are filled in, the bottom right-hand corner should display a Test connection button. The test connection button verifies that the linked service can connect to your Azure Database for PostgreSQL server.

    Screenshot of New linked service window for Azure Database for PostgreSQL connector test connection.

  2. Click on Create button

Linked service JSON

The following table describes the properties of the linked service for Azure Database for PostgreSQL flexible server. The properties are defined in JSON format, which is used to create the linked service programmatically.

Property Description Required
name Name of the linked service. See Naming rules Yes
type Type of the linked service. It should be AzurePostgreSql Yes
server Full qualified host name for Azure database for PostgreSQL flexible server Yes
port The Azure database for PostgreSQL flexible server port number Yes
database Database name Yes
sslMode A numeric value representing the SSL connection configuration. 0 for Disabled, 1 for Allow, 2 for Prefer, 3 for Require, 4 for VerifyCA and 5 for VerifyFull Yes
authenticationType Specify the authentication to be used. BasicAuth, ServicePrincipal, SystemAssignedManagedIdentity, or UserAssignedManagedIdentity Yes
credential Specify the user-assigned managed identity as the credential object. Required for UserAssignedManagedIdentity. Otherwise isn't required
username username for basic auth or Service principal name for service principal authentication Yes
password Username password for the Basic Auth Required when BasicAuth. Otherwise isn't required
tenant Tenant ID Required for ServicePrincipal authentication type
servicePrincipalId Service Principal ID Required for ServicePrincipal authentication type
servicePrincipalCredentialType Service Principal Type. ServicePrincipalCert or ServicePrincipalKey Yes
servicePrincipalEmbeddedCert The service principal certificate Required when ServicePrincipalCert. Otherwise isn't required
servicePrincipalEmbeddedCertPassword The service principal certificate password No
servicePrincipalKey The service Principal key Required if ServicePrincipalKey is the servicePrincipalCredentialType. Otherwise isn't required

Depending on the type of authentication, different fields require a different JSON payload.

Here are JSON configuration examples for different authentication methods, including Basic Authentication, Service Principal Certificate, and Service Principal Key. These JSON templates can be customized to suit your specific requirements.

A linked service using BasicAuth is defined in JSON format as follows:

{
    "name": "<Name of the linked service>",
    "properties": {
        "type": "AzurePostgreSql",
        "version": "2.0",
        "typeProperties": {
            "server": "<server host name>",
            "port": 5432,
            "database": "<database name>",
            "sslMode": 2,
            "username": "<Service Principal Name>",
            "authenticationType": "BasicAuth",
            "password": "<username password>"
        }
    }
}

A linked service using Service Principal certificate is defined in JSON format as follows:

{
    "name": "<Name of the linked service>",
    "properties": {
        "type": "AzurePostgreSql",
        "version": "2.0",
        "typeProperties": {
            "server": "<server host name>",
            "port": 5432,
            "database": "<database name>",
            "sslMode": 2,
            "username": "<Service Principal Name>",
            "authenticationType": "ServicePrincipal",
            "tenant": "<Tenant ID>",
            "servicePrincipalId": "<SP ID>",
            "servicePrincipalCredentialType": "ServicePrincipalCert",
            "servicePrincipalEmbeddedCert": "<Embedded certificate>",
            "servicePrincipalEmbeddedCertPassword": "<Service Principal certificate password>"
        }
    }
}

A linked service using Service Principal key is defined in JSON format as follows:

{
    "name": "<Name of the linked service>",
    "properties": {
        "type": "AzurePostgreSql",
        "version": "2.0",
        "typeProperties": {
            "server": "<server host name>",
            "port": 5432,
            "database": "<database name>",
            "sslMode": 2,
            "username": "<Service Principal Name>",
            "authenticationType": "ServicePrincipal",
            "tenant": "<Tenant ID>",
            "servicePrincipalId": "<SP ID>",
            "servicePrincipalCredentialType": "ServicePrincipalKey",
            "servicePrincipalKey": "<Service Principal Key>"
        }
    }
}

Example with system-assigned managed identity:

{
    "name": "AzurePostgreSqlLinkedService",
    "type": "Microsoft.DataFactory/factories/linkedservices",
    "properties": {
        "annotations": [],
        "type": "AzurePostgreSql",
        "version": "2.0",
        "typeProperties": {
            "server": "<server name>",
            "port": 5432,
            "database": "<database name>",
            "sslMode": 2,
            "authenticationType": "SystemAssignedManagedIdentity"
        }
    }
}

Example with user-assigned managed identity:

{
    "name": "AzurePostgreSqlLinkedService",
    "type": "Microsoft.DataFactory/factories/linkedservices",
    "properties": {
        "annotations": [],
        "type": "AzurePostgreSql",
        "version": "2.0",
        "typeProperties": {
            "server": "<server name>",
            "port": 5432,
            "database": "<database name>",
            "sslMode": 2,
            "authenticationType": "UserAssignedManagedIdentity",
            "credential": {
                "referenceName": "<your credential>",
                "type": "CredentialReference"
            }
        }
    }
}

Create linked services via API

Linked services can be created in the Azure Data Factory Portal via the management hub and any activities, datasets, or data flows that reference them.

You can create linked services by using one of these tools: .NET API, PowerShell, REST API, Azure Resource Manager Template, and Azure portal.

When creating a linked service, the user needs appropriate authorization for the designated service. If sufficient access isn't granted, the user can't see the available resources and must use the manual entry option.

Activities

Activities are tasks within a pipeline that can execute specific tasks. With script activity, users can run PostgreSQL scripts to query or modify their databases.

Related content