A client recently upgraded to Dynamics GP 2016. As part of their GP upgrade, the client setup new SQL Servers in a dual-server high availability (HA) cluster. The ODBC DSN is setup to use a DNS name that points to the SQL Server cluster. The ODBC DSN looks something like this:
The name "sqlcluster.company.lan" is not a physical machine. It's a DNS entry that points to an IP address for the SQL cluster. The SQL cluster decides which of two SQL instances / machines should receive the requests.
Using these DSN settings, you can setup GP just fine. When you create users based on this DSN and fully qualified server name, GP works great and users can login.
But this configuration can cause problems with applications or utilities that are not designed to work with the SQL Server HA setup or DNS aliases. One such incompatible utility is the GPConnNet.dll library.
GPConnNet is used by .NET developers who create Dynamics GP AddIns that require a connection to the GP SQL Server. It allows the developer to request a connection to SQL without knowing the server, username or password. You simply ask GPConnNet to provide you with an active connection to the SQL Server.
Well, in an environment using SQL Server HA, GPConnNet is unable to connect to the SQL Server instance. Rather than looking at the ODBC DSN server setting for the GP client, GPConnNet uses some other technique to determine the SQL Server instance name. As a result, it gets the physical SQL instance name, which in a SQL HA environment, is different than the fully qualified cluster name.
This dialog shows that GPConnNet returned an error number of 131074, which indicates that the connection was successful, but that the login failed. You can see that the Data Source value ends in "DB01", which is the name of one of the physical SQL instances, and not the cluster DNS name.
As a result, when GPConnNet tries to connect to the physical SQL instance, the login fails. Because the Data Source name is used, the user password is encrypted differently, and an incorrect password is then sent to the SQL Server.
GPConnNet could likely be modified to fix this issue, but I'm pretty sure that isn't going to happen.
There might be a way to configure SQL Server HA to work around this issue with GPConnNet, but I'm skeptical about that, and even if there was, it would require this customer to completely re-implement their HA setup and GP configuration.
I offered the customer the option of customizing my software to use a configuration file to store SQL credentials to work around this issue. But the client informed me that they have encountered several other issues with the HA setup, and as a result of this additional issue, they are abandoning SQL HA for their GP environment. They will be switching over to a standard single SQL Server machine and will be eliminating the use of a DSN entry to point to the SQL cluster.
So while SQL HA for Dynamics GP might be a sensible goal, be aware that other software may not work properly in such an environment.
Steve Endow is a Microsoft MVP
for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.
He is the owner of Precipio Services, which provides Dynamics GP
integrations, customizations, and automation solutions.
Ever thought of trying to ILSpy the dll and overriding/replacing just the connection code (and whatever is in the chain before it) with new classes? I've had to do something similar before. You'd then call the rest of the stack as normal.
ReplyDeleteI don't have GP anymore since I haven't done any development for it since the beginning of the year, but if I had that assembly, I'd start looking there to see if I could patch my own fix around it.
Hi Paul,
ReplyDeleteThanks for the note. I believe I have previously tried to decompile it, but if I recall correctly, it was written in C++, so the .NET decompiler did not work. I'm guessing they did that because it contains the "encryption" method for the GP passwords.
Steve
Hi Steve,
ReplyDeleteSorry to comment on an old post but we are looking at using SQL Server HA and we've got quite a lot of VS Tools customisations. Do you know if this limitation has been fixed in newer versions of GP?
Phillip