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.
- Status Code : 400
- Error Code :- DM_GWPipeline_Gateway_DataSourceAccessError
- Underlying Error code :- -1055391722
- Underlying Error message :- The user does not have permission to call the Discover method
- DM_ErrorDetailNameCode_UnderlyingHResult :- -2146233088
SQL Server Profiler
- Event :- Server State Discover Begin / Session Initialize / Server State Discover
- 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.
||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
||List principals ( OS or Active Directory Accounts) that are earmarked for this role.
|| Added our Service\Proxy granted
||Permissions on Data Source to read data and definition
||Granted our account permission to read data and definition
||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
||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
||List all dimensions. And, allow permissioning of Read & Write data, definition, and processing.
||Granted ability to read data, read definition, and process dimensions.
||Allow global database permission to be granted or granular permission to individual dimension \ attributes.
||Settled on default global permissioning
||List all mining structures
||We have not enabled mining ability and so did not have that option enabled
Analysis Service Instance Hierarchy
Here we add our surrogate account to the define role, rolePowerBI
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.
Granted Read, Read-Contingent, and Read-Write permissions to the Cube’s content.
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.
List the various dimensions and attributes.
Again we accept the default of granting permissions to all members.
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.