none
Query Connection Properties - Enabling 'Save Password ' RRS feed

  • Question

  • Hi Forum,


    I'm using Excel-Office 365 to create queries to the SQL server.  I want users with no authorized access to the SQL server to refresh these queries.

    I want to enable the 'save password' option located in the 'Definition' tab of the 'Connection Properties' window but it is grayed out for every query.

    Thank you for your help

    Monday, August 20, 2018 4:48 PM

Answers

  • Hey!

    This is a by-design security feature within Power Query to protect you from situations where the file might end up in the wrong hands and people who shouldn't have access to the server/database now has access to it. (and who knows what they might do with it)

    As a general rule, it is not a good idea to save these type of passwords at the file level, but rather save it at the user instance/session level. Especially since when you save a password in Excel it gets saved as clear text and that's really troublesome from a security perspective.

    Wednesday, August 22, 2018 2:49 AM

All replies

  • Hey!

    This is a by-design security feature within Power Query to protect you from situations where the file might end up in the wrong hands and people who shouldn't have access to the server/database now has access to it. (and who knows what they might do with it)

    As a general rule, it is not a good idea to save these type of passwords at the file level, but rather save it at the user instance/session level. Especially since when you save a password in Excel it gets saved as clear text and that's really troublesome from a security perspective.

    Wednesday, August 22, 2018 2:49 AM
  • Hi,

    I have the exact same situation - trying to give outside users "view" to specific data in the database - through excel.  I've created a "read-only" user and connected to Azure database.  I can refresh the data through excel, but others cannot.

    What do you suggest as an alternative to achieve the same goal?

    Thank you in advance!

    Tuesday, May 14, 2019 6:55 PM
  • it heavily relies on your data security guidelines and what would be permitted within your company.

    I highly suggest that you get in touch with your DataBase Administrator to have a conversation with him about this topic and what would be the best course of action based on your company policies and security guidelines.

    If other folks don't need to have access to the database, then they shouldn't be able to refresh the queries, but if they do, then they absolutely need a set of credentials in order to gain access to the data source.

    Wednesday, May 15, 2019 4:57 PM
  • I am also looking for an answer if it is possible.

    Would appreciate it if someone can answer the question without giving the security lecture.

    Wednesday, October 16, 2019 8:02 PM