Forum Discussion
Helper IPower Query in Excel issue
Hi,
We used to use the legacy microsoft query option in excel to pull data into a spreadsheet. we would then share this spreadsheet with colleagues who could also refrssh the data.
As MS are movinig to Power query we thought we would do the same, however when we share the sheet and other users refesh the data they get asked for log in credentials. Its an SQL datasource with SQL credentials set up so they dont have individual credentials and we do not want to share the log in details
We have looked at pushing out a DSN via intune but this doesnt store the credentials either.
Is there a way of doing this?
thanks
Gareth
In Power Query, credentials are stored "per user" and "not embedded" in the Excel file for security reasons. That’s why others are prompted to log in when refreshing the data.
To work around this securely:
- "Use a gateway + Power BI service" if you're open to sharing via Power BI – it allows central credential management.
- Or, consider using a "shared Windows-authenticated SQL account" if your SQL Server allows it.
- Embedding SQL credentials in Excel is not supported for security reasons.
Unfortunately, Power Query is designed to avoid sharing credentials directly for compliance and safety.
9 Replies
- Thejeswar
Super User
Hi GarethParry ,
This is how it works. The SQL Server handles authentication to the data in it, so a separate authentication may be required to refresh the SQL data.
Alternatively, you can try using On-premise data gateway to create the authentication at the Power BI Service level. If my understanding is right, this will allow you to authenticate to the SQL server using a centrally managed connection. Once you authenticate to it and share it, if someone tries to refresh the data, the On-premise data gateway should take care of the authentication part for the refresh.
BTW this approach would require you to publish your Power Query into Power BI Service by placing it in a PBIX
Regards,
GarethParryHI,
Not sure i fully understand. we need the query in excel not a power BI report. we can access data in the power BI service from Excel?
thanks
- GarethParry
Hi,
I can see how i can do this, but
How do i pass parameters from Excel to the dataset
Do all of the users who will access the information need to have a power bi License?
- Thejeswar
Hi GarethParry ,
If you are taking the Power BI Gateway based authentication approach, as your Gateway will already have your credentials authenticated with your dataset, any person refreshing it may not need separate credential / authentication to refresh the dataset.
As for the license part, if your workspace is in Premium Capacity, your users may not need individual license to refresh the dataset from excel. In case if you are in a PRO license, then only users who have PRO License will be able to refresh the dataset
If this answers, kindly "Accept it as Solution" and appreciate with a Kudo !!
Regards,
v-sdhruvCommunity Support
Hi GarethParry ,
This is a common challenge when transitioning from legacy Microsoft Query to Power Query in Excel, especially with shared workbooks and SQL authentication.
Power Query stores credentials per user, not per workbook. So, when someone else opens the workbook and refreshes the query, they're prompted to authenticate, because their local credential store doesn't have access to the original SQL login.
As rightly pointed by Thejeswar , using a Power BI Gateway (On-premises Data Gateway) with Power BI Service,you can publish the data model to Power BI, then let up a gateway connection with the shared SQL credentials centrally then the end-users can just refresh via Power BI — no credential prompts.
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You- v-sdhruv
Hi GarethParry
Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You - v-sdhruv
Hi GarethParry ,
No, you cant pass parameters from Excel to the datasetnot directly. Power BI datasets don’t support dynamic parameter passing from Excel like Power Query in Excel does.Excel allows filtering, slicing, and pivoting within the scope of what's already loaded in the dataset, but you can't pass new parameters to filter the dataset at the source level.If you're staying in Excel and need parameter-driven queries:You'll need to continue using Power Query in Excel and handle parameter entry manually.
For workspace license-
1. If the Workspace is Pro Workspace, users will need Pro License
2.If workspace is Premium/My workspace, free users can connect.
Hope this helps!
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You! - MCG
Hi there,
I would create workflow in PowerBI service ( connection to your SQL with all needed credentaials ) and then connect via PQ in Excel
Workflow can be updated on daly basis
br
MCG
- Ilgar_Zarbali
In Power Query, credentials are stored "per user" and "not embedded" in the Excel file for security reasons. That’s why others are prompted to log in when refreshing the data.
To work around this securely:
- "Use a gateway + Power BI service" if you're open to sharing via Power BI – it allows central credential management.
- Or, consider using a "shared Windows-authenticated SQL account" if your SQL Server allows it.
- Embedding SQL credentials in Excel is not supported for security reasons.
Unfortunately, Power Query is designed to avoid sharing credentials directly for compliance and safety.
