Start SQL Server Management Studio and sign in as the sa user.
Run the script that is shown here on the ManagementReporter database:
drop symmetric key generalusersymmetrickey
drop certificate generalusercertificate
drop master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Access!23'
-- NOTE Where Access!23 is your actual password
CREATE CERTIFICATE [GeneralUserCertificate]
WITH SUBJECT = 'Certificate for access symmetric keys - for use by users assigned to the GeneralUser Role.'
CREATE SYMMETRIC KEY [GeneralUserSymmetricKey]
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE [GeneralUserCertificate]
if not exists (select 1 from sys.database_principals where name='GeneralUser')
begin
CREATE ROLE [GeneralUser]
AUTHORIZATION [dbo];
end
GRANT CONTROL ON CERTIFICATE::[GeneralUserCertificate] TO [GeneralUser]
GRANT VIEW DEFINITION on SYMMETRIC KEY::[GeneralUserSymmetricKey] TO [GeneralUser]
ALTER DATABASE [ManagementReporter] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
Grant the GeneralUser SQL role, on the restored ManagementReporter database, to the user running your MR services.
Restart the MRProcessService and stop and then start the Management Reporter Application Pool in IIS.
Additional restore options
If you know the Master Key that was used during the installation of MR, you can follow the steps shown here instead of dropping all certificates and keys on the MR database.
Reset the Master Key password by running the following SQL statements on the ManagementReporter database:
OPEN MASTER KEY DECRYPTION BY PASSWORD ='<password>'
Where <password> is equal to the Master Key That You used when this ManagementReporter database was created. Then run:
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = '<password>';
CLOSE MASTER KEY
Where <password> is equal to a new Master Key password of your choice.
Note There is a “FORCE” option available on this statement if you do not know the Master Key password that is discussed more in the More Information section.
Enable Service Broker by running the following SQL statement:
ALTER DATABASE [ManagementReporter] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
Restart the MRProcessService and the Management Reporter Application Pool.
