DirectQuery in Power BI

[earg73]

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?

[Furanity]

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]

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]

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

[Anonymous]

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]

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]

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]

@earg73

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

[earg73]

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

[earg73]

Any additional insight on this?

[Bjoern]

@earg73 Have you tested @Anonymous's proposal? 

[JorgeMoralesCR]

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.

[earg73]

Does anyone have any more information on this?

[fbrossard]

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 ?

[fredrikg]

@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

[fbrossard]

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

[fredrikg]

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

[fbrossard]

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/

[Bjoern]

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

[Dodge1350]

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.

[Dodge1350]

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.