Forum Discussion

AntonyKlipper's avatar
AntonyKlipper
Regular Visitor
1 year ago

Creating a custom PostgreSQL connector with the power query SDK

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. 

 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

     

     

     

  • AntonyKlipper's avatar
    AntonyKlipper
    Regular Visitor
    1 year ago

    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