Power Query using SQL as a data source

[mrbuttons07]

Hello everyone,

I made a report combining data from SQL and excel file. Since the data source in SQL requires me to use my credentials (due to the fact that SQL server here requires credentials to log in), how can I get other users to view the report without the need to access the SQL database?

The report only refreshes when I open the file but does not refresh when other users view the file. Each time a user opens the report, it prompts them to put in their credentials (which they do not have access to SQL Server or its database).

Thank you for your support!

[v-juanli-msft · Accepted solution]

Hi @mrbuttons07 

As tested, it seems importing data from SQL wouldn't cause this problem,

Maybe you connect to SQL via direct query mode in excel power query.

In this case, please create a sql database role in SQL server side, grant permission of the specific database and tables(you used in power query) to the end users.

When end users open excel, they can sign in with the granted sql database credential.

To enable the end users to refresh the data on their side, please refre to:

https://support.office.com/en-us/article/refresh-an-external-data-connection-in-excel-1524175f-777a-...

Based on my understanding, if the end users have access to the database and tables, they can refresh the data as creator does.

If you use power query in power bi, then you can publish the power bi desktop file to power bi service where you could configure schedule refresh.

https://docs.microsoft.com/en-us/power-bi/refresh-data

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rj7]

Hi @mrbuttons07 @Jimmy801 @Cristian_Angyal and @v-juanli-msft , I have used microsoft sql server import connection to powerbi desktop. Upon publishing the file online to app.powerbi.com, I get an error that 'Scheduled refresh is disabled because at least one data source is missing credentials. To start the refresh again, go to this dataset's settings page and enter credentials for all data sources. Then reactivate scheduled refres'.
I followed powerbi documentation and in advanced editor i found 'Source = Sql.Databases' in the datasource query.  

How do I enable refresh then?
Please help with your inputs at the earliest as i'm doing a critical task, Many thanks!!

Note- when connecting to the database in PBI desktop, I had to use the database credentials to login to it( I mean not my username and password). Could that be the reason?

[Cristian_Angyal]

Are you using a Gateway to connect to SQL DB?

[Cristian_Angyal · Accepted solution]

Create a new read-only SQL Database login (DB user) and instruct end-users to choose Database when prompted and then type user / pass.
This step is needed ONLY FIRST TIME when connecting

[Cristian_Angyal]

This solution works only for users using PBI Desktop.
As soon as the report is publised on PBI Service a Gateways is needed to be able to Refresh the data

[mrbuttons07]

@Cristian_Angyal 

Thank you for your suggestion. This seems to be the ideal way to allow them to view the report. Thanks!

[Jimmy801]

Hello @mrbuttons07 

you have to deactivate the automatical refresh. So you are the one to update the report, all other have no possibility, and this is just as you have designed it

Jimmy

[mrbuttons07]

hi @Jimmy801 

This seems like a good suggestion as well. However, the report I designed is to be viewed live by others. If the users would want to see the report every 1 hour, does that mean I would need to refresh the connections every 1 hour?

[Jimmy801]

Hello @mrbuttons07 

when they need to update it by themselves, how should it work that without having access to the server?

You could think of you checking out the file, set the queries to update every hour and write a smal makro that saves the file every hour and everybody else is only viewing in read only. But I don't know if something like that could be a feasable solution for your organisation.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[v-juanli-msft · Accepted solution]

Hi @mrbuttons07 

As tested, it seems importing data from SQL wouldn't cause this problem,

Maybe you connect to SQL via direct query mode in excel power query.

In this case, please create a sql database role in SQL server side, grant permission of the specific database and tables(you used in power query) to the end users.

When end users open excel, they can sign in with the granted sql database credential.

To enable the end users to refresh the data on their side, please refre to:

https://support.office.com/en-us/article/refresh-an-external-data-connection-in-excel-1524175f-777a-...

Based on my understanding, if the end users have access to the database and tables, they can refresh the data as creator does.

If you use power query in power bi, then you can publish the power bi desktop file to power bi service where you could configure schedule refresh.

https://docs.microsoft.com/en-us/power-bi/refresh-data

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mrbuttons07]

@v-juanli-msft 

Thank you for your advice. I will grant those users a read access only for the SQL database. Thanks a lot!