CredentialMissing for SQL kind inside Custom Connector

[ms_query]

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 credentials while attempting to connect the Sql database.

{

...

"Method": "PQTest.RunTest",
"Name": "ms_test_ext.query.pq",
"Status": "Failed",
"Type": "PQTest.Expression",
"Error": {
"Message": "Credentials are required to connect to the ms_test_ext source. (Source: from myserver-ondemand.sql.azuresynapse.net;db to ms_test_ext.)",
"Details": {
"Microsoft.Data.Mashup.CredentialError.Reason": "CredentialMissing",
"Microsoft.Data.Mashup.CredentialError.DataSourceKind": "ms_test_ext",
"Microsoft.Data.Mashup.CredentialError.DataSourcePath": "ms_test_ext",
"Microsoft.Data.Mashup.CredentialError.DataSourceOriginKind": "SQL",
"Microsoft.Data.Mashup.CredentialError.DataSourceOriginPath": "myserver-ondemand.sql.azuresynapse.net;db",
"Microsoft.Data.Mashup.MashupSecurityException.Reason": "CredentialMissing",
"Microsoft.Data.Mashup.MashupSecurityException.DataSources": "[{\"kind\":\"ms_test_ext\",\"path\":\"ms_test_ext\"}]"
}}}

While if I directly try to use this query in test file it works fine.

[v-pnaroju-msft]

Hi ms_query,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.

Thank you.

[v-pnaroju-msft]

Hi ms_query,

We wanted to check in regarding your query, as we have not heard back from you. If you have resolved the issue, sharing the solution with the community would be greatly appreciated and could help others encountering similar challenges.

If you found our response useful, kindly mark it as the accepted solution and provide kudos to guide other members.

Thank you.

[v-pnaroju-msft]

Hi ms_query,

We would like to inquire if the solution offered by @SacheeTh and @lbendlin has resolved your issue. If you have discovered an alternative approach, we encourage you to share it with the community to assist others facing similar challenges.

Should you find the response helpful, please mark it as the accepted solution and add kudos. This recognition benefits other members seeking solutions to related queries.

Thank you.

[ms_query]

Sorry @lbendlin to tag, but could you help in any way?

[SacheeTh]

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 

[lbendlin]

What's the reason for bolting this onto a standard SQL connection?

[ms_query]

@lbendlin  Thanks for the reply, I need to create a wrapper on top of the SQL connection which would filter data based on some bussiness need before user's data access.

[lbendlin]

That's what RLS is for?

[ms_query]

Yes it is, but more specific.

The need is to restrict at the data-source integration level. Bcause the entitlement configurations are not exposed to the dashboard developer.

[ms_query]

Thanks @SacheeTh for the reply.

Yes I have Authentication setup in my extension as you suggested. But it fails while evaluteing ms_test_ext.query.pq itself.

as if it could not find the credentials for

DataSourceOriginKind: "SQL" and DataSourceOriginPath: "myserver-ondemand.sql.azuresynapse.net;db" combination

If Itry this in PowerBi Desktop then it shows error: "Please specify how to connect."

[SacheeTh]

Tyr Modify your as follow, pass both the server and databas

let 
    result = ms_test_ext.Contents("myserver-ondemand.sql.azuresynapse.net", "mydatabase")
in 
    result

 
Not much of an indea since your one is a custom connector, asuming this is done using power m query, I can suggest following as well, 

•  Ensure the DataSourcePath matches what the SQL source (myserver-ondemand.sql.azuresynapse.net) expects. This includes both the server name and the database name.

• The CredentialMissing error often happens when the SQL credentials are not propagated correctly from the custom connector.

• You're passing only the server URL, but SQL often requires both server and database names explicitly. Adjust the connector to expect both parameters.

  Additional Steps

  - Clear existing credentials from Data Source Settings in Power BI.

[ms_query]

Is it possible for you to try with any of your SQL server connection to confirm. As nothing is working for me.

[SacheeTh]

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]

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!