topic Re: DirectQuery in Power BI in Power Query

DirectQuery in Power BI

earg73 — Thu, 03 Dec 2015 15:18:42 GMT

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?

Re: DirectQuery in Power BI

Bjoern — Fri, 04 Dec 2015 08:32:16 GMT

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.

Re: DirectQuery in Power BI

earg73 — Wed, 09 Dec 2015 16:17:09 GMT

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?

Re: DirectQuery in Power BI

Bjoern — Thu, 10 Dec 2015 10:44:20 GMT

earg73

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

Re: DirectQuery in Power BI

Anonymous — Thu, 10 Dec 2015 11:50:11 GMT

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?

Re: DirectQuery in Power BI

earg73 — Thu, 10 Dec 2015 13:37:11 GMT

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

Re: DirectQuery in Power BI

earg73 — Wed, 16 Dec 2015 23:03:06 GMT

Any additional insight on this?

Re: DirectQuery in Power BI

Bjoern — Thu, 17 Dec 2015 08:57:17 GMT

earg73 Have you tested Anonymous's proposal?

Re: DirectQuery in Power BI

JorgeMoralesCR — Thu, 17 Dec 2015 18:02:55 GMT

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.

Re: DirectQuery in Power BI

earg73 — Wed, 30 Dec 2015 20:48:13 GMT

Does anyone have any more information on this?

Re: DirectQuery in Power BI

fbrossard — Tue, 05 Jan 2016 01:33:25 GMT

Using views is a good thing to expose your data.

The problem should come from your database connection settings. Which authentication do you use ? Windows ? Database ? Do you have specified a user or do you use the current user ?

Re: DirectQuery in Power BI

Dodge1350 — Wed, 13 Jan 2016 14:33:43 GMT

We are having the exact same issue. Our situation is as follows:

Installed Power BI Gateway, success.

Connected to the Data Source through the Gateway so that we could see the data in the through the gateway; success.

Tried adding the data source to power bi desktop so we can take advantage of direct query and when we do so, we are faced with an "impersonation" error message. We have no idea what that means, since we have not setup anything during this process to use impersonation and did not see anywhere where one would set that up or configure it in the software.

Using database credentials that were prepared explicitly for this purpose. Those credential have read only access of the table we are trying to connect to through the desktop app using the gateway. Any ideas why this is occurring?

Also, there was absolutely no documentation regarding this error in the Power BI support forum, a search for impersonation returns no results. I would expect to see at a minimum some explanation of how the condition can exist and what it means.

Looking forward to learning more about this issue and how to rectify it and quickly. We are demoing this to non-technicals and will need to make the business case for it to justify pro cost. If we can't connect through the gateway, that business case evaporates.

Re: DirectQuery in Power BI

mwballenger — Mon, 18 Jan 2016 18:15:00 GMT

I'm getting the same issue. Let me ask if the circumstances are similar: When importing (get data), I'm logging onto a SQL server database on a local machine or network server. I have to specify credentials for logon (impersonation) because the default for login username uses a concatenation of computername and username (mycomputermyusername)instead of mynetworkname\myusername. I have to do this for SSIS and SSAS as well, but they have no issues with impersonation.

My thought is that import is set up to allow impersonations, but direct query is not. Unless I can set the default for the login username, it looks like the only way to bring in the data is by import.

Any ideas?

Re: DirectQuery in Power BI

fredrikg — Tue, 19 Jan 2016 09:39:32 GMT

fbrossard: What are those database connection settings you mention? When adding a data source to the Power BI Enterprise Gateway, there is no way to specify "current user", only a specific user for authentication against the SQL database.

And in Power BI Desktop, there is no setting at all, it simply uses the current user (running Power BI Desktop) to authenticate.

Not trying to be rude or anything, just trying to understand if I am missing something.

/Fredrik

Re: DirectQuery in Power BI

fbrossard — Wed, 20 Jan 2016 12:12:00 GMT

No problem fredrikg,

Just connect onto your PowerBI.com worskpace, click on the wheel and select "Manage Gateways"

Select your gateway and the target database and review your data source settings, espcially the authentication method you use

Re: DirectQuery in Power BI

fredrikg — Wed, 20 Jan 2016 12:38:23 GMT

Thanks for your response!

The settings you refer to only allow specifying the user that will be used when connecting to the SQL Server, through the Enterprise Gateway.

I can't see anyway to specify that the current user's identity should be used when connecting.

In other words, there will always be the same user (Windows or Basic) connecting, there is no way to detect the identity of the user browsing the Power BI report/dashboard.Am I right?

/Fredrik

Re: DirectQuery in Power BI

fbrossard — Fri, 22 Jan 2016 02:49:06 GMT

When you use Windows Authent, the credentials of the user who is browsing is passed through the gateway to your on-premise SQL Server.

Basic Authent is the same as using SQL Authent, in that case it's the same account that is passed through the gateway regardless of the connected user.

http://biinsight.com/power-bi-enterprise-gateway-preview-episode-1/

Re: DirectQuery in Power BI

Bjoern — Sun, 24 Jan 2016 11:31:29 GMT

fbrossard

To my knowledge, for the SQL on-prem gateway, the credentials are not passed through the gateway. -> RLS only works for SSAS Tabular, but not for SQL.

// Also described here:

http://community.powerbi.com/t5/Desktop/Performance-issue-with-Direct-Query-with-SQL-Azure/td-p/14656/page/2

Re: DirectQuery in Power BI

Dodge1350 — Mon, 25 Jan 2016 22:07:11 GMT

No response in this thread or through the Office 365 ticket system has provided any answers; in fact, no response means no response. I've heard nothing from either side.

Re: DirectQuery in Power BI

PowerBIGuy — Tue, 26 Jan 2016 16:37:42 GMT

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.