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