Views:

Symptoms
After making changes to your domain or moving the server to a different domain, users are not able to access Management Reporter.

Cause
Windows SID has changed for the user and is no longer valid.

Resolution
Update Windows SID for a user that is in the Administrator role in Management Reporter.

1. Sign in to the SQL server as one of the users that has the Administrator role in Management Reporter.
2. Select Start > Run and enter CMD.

a. At the Dos command type:
Whoami /user

b. In the DOS window, right-click, select Select All, and then press Enter.

c. Paste the information into Notepad.

3. Open SQL Server Management Studio and run the following SQL statement, against the Management Reporter database.

a. Management Reporter 2012 CU13 and later. In SQL Management Studio, run the following against the ManagementReporter database:
SELECT A.UserName,
B.Name,
CASE A.ROLETYPE
WHEN 2 THEN 'VIEWER'
WHEN 3 THEN 'GENERATOR'
WHEN 4 THEN 'DESIGNER'
WHEN 5 THEN 'ADMINISTRATOR'
END AS SecurityRole,
A.WindowsSecurityIdentifier,
A.UserID,
CASE A. AccountDisabled
When 0 Then 'Enabled'
When 1 Then 'Disabled'
End AS AccountStatus
FROM Reporting.SecurityUser A
JOIN Reporting.SecurityPrincipal B
ON A.USERID = B.ID
ORDER BY A.UserName


b. Management Reporter 2012 CU12 or earlier. In SQL Management Studio, run the following against the ManagementReporter database:
SELECT A.UserName,
B.Name,
CASE A.ROLETYPE
WHEN 2 THEN 'VIEWER'
WHEN 3 THEN 'GENERATOR'
WHEN 4 THEN 'DESIGNER'
WHEN 5 THEN 'ADMINISTRATOR'
END AS SecurityRole,
A.WindowsSecurityIdentifier,
A.UserID,
CASE A. AccountDisabled
When 0 Then 'Enabled'
When 1 Then 'Disabled'
End AS AccountStatus
FROM SECURITYUSER A
JOIN SECURITYPRINCIPAL B
ON A.USERID = B.ID
ORDER BY A.UserName


4. Note the UserID.

5. Make a backup of your Management Reporter database and then run the following SQL statement. You must modify the statement to include the new user's Windows SID and their existing UserID.

a. Management Reporter CU13 or later
UPDATE Reporting.SecurityUser
SET WindowsSecurityIdentifier = '<copy/paste new Windows SID>'
WHERE UserID = '<paste UserId from step4>'

b. Management Reporter CU12 or earlier
UPDATE SecurityUser
SET WindowsSecurityIdentifier = '<copy/paste new Windows SID>'
WHERE UserID = '<paste UserId from step4>'


6. Run the following SQL statement, modifying the statement to include the new user's domain\alias.

a. Management Reporter 2012 CU13 or later.
UPDATE Reporting.SecurityPrincipal
SET Name = '<enter new domain\alias>'
WHERE ID = '<paste UserId from step4>'
Management Reporter 2012 CU12 or earlier

b. UPDATE SecurityPrincipal
SET Name = '<enter new domain\alias>'
WHERE ID = '<paste UserId from step4>'


7. The user should now be able to log into Management Reporter. To update the other users, select Security. Select Users and then remove the users that are unable to access Management Reporter. You can then add the users back with their new domain name.