none
Database query with defined database user RRS feed

  • Question

  • Hello, support,

    I have created an Excel query on an SQL database. Because the Excel file is to be used by different users, I have stored a fixed database user with password under: "data source settings / data sources in the current workbook / edit permissions / login information for database".

    However, if another user wants to use the file, he will still be asked for valid credentials. Excel "forgets" the credentials I have stored. Even if I personally open the workbook from another PC, the saved credentials are no longer available. I assumed that "Data sources in the current workbook" means that this information is really stored in the workbook. 

    Does Excel not store this information in the workbook as it thought?

    Is it due to any other guidelines we may have stored in the company?

    Could it be due to the data protection level (I tried the selections "none" and "organization")?

    By the way, I don't need any help to create a connection via an odc file. I have to create a connection via a "Power-Query" query, because I need the programming language "M". I don't know any way how to create a "Power-Query" query on an odc file.

    Many thanks for your help

    Michael
    Tuesday, December 10, 2019 10:49 AM

Answers

All replies

  • Hi Michael. The credentials entered by a given user are stored separately from the workbook for security reasons.

    Ehren

    Tuesday, December 10, 2019 10:38 PM
    Owner
  • Hi Ehren.

    First of all, thank you very much for your answer. But it is very confusing when "connections in this workbook" are not really stored in the workbook.

    How should I proceed now to solve my problem? The user should not be added on the SQL-Server as a database user.

    Another tip?

    Greetings
    Michael

    Wednesday, December 11, 2019 9:56 AM
  • Our security model is that if a user accesses a data source, they need to provide their own credentials. You could work within this model by adding the users to the SQL Server, or giving users a username/password to use when connecting to the database. I'm not aware of any other options beyond this.

    Ehren

    Wednesday, December 11, 2019 6:19 PM
    Owner