Loading SSAS Tabular Databases in SSMS fails for Non-admin users with “Failed to retrieve data for this request”
I was working with one of my customers where he came across the following issue. I thought of sharing the info related to this, because I didn’t find any articles online that talks about this issue. The issue is, users are getting the below error when they are connecting to SSAS tabular instance using SQL Server Management studio.
Stack trace:
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
------------------------------
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server\&LinkId=20476
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.NavigableItemBuilderDataReader.RunQuery()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.NavigableItemBuilderDataReader.Process()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.NavigableItemBuilderDataReader.get_PropertyNames()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.BuildDynamicItemWithQuery(IList`1 nodes, INodeInformation source, INavigableItem sourceItem, String urnQuery, Boolean registerBuilder, Boolean registerBuiltItems)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.BuildDynamicItem(IList`1 nodes, INodeInformation source, INavigableItem sourceItem, IFilterProvider filter)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.Build(IList`1 nodes, INodeInformation source, INavigableItem sourceItem, IFilterProvider filter)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItem.RequestChildren(IGetChildrenRequest request)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ExplorerHierarchyNode.BuildChildren(WaitHandle quitEvent)
==================================
Object ID property was not found (Microsoft.SqlServer.OlapEnum)
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Smo.Olap.OlapEnumObject.PopulateDataSet(EnumResult erParent, String sResponse)
at Microsoft.SqlServer.Management.Smo.Olap.OlapEnumObject.GetData(EnumResult erParent)
at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()
at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)
at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)
However, when he (who is an SSAS administrator) tries to connect to SSAS Tabular instance, he is able to see the databases without any issues. Even the applications like PowerBI are able to connect to SSAS tabular instance and pull the data successfully. But the issue is happening only when the users(non-admins) connect to SSAS tabular instance from SQL Server Management studio.
We looked at the SSAS logs and found the following error “Error: An error occurred when loading the Database Permissions.” is being logged.
We decided to capture a SSAS profiler trace to check if there are any issues. In the SSAS profiler trace, we figured out that it is trying to load the Database permissions file of a database. When we looked at the database, we found that the database is in SUSPECT mode.
After removing the corrupted SSAS database, the users were able to connect to SSAS and list the databases in SQL Server Management Studio.
Author: Sarath Babu Chidipothu – Support Escalation Engineer, SQL Server BI Developer team, Microsoft
Reviewer: Kane Conway – Support Escalation Engineer, SQL Server BI Developer team, Microsoft