March 15, 2013 at 4:41 am
Morning all,
I recently carried out an SSRS Migration from one domain to another.
I used the backup and restore method, and all reports are generating fine and all access for existing users appears to be fine.
However, there is one (fairly significant) problem;
Some users appear to have been duplicated (seems that a problem with new SIDs being generated / mapped for each domain user affected, and this has resulted in me being unable to edit the security on the site levels/folders where this is a problem.
I can neither add, amend or delete users and was advised to delete the first instance of a user to correct this, but it seems this has to be ruled out, due to the fact that even the top level folder contains duplicate users, and an error occurs when I attempt to delete thr user via the site security.
The original error was:
"Response is not well-Formed XML"
The error when I try to delete a user now in one of the affected folders is;
"role assignment is not valid. The role assignment is either empty or it specifies a user or group name that is already used in an existing role assignment for the current item. (rsInvalidPolicyDefinition)"
I tried amending the ownership and modifiedby entries in the database for one of the specific users to myself, and then deleted the user and policies assigned to him, but it unfortunately never resolved the problem (The user still remains in place with security entries on the site, even if deleted within the database).
Has anyone else ever encountered this problem and is there a way of resolving this which doesn't mean having to resort to backing up the original db with the affected users stripped out, in order for a restore to be carried out.
A lot of further configuration work and report imports has occurred since this was put in place.
Thanks in advance for any responses.
March 15, 2013 at 5:57 am
Any suggestions at all for either removing the user references altogether (outwith the web front end, which I am encountering the failure on), or for resolving the duplicate issue?
If anyone needs more info, I can easily beef this out with more detail if need be.
Thanks 🙂
May 20, 2013 at 7:28 am
I have had the exact same problem after migrating domains, I have put together a script that has worked for us and resolved the issues. ****Obviously make a backup before performing any changes as nothing is guaranteed************
-----------------------------------------------------------------------------------------------
create table #ConversionTable
(SecDataIDvarchar(40)
,XmlDescription XML)
-------------------------------Global Query ------------------------------------------------
---To update all permisison on folders and datasources across the entire SSRS site
insert into #ConversionTable
select SecData.SecDataID,SecData.XmlDescription
FROM SecData
----------------------------------------------------------------------------------------------*/
--Makes the amendment in the temp table by deleting all Policy nodes that contain the OLD DOMAIN
update #ConversionTable
SET XmlDescription.modify('delete //Policies/Policy[contains(.,"OLDDOMAIN")]')
----------------------------------------------------------------------------------------------*/
--Make sure you are happy with the changes made to #ConversionTable before commiting to live in the next step.
select CT.XmlDescription, cast(SD.XmlDescription as xml)
from #ConversionTable CT
Join SecData SD on CT.SecDataID = SD.SecDataID
-------------------------------------------------------------------------------------------------*/
--Updates the ******LIVE******** XmlDescription field in the SecData table based on the new data fields in the temp table
update SD
set XmlDescription = cast(CT.XmlDescription as nvarchar(max))
--select CT.XmlDescription , cast(SD.XmlDescription as xml)
from #ConversionTable CT
Join SecData SD on CT.SecDataID = SD.SecDataID
----------------------------------------------------------------------------------------------*/
--Once you are happy you can drop the temp table
Drop table #ConversionTable
--and remove the user accounts from the user table
delete from users
where UserName like '%OLDDOMAIN%'
May 20, 2013 at 7:43 am
Thanks for the script, I'll keep a hold of it for future reference! 🙂
I eventually managed to resolve the issue, I think by selecting the second instance of EVERY duplicate user simultaneously and performing a delete of all.
I recall having to perform the deletions in bulk, which was a bit frustrating for some folders where thousands of users had been individually added, instead of group access being applied.
All up and running fine now anyway, but thanks for the reply.
August 14, 2013 at 12:21 pm
SQL query that may be ran to identify if you will encounter duplicate user accounts as a result of changing domains.
1. On your SRSS DB server, run:
SELECT * FROM [ReportServer].[dbo].[Users]
WHERE UserName NOT LIKE '%NEWDOMAINB\%'
{Replace 'NEWDOMAINB' with your new domain NetBIOS name.}
Save the results of this query.
2. Run the following query on your SRSS DB server:
SELECT * FROM [ReportServer].[dbo].[Users]
WHERE UserName NOT LIKE 'Everyone'
AND UserName NOT LIKE 'BUILTIN\Administrators'
AND UserName NOT LIKE 'NT AUTHORITY\SYSTEM'
Compare the single (non-DOMAIN prefix'd usernames) to the NEWDOMAIN usernames. If you see any duplicate usernames here, these will also need to be removed, as there could be an issue.
There are two methods to remove the old usernames:
1. While the SRSS Report Web server is a member of the old domain, log into the SRSS Reporting web server and delete the old user accounts.
or
2. Run query on the DB directly to remove the old usernames.
-----------------------------------------------------------------------------------------------
create table #ConversionTable
(SecDataID varchar(40)
,XmlDescription XML)
-------------------------------Global Query ------------------------------------------------
---To update all permisison on folders and datasources across the entire SSRS site
insert into #ConversionTable
select SecData.SecDataID,SecData.XmlDescription
FROM SecData
----------------------------------------------------------------------------------------------*/
--Makes the amendment in the temp table by deleting all Policy nodes that contain the OLD DOMAIN
update #ConversionTable
SET XmlDescription.modify('delete //Policies/Policy[contains(.,"OLDDOMAINA")]')
----------------------------------------------------------------------------------------------*/
--Make sure you are happy with the changes made to #ConversionTable before commiting to live in the next step.
select CT.XmlDescription, cast(SD.XmlDescription as xml)
from #ConversionTable CT
Join SecData SD on CT.SecDataID = SD.SecDataID
-------------------------------------------------------------------------------------------------*/
--Updates the ******LIVE******** XmlDescription field in the SecData table based on the new data fields in the temp table
update SD
set XmlDescription = cast(CT.XmlDescription as nvarchar(max))
--select CT.XmlDescription , cast(SD.XmlDescription as xml)
from #ConversionTable CT
Join SecData SD on CT.SecDataID = SD.SecDataID
----------------------------------------------------------------------------------------------*/
--Once you are happy you can drop the temp table
Drop table #ConversionTable
March 10, 2014 at 2:35 pm
I've been working through trying to get through this issue for a few days now and this has helped me tremendously!
April 8, 2015 at 9:02 am
I also used the script to clean up the duplicates in the folder perms. Thank you.
The last script step... "delete from Users" did fail with the following error...The DELETE statement conflicted with the REFERENCE constraint "FK_PolicyUserRole_User". The conflict occurred in database "ReportServer", table "dbo.PolicyUserRole", column 'UserID'.
I could see the old domain users in the Users table but not in the Users folder under the Security folder. Do you know how I might delete these? Thanks again.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply