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 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.