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
6 years ago

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's avatar
Jimmy801
Community Champion
6 years ago

Hello Anonymous 

 

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