There has been a lot of buzz and questions around connecting your Office 365 tenant to data located on Premises. This is a common requirement and is now made possible with PowerBI.
Connecting to your data source is made possible with the Data Management Gateway (DMG) The awesome part of the DMG is that is makes the connection outside the firewall. That means, no reverse proxies, custom web services or firewalls to deal with!
Overall, there are few requirements and caveats that I encountered while setting up my demo environment. FYI, I set this up in under and hour, including the time is took to spin up a PowerBI trial.
- The DMG must be installed on a non-SQL server in your on premises environment. I could not get anything to work on my SQL server so I am thinking this has to be done on a member server,
- A new Gateway must be created via your bi admin center.
- You must register the gateway with the on premises DMG client.
- You can then store your credentials for the data sources on premises OR in the cloud. The benefit to storing the creds in the cloud is you can recover quicker from a system failure.
- Let’s get started
First, I setup a trial of PowerBI at http://powerbi.com. After the provisioning was complete, my tenant at https://mybi.sharepoint.com has a newly created asset called PowerBI.
Keep in mind, my site was provisioned but it took a bout 10 minutes for this app to finally show up. Next, I opened the admin center and created a new Gateway. A gateway requires simply a name and description. After giving it a title and description it will generate a new key for you. Save this key as it is the key needed to connect your on premises client to the gateway. If you lose the key you can always regenerate a new key. Also, from here you can download the DMG client.
Next, you will use this key to connect to the gateway. Launch your DMG client and select register a new gateway.
As mentioned previously, doing this on a SQL server did not work. Enter in your new key and select register.
Now we have a connected gateway and we can control the service from this screen.
Next, we need to create a data source.
The best part about this connection info screen is that as soon as I typed in the DB name correctly, the Credentials button became active. That means there is active communication with your on-prem DMG client. Pretty cool!
Next, I clicked on credentials. At this point it will launch a new application and ask for your credentials.
At first attempt, I received these messages.
No support for the tools launcher (Cred manager) in Chrome.
The error came because I was launching the tool from a non-domain joined machine. You need to perform this credential sync from a machine that has access to the SQL server. I relaunched this from the host machine and everything worked fine.
I entered in my GOD rights and finished the wizard. Keep in mind it is not best practice to use a domain admin. This should be a service account that has less permission.
My connection is now complete.
Way cool! In under an hour, we connected a brand new Office 365 trial to my on-premises data source and now it is available for use in all my PowerBI reports.
