none
Shared Excel 365 power query connection to SQL table RRS feed

  • Question

  • I have an excel 365 file with a power query connection to a SQL DB table (custom SQL login) and I attempting to save the file with the connection and share within my organization. When the file is opened and the query refreshed the staff member receives a prompt for the SQL credentials that are saved within the initial connection to SQL.  My question is, is it programmatically possible to share the file with the query with other staff, with the ability to refresh the data without being challenged for credentials

    Thanks


    John Chendorain

    Thursday, May 23, 2019 8:09 PM

Answers

  • Hi John. It's currently not possible to share a PQ query with the credentials included. This is for security reasons. Each user will have to enter the credentials when refreshing.

    Ehren

    Friday, May 24, 2019 10:42 PM
    Owner
  • Thanks Ehren for that info, so I have found that its possible, and that I was not completely clear with the question. In our case the AD Users accessing own the custom DB, and I have setup a local domain group with there approved AD users only with db_reader permissions to there DB only.  This allows to set-up a query using the data model as the connection that can be refreshed by the authorized AD users only.

    Thanks again

     

    John Chendorain

    Monday, July 15, 2019 4:34 PM

All replies

  • Hi,

    Since this issue is more related to Power Query, I'm moving it to a Power Query forum:

    https://social.technet.microsoft.com/Forums/en-US/home?forum=powerquery

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Bella Wu


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    Friday, May 24, 2019 8:21 AM
  • Thanks so many forums to choose from.

    John Chendorain

    Friday, May 24, 2019 1:34 PM
  • Hi John. It's currently not possible to share a PQ query with the credentials included. This is for security reasons. Each user will have to enter the credentials when refreshing.

    Ehren

    Friday, May 24, 2019 10:42 PM
    Owner
  • Thanks Ehren for that info, so I have found that its possible, and that I was not completely clear with the question. In our case the AD Users accessing own the custom DB, and I have setup a local domain group with there approved AD users only with db_reader permissions to there DB only.  This allows to set-up a query using the data model as the connection that can be refreshed by the authorized AD users only.

    Thanks again

     

    John Chendorain

    Monday, July 15, 2019 4:34 PM
  • Hi John. That makes sense. I'm glad you were able to find a solution.

    Ehren

    Monday, July 15, 2019 6:59 PM
    Owner