I previously wrote about a situation where the Dynamics GP GPConnNet library is unable to connect to a SQL Server instance if a port number must be specified.
This week I encountered a new limitation with GPConnNet and VS Tools. A customer has been successfully using a Dynamics GP AddIn for several years, and they are now upgrading to GP 2015. When they tried to test my AddIn on their GP 2015 test server, they received this error.
The error message says GP login failed. But since this is a VS Tools AddIn that runs inside of GP after a user has logged in, that message doesn't make much sense. We know that the username and password are correct. Very odd.
We then noticed that the server name was incomplete. The final "T" in the name was missing, and the value displayed is exactly 15 characters--more than a coincidence. So it looks like the 16 character server name is being truncated to 15 characters, and that is likely the cause of the problem.
But wait! If the server name is being truncated, then the server name would be incorrect. And when the AddIn attempted to connect to that non-existent server to authenticate, the connection attempt would fail, right? The error message would be different for a connection failure.
So back to the original error message. It says "GP login failed", not "failed to connect" or something similar. So this would seem to tell us that the connection was successful, but that the login subsequently failed.
What in the world?
But it gets better.
If the customer logs in to Dynamics GP using the 'sa' login, the AddIn works and does not give the "GP login failed" message.
So the sa account works, but GP users don't work. What does that tell us? In theory, it is a confirmation that the AddIn connection process is working, but that there is something about the GP logins that is failing.
So why would sa work, but not a GP user login?
My guess is Dynamics GP password encryption.
When you create a new user in Dynamics GP, it "encrypts" the password before sending it to SQL Server. This prevents a user from connecting directly to the SQL Server.
My guess is that GPConnNet uses the SQL Server name in the "encryption" process, but it is truncating the server name at 15 characters for some reason, and that is the cause of this issue. Presumably Dynamics GP does not do this, since my client is able to login to GP just fine.
So how do you work around this issue?
The best option is to make sure that your SQL Server instance names are no more than 15 characters.
The only other option I was able to come up with was to have the client create a shorter SQL Server Alias. I then had to hard-code that shorter alias name in my AddIn. Once I hard coded the shorter alias for the server name, the AddIn worked fine.
Why hard code, you ask?
Well, VS Tools uses the Dynamics GP Backup / Restore form in order to get the name of the SQL Server. Even if the Dynamics GP ODBC DSN is set to use a short alias name, the Backup / Restore window will return the actual SQL Server name. So even after the Alias was setup and the GP ODBC DSN was using it, my AddIn was still receiving a SQL Server name of MCCGP15DB01-TEST, and the login would still fail. Fortunately, they only have this issue with their Test database server--their GP 2015 production SQL Server has a shorter name.
So, like I said, just make sure your SQL Server instance names are 15 characters or less if you are using GPConnNet.
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.
No comments:
Post a Comment