A client was recently having problems with the Microsoft Dynamics GP Datamart in Management Reporter 2012, so I removed the DDM and installed the Legacy GP Connector. I tested it on the server and all worked okay so I asked the user to try.
Unfortunately, it didn’t just work and they got the below error:
Management Reporter – Unable to connect to the ‘{company}’. The connection to the Microsoft Dynamics GP database failed. Contact your system administrator.I checked the logs in Management Reporter 2012 Configuration Console to see if there was any additional information:
There was additional information, but all of it pointed at the credentials being wrong which I knew couldn’t be the cause of the problem as the user could log into GP without problem. I took a look into the Windows Event Viewer to see if there was any more information, but it just gave the same error the user had seen:
I did a little more investigation and realised the problem was down to how I had installed the Legacy GP Connector. When I had installed it I had used the machine name (for sake of argument let’s say I used SQL1\GP as the machine name) but this client is configured to use a DNS Alias of GPLIVE to connect to GP; this means the users passwords have been encrypted with the DNS Alias and so won’t work using the machine name.
The easy option would be to remove and re-add the Legacy GP Connector using the DNS Alias, but I didn’t want to do this. Basically, because I want a better answer for next time this happens. With Management Reporter storing its data in a SQL Server database it means that it can be examined and updated where necessary.
In the case of an incorrect SQL Server name, there is an entry in the ControlCompany table in the GLEntityConnectionInformation column. The contents of this column is stored as XML. The SQL Server node contains the DNS Alias or machine name:
<EntitySetting Name="SQL Server">
<Value xsi:type="xsd:string">SQL1\GP</Value>
</EntitySetting>
To correct the problem it was simply a case of updating SQL1 to GPLIVE.
What should we write about next?
If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.
Thanks. We were live in GP2013 and started a test instance to prepare to move to 2018. I configured Management Reporter and we successfully tested all functions and were good to go live in 2018. After the move we could access Management Reporter, the connection test passed, all the security was good and nothing has changed. However, got the database access error trying to access a live company. I went through pretty much the same process you laid out, and then searched. Your post directed me to the “reporting.controlcompany” table and sure enough, there was a reference to the old GP2013 server in the XML string. Update the row with the new server and connected with no problem. We had not caught the error because the 2013 instance was live and the data was fresh enough in the 2018 test environment where the finance people did not notice. Updating the row with the right server name- great post, thank you.