Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
UserInterface
Advocate I
Advocate I

Gateway with different creds to Datasource

Hey Guys. I have a problem trying to work out how to use 2 sets of creds.

 

I need to use Admin creds to access the data source (2x SQL DB) but then when I use a gateway I need to sign in with my normal account to access o365 PowerBI.

 

Does anyone know if this can be achived somehow?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

20 REPLIES 20
Anonymous
Not applicable

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.

 

 

 

Anonymous
Not applicable

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.

Capture.PNG

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)

Anonymous
Not applicable

Power BI Desktop remembers credentials you have used, which might be why you aren't getting the credentials message. You can change the credentials by clicking on 'Edit Queries drop arrow' -> 'Data Source Settings'

 

Credentials are not saved into the project itself.  Whatever you choose in the Power BI desktop has no bearing on your gateway.

 

If you do want to modify what is stored in Power BI Desktop you can try:

 

If you are using a file that is already connected to the data source, you can select it and choose "Edit Permissions".

 

If you are in an empty project, you can select at the top "Global Permissions" and find your data source.  This will let you update the permissions.

This is what i get when trying to use domian admin creds when connection to a data source. 

Power BI running as domain user (run as admin) and datasource has my domain admin creds.

powerbierror.PNG

 

Anonymous
Not applicable

If you select 'Edit Queries drop arrow' -> 'Data Source Settings' and then edit the permissions.  Is your data file set to this:

 

Capture.PNG

 

That is what Power BI desktop is complaining about.

 

Alternative you could instead use an SQL Authentication (database username and password) and avoid it entirely.

 

Based on your description it sounds like there is a specific account that you need to use, so if the user account that is running Power BI Desktop is different to the one in the database, you could try holding shift before right-clicking on your Power BI desktop icon and selecting "Run as different user".

No I have my domain admin creds there. This is my problem, if I run as and use my domain admin creds then I can't publish to my persoanl gateway because it puts it to the wrong O365 acocunt (although power BI desktop is fine).

 

maybe it just can't be done..

 

powerbierror2.PNG

Anonymous
Not applicable

When you say publish, do you mean publish to your 'My Workspace'?  I think i've been confused this whole time because you've been using the term 'Personal Gateway', which is a software program you could load into your computer to act as a bridge between the office 365 cloud and your internal network.  The main version that is used now is the On-Premise Data Gateway, which can handle the personal setting.

 

 

When you publish a project, you are publishing to the Power BI Service, you don't publish to a gateway.  Once your report and dataset is within the Power BI Service, you might need a gateway to connect the Office 365 dataset to your on-premise data source.  That is where the gateway comes in and that is where you load in credentials to handle what user account that is.  This account can be different to the account that owns the personal workspace.

 

If your confused its definatly my fault 🙂

 

My understanding is that when I publish to my workspace it will be useless without a gateway that hold the datasource information. So basicly it would all be blank because O365 has no way to see the data without the gateway (like a template thats got no data).

So I am trying to publish my report using my domain user creds (so it goes to my O365 workspace) but then have the datasource (which I thought was stored on the gateway) connect using my domain admin details as that is what has access to the SQL.

 

I'm now assuming that most of that is wrong.. haha

 

 

Actually, i'm sure what I am trying to do just can't be done now. 

I'm still wrapping my head around part of that but most I get.

I have a gateway running on my local machine (testing) and it says that it is connected and can see outside, but O365 doesn't see it. For this reason I haven't managed to see what settigs are avalible in manage gateway.
So this part "Under "Manage Gateways" in the Power BI Service, you can add Data Sources, their credintials, and which users can access this data source. ", dosen't seem possible to me.

Agaiin, I thought that by entering in my email address when creating to connector that was all that is required to have it show in O365. 



powerbierror3.PNG

I think i need to have a play with some different sources (where i can use my domain user account) so that I can see what should be happening.. 

Anonymous
Not applicable

Do you have this option in your Power BI Service?  (Click Cog Icon)

 

Capture.PNG

Yes but it shows the error above (top one) 'You don't have any gateways' even though I can see that my gateway says connected.

Maybe its a networking issue (even though it says connected). I might see if I can dig into that further.

 

Screen below to show you what i see. In red is the service (app.powerbi.com) and on top is my gateway showing all is OK. Also checked the logs and they all say connected.

 

powerbierror4.PNG

Anonymous
Not applicable

The account you used to register the gateway is the same account you use to log onto Office365/Power BI?

Yep.. i think i'll have to have a good dig into it. 

Initially i thought it was just going to be as simple as adding creds somewhere. Now i'm thinking it could be anything.

I might try an push them to set up a full gateway, with DB rights.. Maybe if I get off the personal one it will fix it.

I think that I have convinced them to put in a proper managed gateway somewhere so i'll let this drop and start pushing them for that.

 

Thanks for all your help!

Anonymous
Not applicable

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.

This was super helpful and very simple to understand! Thanks!

Anonymous
Not applicable

What about connecting via gateway to Teradata? Each user in our warehouse has different AD group access which drives their security.  Query banding so to speak.

 

So if I set up a gateway with my credentials and I have high access, everyone will also receive high. How do you pass through credentials to a gateway?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors