Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Power Query using SQL as a data source

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...
Need Help
Show and Tell
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    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-48fc-8fc7-c8514b984440

    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.

  • Cristian_Angyal's avatar
    Cristian_Angyal
    6 years ago
    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
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Anonymous 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's avatar
Cristian_Angyal
Most Valuable Professional
3 years ago

Are you using a Gateway to connect to SQL DB?