Forum Discussion

ms_query's avatar
ms_query
Frequent Visitor
1 year ago
Solved

CredentialMissing for SQL kind inside Custom Connector

I was trying to create a custom data source which is wrapper on top of SQL.   I have set the Username-Password credentials for the custom data source. But it reports an error of missing crede...
Need Help
  • lbendlin's avatar
    lbendlin
    1 year ago

    That's what RLS is for?

  • SacheeTh's avatar
    SacheeTh
    1 year ago

    If ms_query ,

    If you want to limit some access based on each user, you can use RLS and OLS for this as lbendlin  suggested. 

     

    Initally connect using the standard connection using the SQL user name password. Then setup the Ro level Security and share with users accordingly.

     

    You even can use the Object level Security filtering some part of the tables with specific user group. 

     

    If you have a larger yser list, you may bring the same users to the model and setup a dynamic RLS where each user filers the specific rows accordingly 

SacheeTh's avatar
SacheeTh
Icon for Resolver II rankResolver II
1 year ago
The error indicates that your custom connector isn't passing credentials correctly to the SQL data source. I'll share some of the methods that how you can troubleshoot and resolve the issue:

The CredentialMissing error in your Power BI custom connector indicates that the credentials for the custom data source (ms_test_ext) are not being properly supplied or recognized. This can occur even if the test file works fine because Power BI Desktop and the testing environment handle credentials differently.

Here are steps to troubleshoot and resolve the issue:

1. Check Credential Storage in Power BI
Ensure that the credentials are set up correctly in Power BI for your custom data source.

Open Power BI Desktop.
Navigate to File > Options and Settings > Data Source Settings.
Locate your custom data source (ms_test_ext).
Edit the credentials to ensure the Username-Password combination is properly entered.



SacheeTh's avatar
SacheeTh
Icon for Resolver II rankResolver II
1 year ago

On 2nd thought, Looking at the code snippet in your screenshot, the issue might be related to how credentials are handled in your custom connector. Below are the adjustments and steps to ensure credentials are correctly passed and recognized by your custom data source (ms_test_ext).

Updated Code for ms_test_ext.pq

Add an Authentication property in the DataSource.Kind configuration to specify UsernamePassword authentication. This tells Power BI how to handle credentials for your custom connector.

 

 
 

 

[DataSource.Kind = "ms_test_ext", Publish = "ms_test_ext.Publish"]
shared ms_test_ext.Contents = (url as text) =>
    let
        Source = Sql.Databases(url),
        db = Source{[Name = "db"]}[Data]
    in
        db;

ms_test_ext = [
    Authentication = [
        UsernamePassword = []
    ],
    Label = "Custom SQL Data Source"
];
​

 

 

 

Explanation of Changes

  1. Authentication Property:

    • The Authentication property is added to explicitly support UsernamePassword. Without this, Power BI may fail to recognize or prompt for the credentials.

    • Simplified Parameter Handling:

      • The optional keyword was removed from the url parameter for simplicity.

      • Label Property:

        • A descriptive label is added for clarity when selecting the connector in Power BI.

          Additional Steps

          1. Set Credentials in Power BI Desktop

          After implementing the updated code:

          • Import the connector (ms_test_ext).
          • When prompted, enter the SQL server username and password.

            2. Test in Power Query SDK

            Before deploying the connector, test it using the Power Query SDK to ensure it correctly requests and uses the credentials.

            3. Validate Data Source Settings

            Ensure no conflicting or cached credentials exist:

            • Navigate to File > Options and Settings > Data Source Settings in Power BI Desktop.
            • Locate and clear any existing settings for ms_test_ext.
            • Re-enter credentials.

              4. Verify Deployment Environment

              If deploying to Power BI Service:

              • Ensure the custom connector is signed.
              • Configure the Power BI Gateway to use the appropriate authentication method.

                If you still encounter issues, provide the detailed error logs or let me know, and I can assist further!