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:
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">
To correct the problem it was simply a case of updating SQL1 to GPLIVE.