Dataset refresh failure with 'The key didn't match any rows in the table'

[monaraya]

Issue:

Dataset refresh failure with 'The key didn't match any rows in the table.' error even though the data source connection under Manage Gateways was successful. The data source was SQL Server.

This error usually comes up if there is any mismatch in credentials or fields.So we did what we normally do. We confirmed that the credentials, privacy levels and the field names match.

Then we moved to stage 2 which was testing the connection with the data source on Power BI Desktop. We could not connect and got "We could not authenticate with the credentials provided. Try again later." error. Please be informed that connection to SQL Server using the same credentials on SSMS was successful.

Steps which led to Resolution:

We tried connecting to the SQL Server on the same machine where SQL Server was hosted. The connection to the Server was successful  but in the Navigator when we expanded the Database we did not see any tables. So we expanded all the databases present in the Server. We could see tables under only one database.

We went to SSMS and tried to compare the permissions granted to the user on both the databases. (Open SSMS --> Object Explorer --> Security --> Users --> Right click on User --> Properties. We could not find a difference. Please find the screenshots below for reference.

We granted all permissions under Owner Schema except guest and all permissions under Membership.

However, we could not load the data because we did not have the permission to run the SELECT command.

Root Cause:

The permissions  'db_denydatareader' and 'db_denydatawriter' were the reason of connection failure in Power BI Desktop. We have removed it.The connection was successful in Power BI Desktop and the refresh on Power BI Service was successful too. So we could conclude that these permissions were the reason of dataset failure.

Author: Akshiita Srivastava

Reviewer: Mounika Narayana Reddy