Microsoft – “On-premises data gateway” – Upgrade


Documenting the steps we took to upgrade our Power BI “on premise data gateway“.


The need to upgrade was based on notification by MSFT.

Upon visiting Microsoft’s Power Web site ( ), the requester saw this notification:



Your On-premises data gateway version is older than August 2017.

This version might use TLS 1.0 protocol, which is being disabled on March 15th, 2018.

Please upgrade your On-premises data gateway to the latest version.


Please download the latest version of On-premises data gateway.

The name of the installer is GatewayInstall.exe

As of today, 2018-Feb-8th, here is the link.

The detail on the current version is:

  1. Version # 14.16.6524.1
  2. Date Published :- 1/22/2018


The steps for installing and registering the clients are:

  1. Install Client on local computer
  2. Connect to PowerBI.Com with email address
  3. Register Client

Client Installation


  1. Reminder before you install
    • Guideline
      • Computer that is always on and not asleep
      • The gateway will perform more slowly on a wireless network
  2. Install Location
    • Target folder
    • Accept term of use
  3. Choose the Type of gateway you need
    • Available choices are
      • On Premises data gateway ( recommended )
      • On Premises data gateway ( personal )
  4. Installing…
  5. Prepare for registration
    • Email Address to use for registration


Reminder before you install

Getting ready to install the on-premises data gateway

Choose the Type of gateway you need


Installation was successful




  1. Launch Web Browser
  2. Enter the URL into the address bar






  1. Register a new gateway or Re-use existing existing gateway
    • Options
      • Register a new gateway on this computer
      • Migrate, restore, or takeover an existing gateway
  2. Migrate, restore, or takeover an existing gateway
    • Available gateway clusters
      • Choose from available gateway clusters
    • Available gateways
      • Choose gateway
    • Recovery Key
      • Enter “Recovery key


Register a new Gateway or Migrate/Restore/Or Takeover Existing Gateway

Migrate, Restore, or takeover an existing gateway


  1. Microsoft
    • On-premises data gateway



Windows – Reviewing Services Start and Stop Times via Event Viewer & Log Parser Studio


Received an Incident this morning stating that out Power BI Gateway Service was done.

Spent a bit of time trying to see what brought the service down.


   Service Start and Stop Time
           , TO_STRING(TimeGenerated, 'yyyy-MMM-dd hh:mm tt') as [Timestamp] 
           , Message as [MessageLogged]
           , Strings as [StringsRaw] 
           , SUBSTR(Strings, 0, LAST_INDEX_OF(Strings,'|'))  as [ServiceName] 
           , SUBSTR(Strings, ADD(LAST_INDEX_OF(Strings,'|'), 1) , Strlen(Strings)) as [ServiceStatus] 


          ( EventID = 7036 )
                 ( Message like '%SQL%' )
              or ( Message like '%gateway%' )


      TimeGenerated desc


Power BI – Configuring MS Analysis Server as a Data Source



As part of a recent deployment cycle, we needed to provision connectivity from Power BI to our internal MS Analysis Server database \ cube.



A lot of the plumbing predates me and so I will not have an opportunity to talk about setting up the Gateway, opening up the firewall ports to allow Web Service traffic from the Power BI Internet Portal to our internal Gateway Host.

Knowing that these things were already setup and that we were set up to consume traditional SQL Server relational data meant most of the connectivity work was already done.


Error Message


error_ssas (cropped up)



  1. Status Code : 400
  2. Error Code :- DM_GWPipeline_Gateway_DataSourceAccessError
  3. Underlying Error code :- -1055391722
  4. Underlying Error message :- The user does not have permission to call the Discover method
  5. DM_ErrorDetailNameCode_UnderlyingHResult :- -2146233088




SQL Server Profiler


DoesNotHaveDiscoverPermission-20170310-1202PM ( BrushedUp)



  1. Event :- Server State Discover Begin / Session Initialize / Server State Discover
  2. Event :- Error
      • TextData :- The user does not have permission to call the Discover method




Using SQL Server Management Studio ( SSMS ), connect to SQL Server Analysis Service, review and augment access privileges to the designated account.


Page Definition Granted
General Define the Role, it’s name, general description and database permission.  The general database permissions are Full Control, Process Database, and read definition At the database level granted all listed permissions – Full Control, Process database, and read permission
Membership List principals ( OS or Active Directory Accounts) that are earmarked for this role.  Added our Service\Proxy granted
Data Source Permissions on Data Source to read data and definition Granted our account permission to read data and definition
Cubes Lists all cubes and allow us to define read, write, and drillthrough, and process Granted our account permissions to read, write, drillthrough, and process all cubes
Cell Data Reading of cell content, reading of cell content contingent on cell security, and reading & writing of cube’s content Granted read, read based on cell contingent security, and write ability
Dimensions List all dimensions.  And, allow permissioning of Read & Write data, definition, and processing. Granted ability to read data, read definition, and process dimensions.
Dimension Data Allow global database permission to be granted or granular permission to individual dimension \ attributes. Settled on default global permissioning
Mining Structures List all mining structures We have not enabled mining ability and so did not have that option enabled





