none
File.Contents repeatedly asks for credentials in SSDT model RRS feed

  • Question

  • I am using SSDT to authorize tabular model.

    i would like to open a file using service impersonation

    I am using M to read the file using 'File.Contents()'

    the first time i am asked for credentials, i say 'impersonate service user' - works fine

    now every time i open the model i am asked again...

    is there a way to write in M code to tell the M function to use the service user impersonation?

    it works well if i explicitly create a data source with this impersonation. i am trying to do it in code so i can easily parameterize the file name i am opening.


    Thursday, December 5, 2019 2:31 PM

Answers

  • Kobia,

    For a variety of reasons this is not supported. In SSAS there must always be a Data Source object that holds the credential object for a data source. This is important so that credentials intended for one source cannot be hijacked and applied to other sources. One such example is using a credential ImpersonateServiceAccount for C:\test and applying that credential to C:\Windows\system32. It's not a good idea.

    SSDT does not hold on to credentials unless they are stored on a data source. PQ does some things to work around this by constantly asking you, but even if you follow that path, there won't be credentials in the model after it is deployed to an SSAS instance (Or AS Azure).

    If you really want a parameterized data source, you would need to ensure that there is a data source object present for all possible file URLs, even if that Data Source isn't directly referenced from the Table's query. However, this is not really the natural path for SSDT and I can't guarantee that you won't hit issues doing this.

    Tuesday, December 17, 2019 12:21 AM

All replies

  • Hi kobia. Does the same thing happen if you try your scenario in Power BI Desktop?

    Ehren

    Tuesday, December 10, 2019 10:55 PM
    Owner
  • I haven't tried.

    But i can say that when looking at the .bim's json it is clear that when i explicitly define a data source, it saves the credentials definition in the json (don't know what would have happened if i put there user name and password, but i deal with a simpler case that does not require encryption, i.e. impersonate service account). when loading a file in M code, i don't see anything regarding credentials in the json)

    also, anyway, i dont think you expose 'impersonate service account' in power bi as it is not relevant, nor any option of user impersonation.

    Thursday, December 12, 2019 1:01 PM
  • Kobia,

    For a variety of reasons this is not supported. In SSAS there must always be a Data Source object that holds the credential object for a data source. This is important so that credentials intended for one source cannot be hijacked and applied to other sources. One such example is using a credential ImpersonateServiceAccount for C:\test and applying that credential to C:\Windows\system32. It's not a good idea.

    SSDT does not hold on to credentials unless they are stored on a data source. PQ does some things to work around this by constantly asking you, but even if you follow that path, there won't be credentials in the model after it is deployed to an SSAS instance (Or AS Azure).

    If you really want a parameterized data source, you would need to ensure that there is a data source object present for all possible file URLs, even if that Data Source isn't directly referenced from the Table's query. However, this is not really the natural path for SSDT and I can't guarantee that you won't hit issues doing this.

    Tuesday, December 17, 2019 12:21 AM