Day: March 24, 2020

We have an Azure Database which has an Admin UserName and Password

But we have also added the following to the Database In SQL Management Studio

— Create the user with Password for users that authenticate at the database

CREATE USER Org_powerbi WITH PASSWORD = 'password1';

–And Create a role

CREATE ROLE db_Org_read;

–Execute the Stored Procedure addrole member. Here the Org_powerbi user is being added to the above role

EXEC sp_addrolemember 'db_Org_read', ' Org_powerbi';

–We have a schema called org. We are granting the SELECT permission for this schema to the above Role. this means that you can perform Select data Statements

GRANT SELECT ON SCHEMA :: Org TO db_Org_read

So we added a user and granted the user SELECT permissions

When we use this user ID and password in Desktop it works fine. We can refresh the data.

However in Service (Dataset – Schedule Refresh), the login credentials are invalid

Authentication Method – Basic

Choose Basic if you plan to use SQL authentication instead of Windows authentication

Privacy level Setting for this Data Source – Organizational

An Organizational data source limits the visibility of a data source to a trusted group of people. An Organizational data source is isolated from all Public data sources, but is visible to other Organizational data sources.

The Solution

The user account you create needs to be added to the Data Source Credentials in the Power BI Service once the dataset has been uploaded.

Go to Settings

And again. Settings

And Edit Data Source Credentials of the data Set

This will add the user to the Data Source Credentials

Once added you can go back to the Schedule Refresh in the Data set in your app workspace and the Log in Credentials should now work.