Analysis Services Not Detecting Changes in Active Directory Security

Question

Hi,

We are running SSAS 2019 Tabular Models. We have multiple roles setup, each role linked to a security group in active directory. The roles are used to hide fields within the cube for certain users.

We have discovered that if we move a user from one active directory group to another, and therefore now belong to a different role, the users permissions do not change. They continue to have the same access as if they are in the original role. This extends to access to an entire model. If we add them to a new active directory group to give them access to anew model, this also doesn't work.

If the user did not have any permissions to begin with the changes do apply.

The only way to reset the permissions is to rebuild the cube. Refreshing the cube or doing a CreateAndReplace on the roles makes no difference.

We have been using tabular models for 6 years and it's only recently that we've found this issue. It's not a common thing to move people around but in the past we never had this problem. I've found the last two users we've made these changes, they have both failed to get new permissions and a cube rebuild or recreating the user was necessary.

Anyone else had this problem. It sound bizarre and i expect there is a good reason why this is happening and there is an easy fix.

Regards.

Simon.

Answer 1

Hi,

It's definitely an SSAS thing. The above info gave me an idea.

After some research i ran the following:

Select * from $System.Discover_Connections

I showed there was an active connection. I then ran the following to kill that connection.

<Cancel xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<ConnectionID>6097</ConnectionID>
<CancelAssociated>1</CancelAssociated>
</Cancel>

Changing permissions, running this for the relevant connection we reset the security and allow it to be applied.

I am not sure whether this supposed to happen but at least now i've got a solution. Changing of permissions is all done via applications so we could reset the persons connection if we change their permissions.

Answer 2

Yes, if you have a configuration like that it will be slightly different and you will probably get session caching happening at both IIS and SSAS although the default session timeout for both of these is usually around 20 minutes unless it has been configured differently.

SSAS will evaluate the current roles when a session is first established, so if the timeout is longer or something is keeping the session alive this could explain the behavior you are seeing. With the default NTLM authentication signing out and back in will normally force a new session to be established against SSAS. However as you have noticed - doing any sort of process operation will force all open sessions to be closed from the SSAS side. You don't actually have to read any data, you could do something fast like a processDefault or a processRecalc (assuming that the cube is already in a processed state) to flush all the open sessions.

Or as you have found cancelling the session (in your example the CancelAssociated will be cancelling the session) will force the server create a new session at which point it should pick up any group changes.

Answer 3

Is there a way to log people out of AD.

I'm not sure, you'd probably need to ask on an AD forum.

Technically these users are only logging into the cube with AD and aren't actually logged in on a machine.

This is not the default configuration - How did you set this up? Are you using msmdpump with basic authentication? I would expect that each time the user was prompted for a username password that they would be logged in again and with that you would only see the replication delay not the caching behaviour you would get with standard windows authentication.

Answer 4

This is possibly more of an AD issue than something specific to SSAS. If you have multiple domain controllers there are often delays in replicating changes and group membership details are cached on the workstation when the user logs in. So when you move a user between groups you may need to wait 20-30 minutes for the change to replicate to all your AD domain controllers (or you would need to get a domain admin to force an immediate replication). Then if the user is already logged in they would need to sign out and back in (or reboot) to pick up the group membership changes. If you have users that just lock their machines at night or put them to sleep this can extend the duration of this issue.