Forum Discussion

earg73's avatar
earg73
Icon for Advocate III rankAdvocate III
10 years ago
Solved

DirectQuery in Power BI

I tried to load some views using DirectQuery mode and I get the error message "Impersonation is not supported in DirectQuery mode. Turn off impersonation or switch to import mode."  What does this mean?

  • PowerBIGuy's avatar
    PowerBIGuy
    10 years ago

    earg73 I was able to fix this error by ensuring that the user that i'm using to connect to the SQL server was specificity mapped as a user on the actual database contained within my view. even if your user is a admin make sure that they are also mapped to the db that your view is accessing. 

23 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    I was able to fix this problem by creating a database user and map this to the specifiek database. So by not using windows credentials

  • Bjoern's avatar
    Bjoern
    Icon for Continued Contributor rankContinued Contributor
    10 years ago

    Do you have access limitations on the views (row-based security)?

     

    Power BI offers 2 modes of connecting SQL on-prem databases.

    1) Directy Query Mode: A live connection is established to the source. You have no chance of adding measures/calculated columns into the data modle and you basically only see the source.

    2) Import mode: You basically pull all the data into the file. Afterwards, you do not need a connection to the source. It has a few disadvantages (file size, no security, authentication etc.)

     

    What I would recommend:

     

    - Check access limitations on the mentioned SQL view

    - Try to use the "import mode" to test whether you can access the data in general with your authentication settings.

    - if option 2) works, but option 1) does not, open a bug report at microsoft.

     

    I guess in general, the authentication to views might be a bit different, thus causing problems. Please let us know your results.

     

    • earg73's avatar
      earg73
      Icon for Advocate III rankAdvocate III
      10 years ago

      Thanks for the reponse.  I do not have any restrictions on the view or the DB.  I have admin rights to both.  I currently access the data in "import mode" and wanted to move it to directquery mode.  Ideally what is the best way to use directquery mode to acces on prem date table, view or stored procedure?

      • Bjoern's avatar
        Bjoern
        Icon for Continued Contributor rankContinued Contributor
        10 years ago

         

        earg73

        I usually use views. Please help me out here, why would you use a sproc for that?

  • Furanity's avatar
    Furanity
    Icon for Helper I rankHelper I
    9 years ago

    Hi all,

     

    I have the same issue. Impersonation error and not being able to connect. Funny thing is, the gateway is running for another database on the same server with all the same settings as I have set up for a second database in the past.

    So: gateway configured for new second database, all running well, everything online.

    Created account to connect to to database with public/datareader access, works locally in management studio as well.

     

    As soon as I want to add the SQL Database as a source, select a random table (at one point I figured this might be better to test with than a view), it tries to create the connections but it fails with the impersonation error.

     

    More suggestions what could be wrong / what I could try? Especially since my "old" setup is working and the new one isn't, this is breaking my brain.

     

    Thanks!

     

    • Furanity's avatar
      Furanity
      Icon for Helper I rankHelper I
      9 years ago

      Was able to finally fix it. By not using a Windows account. Just couldn't get it working: got a "not authorized" (when connecting) or "impersonation not allowed" (when the tables had to be loaded in the model) error with all the possible connection options I had. Finally reverted back to a SQL account AND by using the Database tab when creating the connection and it was solved.

      I'm not too much a fan of having to use SQL accounts though compared to Windows accounts, so I hope this is going to be fixed some day.

      • cheid1977's avatar
        cheid1977
        Icon for Advocate I rankAdvocate I
        9 years ago

        Thanks, but this didn't work.  There has to be something that will allow you to turn off impersonation.