Creating a Demo Power BI Data Gateway using an Azure Virtual Machine

  • Article

PLEASE READ CAREFULLY

Before getting into the topic of this post, I want to clarify that what I am describing here is an UNSUPPORTED configuration.  There are very specific reasons I am setting up my environment in the manner described below, and anyone repeating these steps should understand that they are accepting all responsibility for deviating from standard guidance.

In addition, I want to clarify that these steps apply to the products released in February 2014.  As these products evolve rapidly – one of the great benefits of the cloud – these steps may become quickly out of date.

 NOTE: Gerhard Brueckl has documented another way to implement a very similar demo environment.  Please check out his post here.

I frequently need to setup trial Power BI environments and as part of my demonstration I need to show the OData feed feature of the Power BI Data Gateway in action.  In a standard deployment, the Data Gateway would be installed on a server that is part of a domain which has been federated with Office 365, but as I am implementing a demonstration environment that is not part of a formal domain (and may be working with multiple deployments simultaneously), I need to take a non-standard approach to my setup.

For my deployment, I am using a client system that is part of my corporate domain, a trail Power BI site with which I have no Active Directory federation, and an Azure Virtual Machine running Windows Server 2012 R2 with SQL Server 2012 SP1 which also has no Active Directory affiliations.  To setup the Power BI Data Gateway, I follow the steps outlined here. I use HTTPS with the default port of 8050 and a self-signed certificate created via makecert per these instructions. When executing makecert, I use the short server name so that if the fully qualified name of my Azure VM was myserver.cloudapp.net, I would use myserver as the server name in that step. I then complete the setup of my data source and OData feeds, again using the standard steps.

At this point, the Data Gateway and the data source are fully configured and should appear to be in good working order from within the Power BI Admin Center.  The challenge is now to get my client system to properly communicate with them.  To do this, I do the following:

  1. Login to the Azure VM and record the public IP address.  This is displayed on the desktop background for easy access. Please note, this address is subject to change so that you may need to revisit this and the next step over time.
  2. In the HOSTS file of my local machine (located at C:WindowsSystem32DriversEtc on most Windows systems), add the following, space-delimited entry:

Public.IP.Address servername

In this last step, the server name is the same short server name as used in earlier steps.

With the HOSTS file modified, I can now locate the server used as my data source.  My next step is to install the certificate used to encrypt the HTTPS channel to my client system. To do this, I do the following:

  1. Open Internet Explorer
  2. Navigate to the HTTPS endpoint associated with the OData feeds.  If my server was named myserver, this endpoint would look like this: https://myserver.cloudapp.net:8050.
  3. On the resulting warning page, click the Continue to This Website (Not Recommended) option.
  4. When prompted with a Windows security prompt, click Cancel.  The address bar will now display a Certificate Error.
  5. Click on the Certificate Error message in the address bar to produce a message regarding a Mismatched Address.  On the bottom of that message, click the View Certificates option.
  6. Clicking View Certificates will open a Certificate dialog.  From the General tab of the dialog, click the Install Certificate button to launch the Certificate Import Wizard.
  7. Within the Certificate Import wizard, select Local Machine as the Store Location and click Next.
  8. On the resulting page of the wizard, select the Place All Certificates in the Following Store option and click the Browse button to select the Trusted Root Certification Authorities location. Click OK and Next until the import process is completed.

Now I can launch Power Query, perform an Online Search to locate data sources I’ve exposed as OData feeds via my Power BI Data Gateway, and pull data through those feeds into my workbook. Be sure to remember to sign-in to the Power Query add-in using the login (Organizational Account) associated with your Power BI site.