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
Ouytree
Frequent Visitor

Publishing an app with on premise data gateway and an excel file in onedrive

Hi all,

 

I'm a sysadmin and my organisation is looking more into powerbi where it can. One particular situation is we have an in-house time recording app that is all on premise with a sql db, so we've got a data gateway set up for it and thats all fine, but now the devs have just told me that they also use an excel file for extra information which currently sits in one of the devs OneDrive.

 

I'm trying to explore any pitfalls or implement some best practices now to future proof the company, and overall gain a better understanding of this.

 

The few ways I can see this going are:

 

  1. Put the file on the data gateway server, make the folder it sits in a share so people can update it (which happens 3-4 times a day currently) then add it as a file data source.
  2. One dev shares the file through his one drive so others can modify it, and publishes this to powerbi pointing one of his data sources to one drive, the other to the on premise sql db
  3.  Create a sharepoint site specifically for the app and put the file(s) in there, share it with the devs and whoever makes updates to it 

My main concern has been what happens if its on the users onedrive and they change their domain password. I imagine it needs to be republished?

 

Any guidance or resources on this are greatly appreciated.

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

You have a close idea, but might be missing a key detail.

 

When you create your data source, in this case a place on sharepoint, you simply give access to the service.  This will be the same system that your SSO relies on.

 

When you develop in Power BI, when you connect to the data source you will have the option of what credentials to use.  By default it will show you your SSO account, but you have the option of choosing another account.  Thats the point in time where you have your developer log in with the service account.  The sign in here is a data source credentials sign in, rather than account you have signed into Power BI with.

 

This will keep your projects developer independant.

View solution in original post

10 REPLIES 10
Anonymous
Not applicable

SharePoint is likely the best solution.  The account issue you raised is why i would suggest using a service account that has a static password.  Give that account read access on sharepoint and that is the user/pass you supply to your gateway.  Now your updates should be person independant and it won't matter who comes and goes from your team or organisation.

Hi Ross,

 

Since the file would be in sharepoint, you cant add it as a datasource for the gateway because its already in the cloud correct?

 

But making a generic account that shares the name of the app to authenticate the sharepoint file for users in power bi desktop should be good enough right?

Anonymous
Not applicable

The On-Premise & Cloud mixing limitation was lifted a few months ago, you just need to check the below both in your manage gateways.

 

As for the service account,  I personally use a specific Power.BI account that i can reuse.  Doing it your way will also work, just think about what is going to work for you when you get to your 100th project and whether you want 100 accounts?

 

Capture.PNG

Hi Ross,

 

I've been working on this and I've run in to some confusion that I'm hoping is easy to explain.

 

In your first reply you suggest using a static service account and password for authenticating against datasets but I'm not sure where this kicks in for something like sharepoint online, as I cant add a sharepoint online datasource to the Gateway because theres no OAuth2 option for authenticating, and in the app.powerbi.com interface under Data sets > Schedule refresh > Data Source Credentials if I try to change them it just does a single-sign on as who i logged in as rather than who I want to use.

 

I also would like to know what happens to dashboard and reports if the user who published them leaves the company, is there a way to publish them so that if someone does leave they dont automatically stop working/get removed when the persons account is disabled and/or deleted?

 

From a sysadmin perspective this is just a nightmare and the only solution that I can see is to get everyone to log in to powerBI desktop and web as a PowerBI.Publisher@company.com account to publish any and everything.

I dont understand why the On Premise Data Gateway isnt just a Data Gateway that you can throw any and all types of datasource credentials in to, and give authority for users to use them without actually telling them anything so if they leave it doesnt just stop working.

 

 

Anonymous
Not applicable

The process that I would use for SharePoint online, is to make use of the Service Account while you are developing.  Once you upload it to the Service, it will just work.

 

 

As for when staff leave the company, uploads are independant of the person who uploaded them.  Having said that, avoid publishing to personal workspaces and only publish your materials to App Workspaces.  Those workspaces are access driven, so as if someone leaves, you can assign another person to that workspace.

Hi Ross,

 

Thanks for the quick reply.

 

I don't understand what service account you are referring to?

 

I've just read the microsoft docs about the app workspace vs personal workspace and all that. Makes way more sense as to how I was expecting it.


Greatly appreciate your help and patience on this one.

Anonymous
Not applicable

A service account is an account you create within your security framework for the purpose of automations.  This account will only be used be a system to talk to another system.  Its not given out to general users to complete actions.  You will commonly use it on servers to call Service jobs, or from automated systems like make calls to a database or other systems.  You can handle the security differently for these accounts, such as only giving them read-only access.

 

There are a few ways you can go about handling what service accounts you create, but whatever structure you use will depend on your business needs.  Some people are in favour of singular service accounts for 1 product line.  I.e. a Power.BI user.  The advantage is you only have 1 account to manage, the disadvantage is you can open up some security flaws.

Others are in favour of 1 service account for each discreet action you are using, this allows you some form of logging to understand who is accessing what and for what purposes.  This does minimise security risks but creates an admin overhead to keep maintained and documented.

 

 

To answer the more direct question of how you would set this up.  Your SharePoint is probably handled through your domain accounts (i.e. ADFS).  Simply create a domain user called Power.BI and set its password to never expire.  When you are doing your development work, rather than connect to a source using your own credentials, impersonate the service account instead.

Hi Ross,

 

Ok it turns out I did understand what you meant.

 

What I'm having difficulty with is in power bi desktop when i go get data > web > https://company.sharepoint.com/sites/SiteName

I have to put credentials in, because this is through our office365 tenant I can only authenticate with an Organizational Account, which does SSO.

 

My problem with this is if a user publishes something that requires their credentials to work rather than this generic system account, and they change their password, it breaks. It's not uncommon in my organization for developers to swap projects so its possible that after 90 days when their password has expired and they've been off this project for 60 days it all goes **bleep** up. THATS what I want to avoid happening.

 

Right now I dont see a way to make this work other than getting all developers to log in to powerbi desktop as this generic account. Or I guess, after the app has been published to the right app workspace, someone logs in as this Power.Bi user and changes it to this accounts credentials (if thats possible).

Anonymous
Not applicable

You have a close idea, but might be missing a key detail.

 

When you create your data source, in this case a place on sharepoint, you simply give access to the service.  This will be the same system that your SSO relies on.

 

When you develop in Power BI, when you connect to the data source you will have the option of what credentials to use.  By default it will show you your SSO account, but you have the option of choosing another account.  Thats the point in time where you have your developer log in with the service account.  The sign in here is a data source credentials sign in, rather than account you have signed into Power BI with.

 

This will keep your projects developer independant.

Hi Ross,

 

I was missing that bit of the conversation. I was sort of actively ignoring it because unfortunately in this present scenario the devs will need read/write access to the excel file so either way im doomed. Although I will keep that solution in mind going forward, I imagine it (and all the info here) will come in handy.

 

Once again thank you for your time and patience. I believe I have enough information here to see organize a plan with many options for the scalability of this

 

Thanks,

Ouytree

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.