SQL Server Reporting Services (SSRS) and SIDHistory problem

Hello all-

We are in the middle of a migration and have run into a very strange issue with SQL Server Reporting Services (SSRS).

Here is the AD environment:

Source:

  • Windows 2008 R2 running in 2008 R2 mode
  • Domain: MNPS

Target:

  • Windows 2008 R2 running in 2008 R2 mode
  • Domain: SCHOOLS
  • SQL Server 2008 R2 box running SSRS
  • One-way trust is in place. Target is trusting, Source is trusted

Note the attached graphic.

Capture.JPG

Essentially these roles are explicitly added using accounts from the source (mnps\username) and in the span of 10 minutes, the account changes to the target (schools\username).  All AD accounts have been merged to the target with SIDHistory preserved. Once this occurs, users get denied access to this reporting engine.

Initially I stopped DSA Sync and the issue persisted. Then I rolled back a sample user account, we re-added the source object to SSRS and the account did not change. I then re-merged the object, this time not including SIDHistory and the account did not change.

This system was moved to the target manually. The RUM was not used nor was the SQL Processing Wizard. This due to the nature of the application and the application owner was more comfortable with this approach.

Can you shed any light on would could be happening here?

As always, thanks in advance.

Eric

  • Considering all what you have told the only possible reason for this behavior would be the SID History.

    Unless there are some new fancy attributes, which could also cause this.

    A simple test could be:

    create 2 test accounts in source and grant them permissions on the SQL.

    then migrate both accounts, but migrate only one with sid history, and see what happens.

  • Hi Wally..always good to hear from you.

    We'll try the the new account test but my bigger question is regarding the "fancy attributes." Their schema has been extended to accomodate a specific application. What AD attributes could potentially cause this?

    Thanks...

  • Wally-

    Due to the scope and impact of this issue, I've been told to take the path of expediency and remove SIDHistory from all these objects. Additional testing will be done but let's assume that it is indeed SIDHistory that is the culprit. My concern is the impact by removing this attribute. Please consider:

    • All our resources (file servers, app servers etc.) now reside in the target.
    • All user, group objects are matched between source and target.
    • About 50% of the workstations reside in the TARGET but users log into the source for email (email has not been migrated yet..still working through permissions etc..) The balance of workstations reside in the SOURCE
    • Remember that we have a 1-way trust from target to source (target=trusting, source=trusted)

    By removing this attribute, how will it affect the RUM's operation on workstations? If at all? I'm just trying to think this all the way through before doing anything so drastic.

    Your input is greatly appreciated.

    Eric

  • I was never good with one way trusts, but in my opinion in your current setup

    • Remember that we have a 1-way trust from target to source (target=trusting, source=trusted)

    I would think the sid history in target is useless anyway, because the source is not trusting target and target users cannot use the sid when accessing resources in the source.

    They can still use the sid history when accessing resources located in target, but we all know that sid history is a temp solution and needs to be removed and cleaned up sooner or later. I would suggest to sync or migrate all objects to target, so they all are present in the ADAM DB, and then re-permission computers, in source and in target.

    Plan could be: use ADPW and remove sid history for a few users and see what happens. If anything goes wrong you can quickly re-migrate or resync them and add it back, or use RUM and process the resources. If all is good you proceed and use ADPW and remove sid history for another batch of users, or for all of them.

    https://support.quest.com/SolutionDetail.aspx?id=SOL22583

    This one-way trust still confuses me a lot . If source doesn't trust target then target users have no chance to access resources there, no matter via sid history or because you repermissioned resources and gave target users direct access.

    PS: sid history is also useless when users from source are accessing resources in the taget, even if target trusts the source, sid hisotry just doesn't work that way. Unless source user has as sid history the sid of target user, but I doubt you have done this.

  • Thanks for the detailed reply, Wally.

    Let me clarify:

    All resources (servers, not workstations) have been moved to the target. The only thing in the source is email.

    Users are still logging into the source. The trust in place allows users to still access those resources.

    All objects have been merged between source and target. (i.e. the target and all target objects already existed before the tool was stood up)

    So based upon your statement here: "even if target trusts the source, sid hisotry just doesn't work that way" tells me that we could have merged our objects and not included SIDHistory at all.

    Finally, it sounds like the RUM does not leverage SIDHistory at all. I was concerned about re-permissioning of workstations and that could get broken. Did I get that right?

    Thanks!

  • Yes, Eric, it looks like in your scenario migrating with sid history was never needed and it didn't do you any good.

    RUM and sid history have nothing in common. Sid history is just a temp solution, but RUM does it properly and offers many options, eg. leave source ACL's in place and add target ACL's. And later, when migration is over, you can run RUM once more and remove (cleanup) old source ACL's.

  • Here are my findings:

    1. I can re-produce the problem – with or without a trust in existence.  This confirms that the issue is linked to the SID history value itself and not the trust.
    2. With all resources stored in the Target environment, the potential value of SID history is diminished.  If you have dual-permissioned everything already then there is greatly reduced risk.
    3. The SQL Processing Wizard can both re-permission for users using their Target accounts and revert permissions users still logging on using their SOURCE accounts.  This will correct any discrepancies in SSRS after you remove SID history.  You will need to create separate SQL processing tasks and use Custom Maps for each with different repermissioning options.
  • Thanks to Rob and Wally. Just need to test and implement but I think this is the direction we need to go.

  • Gentlemen-

    Rather odd behavior going on.

    I used ADPW to remove SIDHistory on a subset of test users to ensure things are OK before going global.

    I checked and indeed the attribute is cleared.

    I set the attribute to "skip" and started a full re-sync and the attribute keeps getting re-populated.

    Any ideas?

    Eric

    ps. SQL Processing Wizard does not reset SSRS settings.

    https://support.quest.com/SolutionDetail.aspx?id=SOL83481&category=Solutions&SKB=1&product=Migration%20Manager%20for%20AD

    Resolution

    At this time, the Migration Manager (version 8.8) , SQL Processing Wizard, does not process SQL proxies, nor does it update SQL Server Reporting Services (SSRS) permissions.

  • SID History is not controlled by attribute skipping. It is only controlled by a special check box elsewhere in the sync configuration.

    Regards,

    Robert Sandri

    Solutions Architect & Principal Consultant | Quest Software | Simplicity at Work | 630.631.9705 | www.quest.com<http://www.quest.com/>