Microsoft – “On-premises data gateway” – Upgrade

Background

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

Instigator

The need to upgrade was based on notification by MSFT.

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

Image

Text

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.

Download

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

Upgrade

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

Steps

  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

Images

Reminder before you install

Getting ready to install the on-premises data gateway

Choose the Type of gateway you need

Installing…

Installation was successful

 

Signin

Steps

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

 

Image

 

Register

Steps

  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

Image

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

Migrate, Restore, or takeover an existing gateway

Reference

  1. Microsoft
    • On-premises data gateway
      Link

 

 

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

Background

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.

Code


/*  
   Service Start and Stop Time
*/
SELECT 
             TimeGenerated
           , 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] 

FROM '[LOGFILEPATH]'

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


          )
  
     )

ORDER BY
      TimeGenerated desc


Output

Power BI – Configuring MS Analysis Server as a Data Source

 

Background

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

 

History

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

Image

error_ssas (cropped up)

 

Textual

  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

 

 

TroubleShooting

SQL Server Profiler

Image

DoesNotHaveDiscoverPermission-20170310-1202PM ( BrushedUp)

 

Textual

  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

 

Remediation

Security

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

Tabulated

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

 

 

 

ScreenShot

Analysis Service Instance Hierarchy

MSAnalysisService-Hierarchy [CroppedUp]

 

 

Role

General

 

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.

Role-PowerBI-DataSources

 

Cubes

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

Role-PowerBI-Cubes

 

Cell Data

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

Role-PowerBI-CellData (Brushed Up)

 

Dimensions

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.

Role-PowerBI-Dimensions

 

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.

Role-PowerBI-MiningStructures

 

Summary

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

Background

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)
Version13.0.1700.1003
Cluster URI

Diagnostic

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.

relevantservices

 

Why is it down?

MS SQL Server

ErrorLog

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

 

Event Viewer

System Log

Filter
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.

customrange

 

Filter – Filter Current Log

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

filtercurrentlogforjune142017

Explanation

  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
Events Image

Here are the events ordered by Level..

eventssystemforjune142017-20170117-0615pm

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.

 

 

Explanation:

  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

 

Remediation

  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”

servicestartuptype

 

Add Service Dependency

servicedependecy