none
SSRS Authentication - Active Directory out of sync

    Question

  • I recently had my Active Directory accounts renamed, and SSRS has not yet picked up on the change.  So when I browse our SSRS native-mode site, any changes I perform are listed under my old ID.  When it comes to subscriptions, this causes issue, as my old ID is gone.

    What steps do I need to take to re-sync my server(s) with Active Directory to fix this?

    Deployment: Web Server, Report Server, Database Server

    Software: SQL Server 2012, Windows Server 2008 R2

    Tuesday, July 16, 2013 7:22 PM

Answers

  • Hi Ahren,

    In this condition, you need to change the subscription owner to the current valid AD account. To do this, we can use the following T-SQL statements:

    DECLARE @OldUserID uniqueidentifier
    DECLARE @NewUserID uniqueidentifier
    SELECT @OldUserID = UserID FROM dbo.Users WHERE UserName = 'DOMAINA\OldUser'
    SELECT @NewUserID = UserID FROM dbo.Users WHERE UserName = 'DOMAINA\NewUser'
    UPDATE dbo.Subscriptions SET OwnerID = @NewUserID WHERE OwnerID = @OldUserID

    Reference:
    http://blogs.msdn.com/b/miah/archive/2008/07/10/tip-change-the-owner-of-report-server-subscription.aspx

    Regards,
    Mike Yin

    If you have any feedback on our support, please click here


    Mike Yin
    TechNet Community Support

    • Marked as answer by Ahren P Thursday, July 18, 2013 2:53 PM
    Thursday, July 18, 2013 7:50 AM

All replies

  • Hi Ahren,

    In this condition, you need to change the subscription owner to the current valid AD account. To do this, we can use the following T-SQL statements:

    DECLARE @OldUserID uniqueidentifier
    DECLARE @NewUserID uniqueidentifier
    SELECT @OldUserID = UserID FROM dbo.Users WHERE UserName = 'DOMAINA\OldUser'
    SELECT @NewUserID = UserID FROM dbo.Users WHERE UserName = 'DOMAINA\NewUser'
    UPDATE dbo.Subscriptions SET OwnerID = @NewUserID WHERE OwnerID = @OldUserID

    Reference:
    http://blogs.msdn.com/b/miah/archive/2008/07/10/tip-change-the-owner-of-report-server-subscription.aspx

    Regards,
    Mike Yin

    If you have any feedback on our support, please click here


    Mike Yin
    TechNet Community Support

    • Marked as answer by Ahren P Thursday, July 18, 2013 2:53 PM
    Thursday, July 18, 2013 7:50 AM
  • While this works for existing subscriptions, my issue is that I am continuing to "own" new subscriptions, report modifications, etc, with my old AD account name, even though I am signed in with the new name.  Is there a similar change that would effectively rename the login on the report server?

    I grant access via an Active Directory group - in the group the correct name is shown.


    • Edited by Ahren P Thursday, July 18, 2013 2:47 PM clarification on new items
    Thursday, July 18, 2013 2:47 PM
  • I believe I have found the issue (that or AD finally propagated down) - on the report server, I had granted my account a direct login during the setup and configuration process, however I did not remove the login after configuration was complete.  This kept the old name.  Removing the login appears to have fixed the issue - the old login name apparently was confusing the server.

    Marking Mike's response as answer, as it still would be useful in this situation and similar ones in order to immediately fix the problem of subscription failures.

    Thursday, July 18, 2013 2:53 PM