locked
Using Windows Authentication to access file on a restricted server RRS feed

  • Question

  • Dear All,

    I am setting up a PowerPivot model which is querying an Excel file in a secured server not accessible by the majority of the users, however, my model (which it will be distributed to several users in the company) will need to read some data on the secured server.

    As the HD where is stored the secured file is not installed on everyone machine, using the default PowerPivot login details will return an error message OLE DB or ODBC error: "H:\...."  is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides, 3044.

    I was wondering if there is any way to force PowerPivot to use the Windows Authentication details (I could then save my login details in the query) instead of the user authentication details.

    Thank you in advance for your help.

    Regards,

    Paolo



    • Edited by Paolo Succo Tuesday, October 4, 2016 8:55 AM
    Tuesday, October 4, 2016 8:49 AM

Answers

  • Hi Paolo Succo,

    After research and test, you are unable to change the User ID and password the .accdb file which store the data you want to import.

    The access database is sot when it is created, and user-level security features are not available in Access web apps, web databases, or databases that use one of the new file formats (.accdb, .accde, .accdc, .accdr).

    For more details, you can review the following articles.
    What happened to user-level security?
    Where is Security feature in Microsoft Access 2007, 2010, 2013 and 2016?

    I will update the new context if I know more professional reference.
    If you have any more questions, please don’t hesitate to ask.


    Best Regards,
    Angelia

    Thursday, October 6, 2016 8:39 AM
  • Hello Paolo,

    That's no directly Power Pivot related issue, more a Windows OS permission issue and therefore it can not be solved on Power Pivot side.

    By your description to PP users should be able to fetch the data, so I don't see a reason why the data source is hosted on a high securely host, where the users can access the source file. Create a copy of the source file on a share where the PP users can access the source file.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, October 7, 2016 5:57 PM

All replies

  • Just curious on below query:

    Is there any version of SQL Server you are using?

    Also, with 2016 version, there are some approaches to do windows authentication, please check below URL:

    https://msdn.microsoft.com/en-us/library/ee210621.aspx?f=255&MSPPError=-2147217396


    Santosh Singh

    Tuesday, October 4, 2016 6:26 PM
  • Hi Santosh

    I am using Powerpivot and Excel 2013.

    Powerpivot is importing a table from an Excel file in a restricted access server.

    On the connection advance setup, my parameter are as follow:

    1 - Pooling: OLE DB Services = Default

    2 - Security:

    Cache auth = True

    Encrypt pass = True

    Mask pass = True

    Password = blank

    Persist security info = False

    User ID = Admin

    for some reasons, if I put a password and my User ID, powerpivot change the User ID back to Admin.

    3 - Source: data source = path to the restricted access server and file

    I was thinking that if I can change the User ID and password to mine, Powerpivot should be able to access the file. 

    Thanks.

    Paolo

    Wednesday, October 5, 2016 7:49 AM
  • Hi Paolo Succo,

    After research and test, you are unable to change the User ID and password the .accdb file which store the data you want to import.

    The access database is sot when it is created, and user-level security features are not available in Access web apps, web databases, or databases that use one of the new file formats (.accdb, .accde, .accdc, .accdr).

    For more details, you can review the following articles.
    What happened to user-level security?
    Where is Security feature in Microsoft Access 2007, 2010, 2013 and 2016?

    I will update the new context if I know more professional reference.
    If you have any more questions, please don’t hesitate to ask.


    Best Regards,
    Angelia

    Thursday, October 6, 2016 8:39 AM
  • Hello Paolo,

    That's no directly Power Pivot related issue, more a Windows OS permission issue and therefore it can not be solved on Power Pivot side.

    By your description to PP users should be able to fetch the data, so I don't see a reason why the data source is hosted on a high securely host, where the users can access the source file. Create a copy of the source file on a share where the PP users can access the source file.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, October 7, 2016 5:57 PM