Management Reporter Upgrade Fails The Users Can’t Connect

I recently did a Microsoft Dynamics GP upgrade for a client which included Management Reporter. During the upgrade of Management Reporter to the latest hotfix, we found that the upgrade of the legacy connector failed every time. We spent quite a bit of time looking into the problem and eventually got it to install correctly.

When legacy connector was first added, it was added using the FQDN of the SQL Server Instance (SQL01.example.com\GP), the upgrade failed every time we tried to use the FQDN, but when we switched to just the machine name (SQL01\GP), the upgrade was successful.

Management Reporter has been used by this client since the release of the 2012 version and it has been upgraded previously without issue, so I am unsure of what had changed in this version.

That was not the end of the story though. When I tested Management Reporter was working fine, it was. However, I’d logged in using the sa account. When users started logging in they were finding that they were unable to connect to the GP company database:

Unable to connect to the 'Example Limited' company.
The connection to the Microsoft Dynamics GP database failed. Contact your system administrator.

I’d seen this error message before and it is because the System DSN used by Dynamics GP was using the FQDN, but now the legacy connector in Management Reporter i using the server name so the password encryption will be different.

I didn’t want to remove the legacy connector and try to install it again as I was pretty sure we’d have the same problem as previously. Instead I decided to have a go at updating the connection used by Management reporter which is stored in the Reporting.ControlCompany table and which I noted I’d done in the previous bog post.

However, I didn’t post the SQL I’d used, so I needed to recreate it. The two highlighted sections are the server name which needed to be changed and to what:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
UPDATE
	Reporting.ControlCompany
SET
	GLEntityConnectionInformation = REPLACE(CAST(GLEntityConnectionInformation AS VARCHAR(8000)), 'SQL01\GP', 'SQL01.example.com\GP')
GO

Obviously, take a backup of the database before running the script and test that it has worked correctly before allowing users back into Management Reporter.