Creating a custom PostgreSQL connector with the power query SDK

[AntonyKlipper]

I'm trying to create a custom PostgreSQL connector that used Microsoft Entra Id as the authentication method with the Power query SDK. 

As a test I'm trying to just recreate the PostgreSQL connector that is already in Power-bi with UsernamePassword authentication. 

Should be pretty straight forward but I'm stuck. 

This is my .pq file

// Main function to connect to the PostgreSQL server
[DataSource.Kind="PostgreSQLConnector", Publish="PostgreSQLConnector.Publish"]
shared PostgreSQLConnector.Contents = (server as text, database as text) =>
    let
        // Connect to PostgreSQL without hardcoding credentials
        result = PostgreSQL.Database(server, database)
    in
        result;

// Publish metadata
PostgreSQLConnector.Publish = [
    Beta = true,
    Category = "Database",
    ButtonText = { Extension.LoadString("DataSourceButtonText"), Extension.LoadString("DataSourceButtonHelp") },
    SourceImage = PostgreSQLConnector.Icons,
    SourceTypeImage = PostgreSQLConnector.Icons, 
    SupportsDirectQuery = true
];

// Data source definition and authentication
PostgreSQLConnector = [
    Authentication = [
        UsernamePassword = []
    ],
    Label = Extension.LoadString("DataSourceLabel")
];

But I'm getting the error that I need to specify how to connect. 

And in visual studio code I get this error, that the credentials are missing even if I already set them. 

I try to do this with Odbc.DataSource in stead of PostgreSQL.Database and this works but I don't want the need to install a PostgreSQL driver locally. 

[AntonyKlipper]

Hi  @Anonymous, 

At the moment I'm not even trying to implement the Aad. I'm just trying to recreate a postgreSQl connector with UsernamePassword authentication and try to connect as a test. 

I'm using this 'PostgreSQl.Database' function but I'm getting the error specify how to connect. 

Kind regards, 
Antony

[Anonymous]

Hi @AntonyKlipper ,

Here are a few things you can try to check:

Authentication Definition: Ensure that the Authentication section is correctly defined and that the credentials are being passed properly.
Connection String: Verify that the connection string is correctly formatted and includes all necessary parameters.
Error Handling: Add error handling to capture and display any issues that occur during the connection process.

Also, you can check the steps to Implement Microsoft Entra ID Authentication:

1. Register Your Application:

   • Go to the Microsoft Entra admin center and register your application.
   • Note down the Directory (tenant) ID and Application (client) ID.

2. Configure OAuth:

   • Update your connector to use OAuth for authentication.
   • Define the StartLogin, FinishLogin, and Refresh functions for the OAuth flow.

3. Update Connector Definition:

   • Modify the Authentication section to include the Aad authentication kind.

Example:

MyConnector = [
    Authentication = [
        Aad = [
            AuthorizationUri = "https://login.microsoftonline.com/common/oauth2/authorize",
            Resource = "{YourApplicationIdUri}"
            Scope = ".default"
        ]
    ]
];

Configuring Microsoft Entra for a custom connector - Power Query | Microsoft Learn

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.