SSAS users randomly losing connection

  • richardmgreen1

    SSCrazy Eights

    Points: 9717

    Hi all

    We've got 5 multidimensional cubes and access is granted by AD groups.
    There is one role per cube with a "catch-all" role that is listed as "Read all cubes".  This is a special role for our reporting team and allows them access to all the cubes.  The other cubes are locked down to different AD groups with just access to that cube (read-only).

    Unfortunately, the reporting team keep getting kicked off the cubes with an error along the lines of the following:-
    "The database does not exist or you do not have access to it".

    The simple solution (or so I've found) is to remove the relevant AD group from our "Read all cubes" role and put it back in.  That solves the problem temporarily but I'd like to get to the bottom of it.

    I've had a look in the msmdsrv.txt (but that only covers part of 2018 and nothing since although logging appears to be switched on) but that doesn't give me anything obvious.
    I've had a (brief) look in the windows error logs but I'm not sure what I'm looking for.

    Has anyone any ideas as to:-
    a) why this is happening?
    b) where I can find some more error logs to try and help me get to the bottom of it?

    The really odd bit is that, if our reporting team (with access to everything) loses access, so does everyone else.
    The solution of removing their AD group andreinserting it seems to solve the problem for everyone.

    I'm guessing that it's something along the lines a (potentially) corrupt AD group but I don't want to ask our IT department to create a new one without some proof that it's definitely that causing the issue.

    One other thing to note is that, because we have a DEV and PROD environment, we don't deploy the roles with the cubes if we make changes.
    We have a job on the PROD server that creates the roles and assigns users and permissions.
    That's on a schedule to run every day (which is probably overkill) but it ensures we don't forget to run it if we have to deploy the cube for any reason.

    On a side note, is it possible to run a post-deployment script when deploying cubes?

    Any help on this would be greatly appreciated.

    Please feel free to ask for any pertinent info you may need.

  • Sue_H

    SSC Guru

    Points: 89891

    richardmgreen1 - Thursday, February 28, 2019 4:36 AM

    Hi all

    We've got 5 multidimensional cubes and access is granted by AD groups.
    There is one role per cube with a "catch-all" role that is listed as "Read all cubes".  This is a special role for our reporting team and allows them access to all the cubes.  The other cubes are locked down to different AD groups with just access to that cube (read-only).

    Unfortunately, the reporting team keep getting kicked off the cubes with an error along the lines of the following:-
    "The database does not exist or you do not have access to it".

    The simple solution (or so I've found) is to remove the relevant AD group from our "Read all cubes" role and put it back in.  That solves the problem temporarily but I'd like to get to the bottom of it.

    I've had a look in the msmdsrv.txt (but that only covers part of 2018 and nothing since although logging appears to be switched on) but that doesn't give me anything obvious.
    I've had a (brief) look in the windows error logs but I'm not sure what I'm looking for.

    Has anyone any ideas as to:-
    a) why this is happening?
    b) where I can find some more error logs to try and help me get to the bottom of it?

    The really odd bit is that, if our reporting team (with access to everything) loses access, so does everyone else.
    The solution of removing their AD group andreinserting it seems to solve the problem for everyone.

    I'm guessing that it's something along the lines a (potentially) corrupt AD group but I don't want to ask our IT department to create a new one without some proof that it's definitely that causing the issue.

    One other thing to note is that, because we have a DEV and PROD environment, we don't deploy the roles with the cubes if we make changes.
    We have a job on the PROD server that creates the roles and assigns users and permissions.
    That's on a schedule to run every day (which is probably overkill) but it ensures we don't forget to run it if we have to deploy the cube for any reason.

    On a side note, is it possible to run a post-deployment script when deploying cubes?

    Any help on this would be greatly appreciated.

    Please feel free to ask for any pertinent info you may need.

    Does this happen in both DEV and PROD where users just suddenly get kicked off?
    Have you looked at the windows logs on the server to see if there is an issue with communication between the server and AD?

    Sue

  • richardmgreen1

    SSCrazy Eights

    Points: 9717

    Hi Sue

    There's not a lot of people on our DEV server so  I'm not sure.
    Where would you recommend in the Windows logs?  I tried looking but nothing obvious jumped out at me.

  • Sue_H

    SSC Guru

    Points: 89891

    richardmgreen1 - Thursday, March 7, 2019 1:35 AM

    Hi Sue

    There's not a lot of people on our DEV server so  I'm not sure.
    Where would you recommend in the Windows logs?  I tried looking but nothing obvious jumped out at me.

    The system event log often will have network related events. Try filtering out any informational messages when you go through it.

    Sue

  • richardmgreen1

    SSCrazy Eights

    Points: 9717

    Thanks Sue

    I'll have another dig around and see what I can find.

  • richardmgreen1

    SSCrazy Eights

    Points: 9717

    Hi Sue

    A bit more info.
    I've found a lot of errors for SChannel that seem to be in pairs.

    The first one is:-
    A fatal alert was generated and sent to the remote endpoint. This may result in termination of the connection. The TLS protocol defined fatal error code is 48. The Windows SChannel error state is 552.

    Followed by:-
    The certificate received from the remote server was issued by an untrusted certificate authority. Because of this, none of the data contained in the certificate can be validated. The SSL connection request has failed. The attached data contains the server certificate.

    I'm assuming I need to refer this to our server team but can you please advise?

    Thanks

    Richard

  • Sue_H

    SSC Guru

    Points: 89891

    richardmgreen1 - Friday, March 8, 2019 7:46 AM

    Hi Sue

    A bit more info.
    I've found a lot of errors for SChannel that seem to be in pairs.

    The first one is:-
    A fatal alert was generated and sent to the remote endpoint. This may result in termination of the connection. The TLS protocol defined fatal error code is 48. The Windows SChannel error state is 552.

    Followed by:-
    The certificate received from the remote server was issued by an untrusted certificate authority. Because of this, none of the data contained in the certificate can be validated. The SSL connection request has failed. The attached data contains the server certificate.

    I'm assuming I need to refer this to our server team but can you please advise?

    Thanks

    Richard

    Yes that's on the server end of things - it's an issue with the certificate. Here is a reference to the SChannel error codes if needed:
    SSL/TLS Alert Protocol & the Alert Codes

    Sue

  • richardmgreen1

    SSCrazy Eights

    Points: 9717

    Hi Sue

    I think the SChannel errors were a red-herring. and I think I've found the cause of the issue.

    I ended up looking through the XMLA scripts that we were using to assign the roles, etc. after we'd done a deploy.

    I tested it by reassigning permissions to users and then running the job which immediately killed off anyone's access.

    I finally noticed that, although the roles were being created and users were being assigned, there were no read permissions being allocated to any of the roles.
    I've assigned the permissions manually and then rescripted the roles creation to some fresh job steps and all now seems to be good.

    I'm not sure if anyone needs drillthrough permissions to be assigned (which would be another change role/redo script but that's a job for another day if anyone starts shouting.

  • Sue_H

    SSC Guru

    Points: 89891

    richardmgreen1 - Monday, March 11, 2019 4:30 AM

    Hi Sue

    I think the SChannel errors were a red-herring. and I think I've found the cause of the issue.

    I ended up looking through the XMLA scripts that we were using to assign the roles, etc. after we'd done a deploy.

    I tested it by reassigning permissions to users and then running the job which immediately killed off anyone's access.

    I finally noticed that, although the roles were being created and users were being assigned, there were no read permissions being allocated to any of the roles.
    I've assigned the permissions manually and then rescripted the roles creation to some fresh job steps and all now seems to be good.

    I'm not sure if anyone needs drillthrough permissions to be assigned (which would be another change role/redo script but that's a job for another day if anyone starts shouting.

    Yes running a script, job that changes permissions could do this although it doesn't sound like it's really random.

    Sue

  • richardmgreen1

    SSCrazy Eights

    Points: 9717

    It seemed to be random, but since I changed the job steps no-one's compalined about losing connections.
    Looks like they were coming to see me randomly.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply