locked
Issue in LDAP group replication - Obsolete groups RRS feed

  • Question

  • Hi,

    I would like to find out whether the following scenario is a bug in SQL Server 2005 and if any patch is there to fix it.

     The system in use is SQL Server 2005 - OS : Windows 2003 server. It is now used to create OLAP reports for end users(client side).

    ·     Authentication : LDAP(Enterprise Directory)

    ·     Problem : User group (say group1) present in the LDAP has a role defined corresponding to it in SQL server.

    ·     The user group became obsolete in the LDAP as its expiry date exceeded.

    ·     The users were not able to access the SQL Server OLAP reports.

    ·     Then, the user group was re-activated in the Enterprise Directory.

    ·     But, the re-activation does not reflect in the SQL Server.

    ·     The non-reflection is in case of obsolete->reactivated state change only.

    ·     Other changes(new group creation, user deletion etc) are properly reflected.

    The following test cases were executed :

    1.  Added one dummy GMS group I_EXT_ACCESS to SQL Analysis Server.

    2.  User in this group has confirmed that they are able to access reports without any issue.

    3.  Made the dummy group to obsolete in LDAP and now users are not able to access the reports.

    4.  Again reactivate the dummy group in LDAP and after that users are not able to access.

    5.  We also restarted the MSSQLServerOLAPService since this is response for cubes and still issue is exists.

    6.  Also we restarted the whole MS SQLServer and still the issue is exists.

    P.S : This SQL server is meant for OLAP reports- Data is in form of SQL Server cubes.

    Could you provide us a feasible solution for this issue please?

    Already, a solution was suggested to log off the OLAP server and log in again. It was said that the OLAP server might be using the old token. We even restarted it. But, still it does not work.

     

    • Edited by HarishAccy Thursday, August 5, 2010 8:52 AM
    Tuesday, August 3, 2010 12:58 PM

Answers

  • I recommend that you perform a test with an empty udl file. For example, create a empty file named test.udl on your desktop, double click it to test the connection to your OLAP cube. If this test succeeds with an user in your GMS group, it means that the credential has no problem after you reactive the account. The problem may be related to your OLAP reports and you need to further check the connection configuration in your OLAP reports.

    If this does not work, this is probably a security issue at the server side. Though I could not reproduce your issue in a Active Directory domain, you may try dropping and re-creating the account with required permissions on the olap server side to see if it helps.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Friday, August 6, 2010 9:41 AM
  • HarishAccy,
    So you added the dummy group I_EXT_ACCESS as an user into the role, right? Unfortunately I could not reproduce your issue at my side. Probably there is an AD issue for further diagnostic. You may try deleting and readding the users/group to the role or re-creating the role to see if it helps.

    If this issue persists, I recommend that you submit a support incident to Microsoft CSS for higher level in-depth troubleshooting. To obtain the phone numbers for specific technology request please take a look at the web site listed below.

    http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS

     

    If you are outside the US please see http://support.microsoft.com for regional support phone numbers.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Wednesday, August 11, 2010 10:12 AM

All replies

  • There is no SQL Server 2004. Could you please post the version here? You can run SELECT @@VERSION to check the output. In addition, please help clarify:

    what is the error message?

    What is the connection string defined in your OLAP reports?

    Which application run your OLAP reports?


    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Thursday, August 5, 2010 8:24 AM
  • SQL Server 2005

     

    Users view reports via a web based UI - Infoview.

     

    User able to login, but "Access denied" when they try to open the OLAP report since the access risghts are derived in SQL server for OLAP reports from Enterprise directory. There access rights are not reactivated automatically for users when their user groups go obsolete and reacttivated at Enterprise directory.

     

    We are not sure about the connection properties between SQL server and Enterprise directory.

    Thursday, August 5, 2010 10:51 AM
  • I recommend that you perform a test with an empty udl file. For example, create a empty file named test.udl on your desktop, double click it to test the connection to your OLAP cube. If this test succeeds with an user in your GMS group, it means that the credential has no problem after you reactive the account. The problem may be related to your OLAP reports and you need to further check the connection configuration in your OLAP reports.

    If this does not work, this is probably a security issue at the server side. Though I could not reproduce your issue in a Active Directory domain, you may try dropping and re-creating the account with required permissions on the olap server side to see if it helps.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Friday, August 6, 2010 9:41 AM
  • The test with the empty UDL file has been done. It works. The problem is not when the user tries to login to the data source/server. His authentication is fine. I would describe the problem in one more way :

    1. User "A" has been granted a role to view the report "X".

    2. The authentication is from Enterprise Directory(ED) using LDAP.

    3. When the user account is reactivated at ED after expiring, authentication works fine for the SQL server.

    4. But the "roles" do not work as before expiry and reactivation. i.e., After reactivation, User "A" is able to login to the server, but he/she cannot view the report "X" because roles do not work.

    Tuesday, August 10, 2010 6:54 AM
  • HarishAccy,
    So you added the dummy group I_EXT_ACCESS as an user into the role, right? Unfortunately I could not reproduce your issue at my side. Probably there is an AD issue for further diagnostic. You may try deleting and readding the users/group to the role or re-creating the role to see if it helps.

    If this issue persists, I recommend that you submit a support incident to Microsoft CSS for higher level in-depth troubleshooting. To obtain the phone numbers for specific technology request please take a look at the web site listed below.

    http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS

     

    If you are outside the US please see http://support.microsoft.com for regional support phone numbers.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Wednesday, August 11, 2010 10:12 AM