Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / MR 2012 CU 16 cannot s...
Microsoft Dynamics GP (Archived)

MR 2012 CU 16 cannot set default company with DataMart!

(0) ShareShare
ReportReport
Posted on by Beat Bucher GP Gee... 28,021 Super User 2024 Season 1

Dear MR 2012 Fans.. 

I ran into a strange issue.. I customer was in need to restore their MR 2012 database following a server crash.. However, they didn't recall on which CU they were prior to the crash, but I figured it out from the configuration table, as it was showing 2.12.15000 (CU15).

After installing CU15 and setup of the MR services, I tried to setup the DataMart connector, since that's what they had prior too. With only 3 GP companies restores, out of 7, MR was reluctant to configure and it wouldn't even let me connect to the DYNAMICS database to check the GP companies.. it would even identify the configured companies before the DM connector was installed..  As I never was able to connect to the DYNAMICS DB (even with 'sa'), I gave up and installed CU16, which prompted to upgrade the MR database. After that was done, I was finally able to connect to DYNAMICS to pull the GP companies, at which point I was also able to setup the DM connector and re-create the ManagementReporterDM DB. 

But when it came to fire up the MR Designer application and connect to the MR database, I was prompted the first time to select a default company, as it was expected, but MR denied that user access to any of the DM companies.. I had also installed the Legacy connector to make sure it wasn't a network, problem and was able to select a default 'Legacy' company (-Curr) by using the 'sa' SQL user. That connection would be denied if trying to connect with a regular GP user, which seems to point to a password encryption issue, but I checked all the ODBC connections and they were all identical. Tried from the SQL server directly as well as from a network computer and the outcome is the same.. 

The Windows Event log returns information pointing to the master encryption key, which I already had reset by using the KB2744330, but nothing changed.

At this point the only thing to consider would be to restart from scratch by exporting all the definitions and building a new MR database, but that customer has tons of reports generated and doesn't want to lose them all, so that would really be only on last resort.

Thanks for any useful hints on that issue.

ScreenCap-2019_2D00_06_2D00_17_5F00_134443.png

ScreenCap-2019_2D00_06_2D00_17_5F00_134530.png

ScreenCap-2019_2D00_06_2D00_17_5F00_134538.png

PS: the last capture is when trying to set the default company in MR Designer with a windows user and AD authentication. 

*This post is locked for comments

  • Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,021 Super User 2024 Season 1 on at
    RE: MR 2012 CU 16 cannot set default company with DataMart!

    Hi Annette,

    Thank you very much for those scripts.. They could prove to be useful indeed, as I'm suspecting some conflicts with the previously setup MR instance.. and tried to remove the connection records from the Company table, but it run into FK_ issues (some tables are linked to that one and refuses to remove records).

    Your scripts might be able to get around this.

    I'll let you know how that goes.

    Beat

  • Suggested answer
    Annette_Brown Profile Picture
    Annette_Brown on at
    RE: MR 2012 CU 16 cannot set default company with DataMart!

    HI Beat,

    Have you tried to remove the integration and start from starch?  A while back, can't remember exactly which CU when you tried to remove the integration it left orphaned records in the ManagmentReporter DB.  A Microsoft tech gave me some scripts to manually remove the records and start fresh.  You could run the scripts below to completely remove all integrations.  Maybe there is some residual connections causing havoc?

    Steps:

    disable integration and remove.

    then run the scripts one at time.  when complete do file>>configure and readd the data mart.  

    --1
    Alter table [Connector].[Integration] DISABLE trigger ALL
    Alter table [Connector].[Map] DISABLE trigger ALL
    Alter table [Scheduling].[TaskCategory] DISABLE trigger ALL
    Alter table [Scheduling].[Task] DISABLE trigger ALL
    Alter table [Scheduling].[Trigger] DISABLE trigger ALL
    --2
    DELETE FROM [Connector].[IntegrationGroup]
    DELETE FROM [Connector].[Map] WHERE [MapId] IN (SELECT [Id] FROM [Scheduling].[Task] WHERE [CategoryId] IN (SELECT [Id] FROM [Scheduling].[TaskCategory] WHERE [ParentId] IN (SELECT [Id] FROM [Scheduling].[TaskCategory] where ParentID is null)));
    DELETE FROM [Connector].[MapDefinition] WHERE [DefinitionId] NOT IN (SELECT DISTINCT DefinitionId FROM Connector.Map)

    --3
    DELETE FROM [Scheduling].[Task] WHERE [CategoryId] not IN (SELECT [Id] FROM [Scheduling].[TaskCategory] where ParentID is null)
    DELETE FROM [Scheduling].[Trigger] where Id NOT IN (SELECT distinct TriggerId from [Scheduling].[Task])
    DELETE FROM [Connector].[MapCategoryAdapterSettings]
    --4
    Alter table [Connector].[Integration] ENABLE trigger ALL
    Alter table [Connector].[Map] ENABLE trigger ALL
    Alter table [Scheduling].[TaskCategory] ENABLE trigger ALL
    Alter table [Scheduling].[Task] ENABLE trigger ALL
    Alter table [Scheduling].[Trigger] ENABLE trigger ALL
    --5
    DELETE FROM [Connector].[Integration]
    --6
    DELETE FROM [Scheduling].[TaskCategory]
    -- Review Reporting.ControlIntegrationSource for correct Server 'Name'
  • Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,021 Super User 2024 Season 1 on at
    RE: MR 2012 CU 16 cannot set default company with DataMart!

    Hi Richard,

    Thanks for the moral support :-).. I've already thought about that, but the thing is that the customer didn't change their domain.. Everything was restored as before. And to rule this out, we even went into MR security, deleted an admin user, re-added it and logged back in with that user, thinking it might fix it, but alas, it didn't.

    I'm actually balancing between an network authentication issue and the data encryption problem, though even I applied the script from the related KB, nobody recalls what the original setup was, as it was done back in 2016 by another partner that is no longer available.

    I hope someone from Microsoft could jump in here (thinking of  , Ryan Sandness or Greg Byer).. Let's see what comes out.

    Have a nice week,

    B@

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: MR 2012 CU 16 cannot set default company with DataMart!

    Beat, hopefully something here will help. I remember having a similar issue when all I had was the MR database. I had to manually change security using SSMS.

    support.microsoft.com/.../after-you-make-changes-to-your-domain-you-no-longer-have-permission-to

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans