Redgate – SSAS Compare



Needing to compare Sql Server Analysis Server ( SSAS ) database across environments, goggled for tools that will facilitate my needs.


Redgate – SSAS Compare

SSAS Compare from Redgate is the tool that Goggle referenced the most.



Downloaded the tool from here ( )



Installation is straight forward…


Screen Shots


Selecting the tools you want to install



End User Licensing Agreement



Customizing the installation folder



Installing Tools



Installation Summary







The tool offers two pathways towards comparing Sql Server Analysis Server ( SSAS ) databases.

Our options are :-

  1. Directly comparing live/active databases
  2. Comparing scripted database file


Comparing Live/active databases


New Comparison

newComparison_20171205_0435AM [brushedup]



Results – Cubes


Results – Data Sources


Results – Data Source Views


Results – Dimension

comparison_Dimensions_20171205_0631AM [brushedup]


Results – Roles





Redgate’s SSAS Compare is a good, small front print, easily accessible tool.

SQL Server – Analysis Services – Error – “A connection could not be made to the data source”


Process ALL Dimensions:Error: The following system error occurred: The user name or password is incorrect.
Process ALL Dimensions:Error: Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of ‘HRDB’, Name of ‘HRDB’.


SQL Server Profiler

Checked via SQL Server Profiler, but nothing came up.


Integration Services Catalog

The errors tabulated above came up.


Object Explorer – Integration Services Catalogs

Reports – Standard Reports – All Executions


SQL Server Analysis Services

Data Source Properties


  1. For Data Source Properties, our “Security Settings – Impersonation Information” is set to “Impersonation Account“.
  2. The setting means a specific account is designated
  3. We reviewed the account and changed it from the cube’s developer to an Active Directory Service Account


Data Source Properties

Data Source Properties – Impersonation Information


Data Source Properties

Data Source Properties – Impersonation Information


SQL Server – Analysis Services – Cannot Connect ( Microsoft.AnalysisServices.AdomdClient )


Users are having problems connecting to the Analysis Services Server.


Error Message



TITLE: Connect to Server

Cannot connect to ....sql01.


A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient)


A connection attempt failed because the connected party did not properly respond after a period of time, 
or established connection failed because connected host has failed to respond z.y.76.188:2383 (System)



Cannot connect to d-isql01.


A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient)

Program Location:

at Microsoft.AnalysisServices.AdomdClient.XmlaClient.GetTcpClient(ConnectionInfo connectionInfo)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.OpenTcpConnection(ConnectionInfo connectionInfo)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.OpenConnection(ConnectionInfo connectionInfo, Boolean& isSessionTokenNeeded)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Connect(Boolean toIXMLA)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.AdomdConnection.IXmlaClientProviderEx.ConnectXmla()
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open()
at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()


A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond xx.xx.xx.xx:2383 (System)

Program Location:

at System.Net.Sockets.TcpClient..ctor(String hostname, Int32 port)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.GetTcpClient(ConnectionInfo connectionInfo)



Resource Monitor

If the server has Microsoft’s Resource Monitor built-in let us use to it to review Network Ports.



  1. Listening Ports
    • Image :-
      • msmdsrv.exe
    • Address :-
      • IPV4 unspecified
      • IPV6 unspecified
    • Port :-
      • 2383
    • Protocol
      • TCP
    • Firewall Status
      • Allowed, not restricted





netstat -an | find "SYN_SENT"

Sample Output


  1. Request was sent to port 2383
  2. Status is SYN_SENT
    • SYN_SENT means waiting on a reply


Open up TCP Port 2383.



Once the Network Port, 2383, is opened up retry access with SSMS.


Network Flow

Also, it makes sense to review the Network Traffic to get a full list of ports to be opened.





Seems Analysis Services exclusively relies on port 2383.

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.

Analysis Services – Error – OLE DB Error : OLE DB or ODBC Error : Login failed for user



Trying to build an Analysis Services’s Cube, but getting the error pasted below:


Error Message


OLEDBOrODBCErrorFailed-20170309-1256PM [BrushedUp]




OLE DB error : OLE DB or ODBC Error : Login failed for user

A connection could not be made to the data source with the DataSourceID of ‘LocalHost’, Name of ‘LocalHost’




Analysis Server


Data Source Properties


DataSourceProperties-BIS-20170309-0124PM (Brushed-up)


  1. Connections
    • Driver :- SQL Server Native Client
    • Data Source :- .
      • . means localhost
    • Authentication
      • Integrated Security=SSPI
    • Database
      • Initial Catalog
        • BIS
  2. Maximum Number of Connections is set to 10
  3. Isolation Level is Read Committed
  4. Impersonation is set to ImpersonateServiceAccount

SQL Server Error Log

Review SQL Server Error Log File

We have two sets of error.

The first set was before we granted SQL Server Instance privilege.

And, the next was after we granted SQL Server Instance privilege, but had yet to grant targeted database privileges.


Could not find a login matching the name provided / Error 18456

 errorLog_20170308_0633PM_to_0635PM (BrushedUp)


Login failed for user ‘NT Service\MSSQLServerOLAPService’. Reason failed to open the explicit specified database / Error 18456 – Severity 14: State 38

errorLog_20170309_0118PM_to_0125PM (Brushed up)



Error Category Section  Message
 Login Not Granted SQL Login
Login Login failed for user ‘LAB\DBSQL$’. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]
Login  Error: 18456, Severity: 14, State: 5.
Login Not granted DB Privilege
 Login Login failed for user ‘NT Service\MSSQLServerOLAPService’. Reason: Failed to open the explicitly specified database ‘BIS’. [CLIENT: <local machine>]
 Login  Error: 18456, Severity: 14, State: 38.




Grant Access to SQL Instance

Grant SQL Instance, Database Connection privileges to account that Analysis Service is running under.


Local Machine

create login [NT Service\MSSQLServerOLAPService] from windows;

Grant Access to Individual Database

Local Machine

use [--database--]

create user [NT Service\MSSQLServerOLAPService] from login [NT Service\MSSQLServerOLAPService];

Grant Object Permission

Local Machine

use [--database--]

exec sp_addrolemember 'db_datareader',  [NT Service\MSSQLServerOLAPService];

SQL Server – Analysis Services – Granting Server level permissions


Granting permissions to the SQL Server Engine is common practice.

But, granting permissions to downlevel services such as Integration, Reporting, and Analysis is not something the DBA does as often.


Analysis Service

In this post we will discuss how to use SQL Server Management Studio to do so.

SQL Server Management Studio ( SSMS )

Here are the steps:

  1. Launch SQL Server Management Studio ( SSMS )
  2. The Connect to Server dialog appears
    • In the Server Type :- Analysis Services
    • Server Name :- Enter Server name
  3. Access the Server’s property
    • Select the Server
    • Right click on your server
    • From the drop-down menu, select Properties
  4. In the “Analysis Server Properties” window
    • Select the Security Tab
    • Review the list of System Administrators
    • Click the Add button to add new Sys Admins


Connect To Server


Server’s Property



Analysis Server Properties – Security




  1. Developer Network
    • Features and Tasks > Multidimensional Models > Roles and Permissions
      • Grant cube or model permissions (Analysis Services)