Forum Discussion
Gateway with different creds to Datasource
Easiest way is to think of it like this:
While you are in Power BI Desktop, you have some locally stored credentials that are used to make the connection. If you save your PBIX file and pass it to someone else, the credentials are not stored in the PBIX file itself, but in the local cache of your Power BI Desktop application. This new person would need to enter their own credentials to access the DirectQuery data, or run a refresh (Import).
When you publish to the Power BI Service, again no credentials are sent along with the file. Since the cloud is outside your network, unless your data source is also in the cloud, it has no direct connection to your data source.
An On-Premise Data Gateway acts as a secure bridge. It gets installed on hardware inside your network and you make a specific connection to the Office 365 tenant. Under "Manage Gateways" in the Power BI Service, you can add Data Sources, their credintials, and which users can access this data source. This is now avaiable to be used by any project that contains that data source and is being configured by someone on the access list. (small note, a project that wants to use a gateway needs to ensure that every data source is within the gateway, or is a cloud data source).
When you go into the "Schedule Refresh" of your Power BI Dataset on the Service, you must select a gateway. If its configured correctly you will see it appear in the gateway listing. Whenever this refreshes (import) or connects (direct query), it will talk only to the gateway. The gateway itself will handle the authentication and passing the data between the cloud and your network. The report itself doesn't need any credentials.
The credentials while in the cloud are all managed via "Manage Gateways" and work irrespective of how you publish a project/dataset into the Power BI Service.
When you set up each data source in your Gateway you specify which connection credentials each source will use. This is where you would use your own personal credentials if that is what is required for your refreshing to the Service.
I would advocate for this to be a service account rather than a personal account however.
Thanks Ross. I have been playing around with this for the last week.
Firstly it took me a while but I realise now that you must run as admin or you can't connect to SQL at all.
Secondly, if I load a SQL datasource by just providing Server\DB\DirectQuery then I get prompted for creds and can connect.
However when I try to provide a SQL statement to connect to, I don't get the option and it just says that its required and never loads.
As for the account, I don't have a service account only a admin account that can be used. I havn't exactly been given permission to use Power BI but it just makes my life so much easier. At the moment I can run Power BI with my admin Creds, and then everything works. I just can't use the (personal) gateway.
When configuring the gateway itself, you'll only need to specify the Server Name, the database name and the appropriate log on credentials.
As for SQL. You can specify the SQL statement as part of your importing, there is an "Advanced options" down below. You won't need to place the SQL into the gateway config.
Sorry maybe I was not clear.
If I add a SQL statement through the spot you just showed me then I cannot add different logon Creds even though it says that it’s required.
If however I just skip the SQL Query then all is good, I get prompted to change the creds and then can see all tables.
Is there a way to just save the Server and DB with the correct creds, and then run my queries on top? I have been adding additional queries for my different sections (i.e. for my Service Desk tool, I have one for Tickets and one for CMDB) as this was the only way I could work out how to join tables (only way to solve this Problem)
