question

SimonDarlow-5610 avatar image
0 Votes"
SimonDarlow-5610 asked CarrinWu-MSFT commented

Analysis Services Not Detecting Changes in Active Directory Security

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.

sql-server-analysis-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SimonDarlow-5610 avatar image
0 Votes"
SimonDarlow-5610 answered CarrinWu-MSFT commented

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.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @SimonDarlow-5610, I am glad to know that you got a solution for this issue. In order to close this thread, please mark useful replies as answers. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated. Thank you!

0 Votes 0 ·
dgosbell avatar image
0 Votes"
dgosbell answered SimonDarlow-5610 commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,

What you are saying does make sense and could be the issue. Is there a way to log people out of AD. Technically these users are only logging into the cube with AD and aren't actually logged in on a machine.

Regards.

Simon.

0 Votes 0 ·
dgosbell avatar image
0 Votes"
dgosbell answered SimonDarlow-5610 commented

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.





· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

We are using MSMDPUMP. The user stores the domain credentials in manage windows credentials on their machine and these are passed through to the cube. They do not have access to our network accept over this HTTPS connection. So unless their session remains active on the cube i can't see how it would not disconnect. I know MSMDPUMP disconnects after a certain amount of time as the website closes these connections.

This leaves me to think the cube is caching this, and not full disconnecting the user. But even that would be unusual. The cube rebuild is the only activity i've see to allow the new security settings to be applied to the user once they have logged in.

0 Votes 0 ·
dgosbell avatar image
0 Votes"
dgosbell answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.