cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Responsive Resident
Responsive Resident

Re: DirectQuery in Power BI

@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. 

Business Intelligence Architect / Consultant

View solution in original post

23 REPLIES 23
Highlighted
Responsive Resident
Responsive Resident

Re: DirectQuery in Power BI

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.

 

Highlighted
Frequent Visitor

Re: DirectQuery in Power BI

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?

Highlighted
Responsive Resident
Responsive Resident

Re: DirectQuery in Power BI

 

@earg73

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

Highlighted

Re: DirectQuery in Power BI

I'm thinking that perhaps it has something to do with the EffectiveUsername property setting, which is explained pretty nicely in the knowledge base (LINK).

 

Have you verified that this setting is working properly?

Highlighted
Frequent Visitor

Re: DirectQuery in Power BI

@Bjoern I'm not using a sproc, I'm using a view.  I was questioning what other ways one can access the data.

Highlighted
Frequent Visitor

Re: DirectQuery in Power BI

Any additional insight on this?

Highlighted
Responsive Resident
Responsive Resident

Re: DirectQuery in Power BI

@earg73 Have you tested @BenniDejagere's proposal? 

Highlighted
New Member

Re: DirectQuery in Power BI

I have the same issue:  Conecting to a SQL  Server I cannto DirectQuery them. The impersonation not suported message is shown. When Using import no problem. When checking @BenniDejagere y saw it applies for Analysis Services. THis is not the case.

Highlighted
Frequent Visitor

Re: DirectQuery in Power BI

Does anyone have any more information on this?

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021