Analysis Service Instance Hierarchy

MSAnalysisService-Hierarchy [CroppedUp]






Role Membership

Here we add our surrogate account to the define role, rolePowerBI

Role-PowerBI-Membership [CroppedUp]


Data Sources

We grant our Role ability to read both data and definition on the DataSource.




Granted Read, Write, Drill Through, and Process access to the Local Cube.



Cell Data

Granted Read,  Read-Contingent, and Read-Write permissions to the Cube’s content.

Role-PowerBI-CellData (Brushed Up)



All of the Cube’s dimensions are itemized.

We accepted the default choice which is to be able to Read Data, Read Definition, and Process Data.



Dimension Data

List the various dimensions and attributes.

Again we accept the default of granting permissions to all members.

Role-PowerBI-DimensionData (Brushed Up)


Mining Structures

In our case we did not have mining structures defined and consequently there was nothing to accept nor customize.




Unfortunately, this is a public place holder.

Our error was unable to call the Discover method.

I worked with the Subject Matter Expert, SME,  who came to us.

I was afraid it was a Network Connectivity Issue, but he assured me it was not.

He assured with me that though Power BI is publicly hosted in the clouds it relies on Web Services over HTTP & HTTPS to communicate with the gateway.

And, since our gateway service is hosted on the same box as our two data sources, SQL Server relational and OLAP, we should not be encumbered by the Network Firewall.

So we went in and reviewed the Data Source setup for relational engined.

Noted that it connects via SQL Authentication.

Knowing that SQL Server Analysis Service does not support SQL Authentication, just Integrated OS Authentication, we configured the data source to use one of our Service Accounts.

The steps listed above are the ones we took to grant permissions to the SSAS Cube.

Power BI Gateway Service – “The on-premises data gateway service” is offline


Application Subject Matter Expert ( SME ) called this morning to report that his application is down.

Error Message

Here is the error message

Couldn't load the data for this visual
The gateway is either offline or could not be reached.
Please try again later or contact support. If you contact support, please provide these details.
Error CodeDM_GWPipeline_Client_GatewayUnreachable
Activity ID5a763a2d-d357-34ab-5396-ec568baae17a
Correlation ID7b1ee57b-c957-d313-125a-08b550e3ab1b
Request ID4bcbe543-5cd3-9787-8ad4-47cdb93842f4
Time    Tue Jan 17 2017 09:57:16 GMT-0800 (Pacific Standard Time)
Cluster URI


Control Panel – Services Applet

Looked for the Gateway Service and in our case it is on the same box as Our SQL Server Engine.

Using the services applet in Control Panel, confirmed that the service is indeed stopped.

Quickly restarted it.



Why is it down?

MS SQL Server


We checked MS SQL Server and know that the errorlogs were recycled on Jan 14th, 2017.


Event Viewer

System Log

Filter – Custom Range

Let us set our date range to encompass Jan 14th, 2017.

And, so 12 AM to 12:55 PM on Jan 14th, 2017.



Filter – Filter Current Log

Here is what our filter looks like once we narrowed on Jan 14th.



  1. Filter Out events that are causing a lot of noises.
    • In our case most of the noises were Events 140 & 157
    • Place a minus in front of an event to filter them out
    • Event ID = 140
      • Event ID :- 140 NTFS Warning
        The system failed to flush data to the transaction log. Corruption may occur in VolumeId:<> DeviceName: \Device\HarddiskVolume<>.(A device which does not exist was specified.).
    • Event ID = 157
      • Event ID :- 157 “Disk # has been surprise removed”


Events Image

Here are the events ordered by Level..


Events Image
Level Date and Time Source  Event ID  General
 Error  1/14/2017 1:25:31 AM  Service Control Manager  7000  The SQL Server Integration Services 12.0 service failed to start due to the following error:
The service did not respond to the start or control request in a timely fashion.
 Error  1/14/2017 1:26:51 AM  Service Control Manager  7000 The On-premises data gateway service service failed to start due to the following error:
The service did not respond to the start or control request in a timely fashion.
 Error  1/14/2017 1:25:31 AM  Service Control Manager  7009 A timeout was reached (30000 milliseconds) while waiting for the On-premises data gateway service service to connect.




  1. Two of our services are timing out.  And, those are:
    1. SQL Server Integration Services
    2. And, On-Premises Data Gateway Service
  2. Two types of events are listed
    • Event ID :- 7000
      • Service Start Timeout
    • Event ID :- 7009
      • Timeout was reached



  1. Change Service Mode from Automatic to “Automatic Delayed Start
  2. Change Service Dependency
  3. Write a start service Script and schedule through “Task Scheduler


Change Start Mode from Automatic to “Automatic Delayed Start”



Add Service Dependency