Redgate – SSAS Compare

 

Background

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.

 

Download

Downloaded the tool from here ( https://www.red-gate.com/products/experimental-tools/ssas-compare/index )

 

Install

Installation is straight forward…

 

Screen Shots

 

Selecting the tools you want to install

selectTheToolsToInstall_20171295_0419AM

 

End User Licensing Agreement

licenseAgreement_20171295_0420AM

 

Customizing the installation folder

customizingTheInstallationFolder_20171295_0421AM

 

Installing Tools

installing_20171295_0422AM

 

Installation Summary

installationSummary_20171295_0423AM

 

 

Usage

 

Options

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

Image

New Comparison

newComparison_20171205_0435AM [brushedup]

 

 

Results
Results – Cubes

comparison_Cubes_20171205_0639AM

Results – Data Sources

comparison_DataSource_20171205_0634AM

Results – Data Source Views

comparison_DataSourceViews_20171205_0643AM

Results – Dimension

comparison_Dimensions_20171205_0631AM [brushedup]

 

Results – Roles

comparison_Roles_20171205_0645AM

 

 

Summary

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”

Errors

Message
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’.

TroubleShooting

SQL Server Profiler

Checked via SQL Server Profiler, but nothing came up.

 

Integration Services Catalog

The errors tabulated above came up.

Images

Object Explorer – Integration Services Catalogs

Reports – Standard Reports – All Executions

 

SQL Server Analysis Services

Data Source Properties

Diagnosis

  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

Images

Original
Data Source Properties

Data Source Properties – Impersonation Information

 

Revised
Data Source Properties

Data Source Properties – Impersonation Information

 

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

Background

Users are having problems connecting to the Analysis Services Server.

 

Error Message

Image

Text


TITLE: Connect to Server
------------------------------

Cannot connect to ....sql01.

------------------------------
ADDITIONAL INFORMATION:

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)


Text


===================================

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)


TroubleShooting

Server

Resource Monitor

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

Image

Explanation

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

 

Client

netstat

Script


netstat -an | find "SYN_SENT"

Sample Output

Explanation

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

Remediation

Open up TCP Port 2383.

 

Review

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.

WireShark

Output

 

Explanation

Seems Analysis Services exclusively relies on port 2383.

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.

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

 

Background

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

 

Error Message

Image

OLEDBOrODBCErrorFailed-20170309-1256PM [BrushedUp]

 

Textual

 

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’

 

TroubleShooting

 

Analysis Server

Database

Data Source Properties

Image

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

 

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

Image

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)

Tabulate

 

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.

 

 

Remediation

Grant Access to SQL Instance

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

Login

Local Machine

 
create login [NT Service\MSSQLServerOLAPService] from windows;
 

Grant Access to Individual Database

Local Machine

 
use [--database--]
go

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

Grant Object Permission

Local Machine

 
use [--database--]
go

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

SQL Server – Analysis Services – Granting Server level permissions

Background

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

connecttoserver-brushed-up

Server’s Property

accessserverproperties-brushedup

 

Analysis Server Properties – Security

analysisserverproperties-security-20170109-1053am

 

References

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