Forum Discussion

GarethParry's avatar
GarethParry
Icon for Helper I rankHelper I
1 year ago
Solved

Power 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 ...
Need Help
  • Ilgar_Zarbali's avatar
    Ilgar_Zarbali
    1 year ago

    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:

    1. "Use a gateway + Power BI service" if you're open to sharing via Power BI – it allows central credential management.
    2. Or, consider using a "shared Windows-authenticated SQL account" if your SQL Server allows it.
    3. 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.

Thejeswar's avatar
Thejeswar
Icon for Super User rankSuper User
1 year ago

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,

  • GarethParry's avatar
    GarethParry
    Icon for Helper I rankHelper I
    1 year ago

    HI,

     

    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's avatar
    GarethParry
    Icon for Helper I rankHelper I
    1 year ago

    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's avatar
      Thejeswar
      Icon for Super User rankSuper User
      1 year ago

      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,