Dynamics Community Forum Thread Details

Hello Kenzie

They need to create a Master Encryption key in the ManagementReporter database. They would use the attached script. The script has the instructions in it.

If you have any issues you can create a case in MS Dynamics SL support for further assistance!

--////////////////////////////////////////////////////////////////
--
-- Script Instructions:
--
-- 1. Update the line in the following code, starting with 'CREATE MASTER KEY ENCRYPTION BY PASSWORD', to contain the
-- Master key you wish to use. The master key must meet the Windows password policy
-- requirements of the computer that is running the instance of SQL Server.
--
-- 2. Run this script against the Management Reporter 2012 database. This script
-- will output a message if it does not detect the Management Reporter 2012 database.
--
--////////////////////////////////////////////////////////////////

IF EXISTS (SELECT Name FROM sys.tables WHERE Name = 'ControlReportSchedule')
BEGIN
IF EXISTS (SELECT TOP(1) name FROM sys.symmetric_keys WHERE name = 'GeneralUserSymmetricKey')
DROP SYMMETRIC KEY GeneralUserSymmetricKey

IF EXISTS (SELECT TOP(1) name FROM sys.certificates WHERE name = 'GeneralUserCertificate')
DROP CERTIFICATE GeneralUserCertificate

IF EXISTS (SELECT TOP(1) name FROM sys.symmetric_keys WHERE name = 'ConnectorServiceSymmetricKey')
DROP SYMMETRIC KEY ConnectorServiceSymmetricKey

IF EXISTS (SELECT TOP(1) name FROM sys.certificates WHERE name = 'ConnectorServiceCertificate')
DROP CERTIFICATE ConnectorServiceCertificate

IF EXISTS (SELECT TOP(1) name FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##')
DROP MASTER KEY

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Access!23'
-- NOTE Where Access!23 is your actual password

CREATE CERTIFICATE [ConnectorServiceCertificate]
AUTHORIZATION [dbo]
WITH SUBJECT = N'Certificate for symmetric key encryption - for use by the connector service.'

CREATE CERTIFICATE [GeneralUserCertificate]
AUTHORIZATION [dbo]
WITH SUBJECT = N'Certificate for access symmetric keys - for use by users assigned to the GeneralUser Role.'

CREATE SYMMETRIC KEY [ConnectorServiceSymmetricKey]
AUTHORIZATION [dbo]
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE [ConnectorServiceCertificate]

CREATE SYMMETRIC KEY [GeneralUserSymmetricKey]
AUTHORIZATION [dbo]
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE [GeneralUserCertificate]

IF NOT EXISTS (SELECT TOP(1) name 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]
GRANT CONTROL ON CERTIFICATE::[ConnectorServiceCertificate] TO [GeneralUser]
GRANT VIEW DEFINITION on SYMMETRIC KEY::[ConnectorServiceSymmetricKey] TO [GeneralUser]
UPDATE Connector.Adapter
SET Settings.modify('declare namespace x="">www.microsoft.com/.../Integration";
replace value of
(/SettingsCollection/x:ArrayOfSettingsValue/x:SettingsValue[x:Attributes="Password"]/x:Value/text())[1]
with ""')
UPDATE Connector.MapCategoryAdapterSettings
SET Settings.modify('declare namespace x="">www.microsoft.com/.../Integration";
replace value of
(/SettingsCollection/x:ArrayOfSettingsValue/x:SettingsValue[x:Attributes="Password"]/x:Value/text())[1]
with ""')
END
ELSE
BEGIN
PRINT 'WARNING: Incorrect database selected.'
Print 'Execute script against the Management Reporter 2012 database.'
PRINT 'This can be found in the Management Reporter 2012 Configuration Console.'
END

Community site session details

Error in MR: Please create a master key in the database or open the master key in the session before performing this operation

Helpful resources

News and Announcements

Quick Links

Responsible AI policies

Andrés Arias – Community Spotlight

Congratulations to the August Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates