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

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 by 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 by 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 flexible server and perform data integration tasks within Azure Data Factory.

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

Prerequisites

• An Azure Database for PostgreSQL flexible server. To learn more, see Create an Azure Database for PostgreSQL.
• (Optional) An Azure integration runtime created within a managed virtual network.

Create a linked service via the Azure portal

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

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

   • Azure Data Factory
   • Azure Synapse

   

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.

   

3. Make sure that Version 2.0 is selected.

   

Authentication

Four methods are supported 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. Enter your Azure PostgreSQL flexible server connection details, including Server name, Username, and Password.

   

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

   • Azure subscription
   • Enter manually

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

   

3. Enter the Username and Password.

   

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

Service principal authentication

To set up service principal authentication, you need to set 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 as the authentication type.

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

   

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

   • Azure subscription
   • Enter manually

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

   

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.

   

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

   

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

   

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

   • Service Principal Certificate
   • Service Principal Key

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

   

9. Select your Azure cloud type.

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

System-assigned Managed Identity authentication

To use 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, complete 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

      

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

      

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

      

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

   

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

   • Azure subscription
   • Enter manually

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

   

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 in the Azure portal. To learn more, see 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. Select + Add Microsoft Entra administrators and select your user-assigned managed identity

      

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, select + Add and select your user-managed identity

      

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

   

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

   • Azure subscription
   • Enter manually

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

   

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

   

   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 select Create.

      

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

Test Connection

1. After you fill in all required connection fields, the bottom right-hand corner displays a Test connection button. The test connection button verifies that the linked service can connect to your Azure Database for PostgreSQL server.

   

2. Select Create.

Linked service JSON

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

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

The following examples show JSON configurations for different authentication methods, including Basic Authentication, Service Principal Certificate, and Service Principal Key. You can customize these JSON templates to suit your specific requirements.

A linked service that uses 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 that uses 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 that uses 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

You can create linked services in the Azure Data Factory portal by using 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 you create a linked service, you need appropriate authorization for the designated service. If you don't have sufficient access, you 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. By using script activity, you can run PostgreSQL scripts to query or modify your databases.

• Script Activity: Transform data using the Script activity in Azure Data Factory or Synapse Analytics.

• Copy Activity: Copy activity in Azure Data Factory and Azure Synapse Analytics.

• Lookup Activity: Control flow lookup activity.

Related content

• How to connect to data factory private endpoint
• Azure Database for PostgreSQL networking with Private Link