none
Sharing excel -file containg query from database (with user credentials) RRS feed

  • Question

  • Hi, 

    is there any way to share excel-file with query to user that does not have authorization to the whole database? Can the file contain credentials or something like that?

    Just to be clear, I want the user to be able to update the query, not just to see current values. And user does not need reading rights to the whole database.

    Jenni

    Monday, August 28, 2017 7:14 AM

Answers

  • Hey,

    Currently is not possible to share credentials within a query and I wouldn't recommend doing that either from a security perspective. Imagine if that file with embedded credentials ends up in the hands of someone that could harm your database?

    If the user doesn't have authorization to the database, then a set of credentials need to be created so that person can securely refresh that query and see only the data that they need to see. This would also help for traceability as the DBA would be able to spot who's querying the database the most and identify the users uniquely.

    Other methods would require you to basically give your own set of credentials to that other person so they can refresh that query, which is not secure nor recommended, but I've seen people do it. In this event, you can either share the workbook and send that person your credentials or share an ODC and also share your credentials.

    For this type of scenario where you want to share a report and provide a layer of security of your own on top of that report, Microsoft recommends that you use Power BI and the Row Level Security. This would give you exactly what you need without giving the end-user any type of access to the database and they'd only see what they are supposed to see

    Tuesday, August 29, 2017 7:40 PM

All replies

  • Hey,

    Currently is not possible to share credentials within a query and I wouldn't recommend doing that either from a security perspective. Imagine if that file with embedded credentials ends up in the hands of someone that could harm your database?

    If the user doesn't have authorization to the database, then a set of credentials need to be created so that person can securely refresh that query and see only the data that they need to see. This would also help for traceability as the DBA would be able to spot who's querying the database the most and identify the users uniquely.

    Other methods would require you to basically give your own set of credentials to that other person so they can refresh that query, which is not secure nor recommended, but I've seen people do it. In this event, you can either share the workbook and send that person your credentials or share an ODC and also share your credentials.

    For this type of scenario where you want to share a report and provide a layer of security of your own on top of that report, Microsoft recommends that you use Power BI and the Row Level Security. This would give you exactly what you need without giving the end-user any type of access to the database and they'd only see what they are supposed to see

    Tuesday, August 29, 2017 7:40 PM
  • Thanks Miquel!

    Appreciate your comprehensive answer a lot and will provide our IT with the possibilities.

    Jenni

    PS. I didn't realise until now you had answered my question, thought I would get some alert about it. That's why the late reaction on it :)


    Thursday, September 14, 2017 2:23 PM