We ran into an issue recently with multiple servers where a newly created user could not log into Dynamics GP on an RDP server and received the error user password not recognized by Dynamics GP. The Dynamics GP credentials worked fine on some Windows sessions, and not on others. Even when the password was blanked out in SQL using Management Studio, the user could set a password, but when attempting to log in, the user login was denied.
One of the things that we found was that the Dynamics GP ODBC connection was being used as an Excel workbook data source, as well as for Dynamics GP. This seems to be a fairly common practice from the partners that I talked with.
What we found was that when the user changed the default database for the ease of use of Excel, it appears to have changed the SQL connection string that was stored in the user’s profile. This created a situation where the Dynamics GP user password, which is encrypted before passing it to SQL, is not being encrypted properly, and therefore was not matching the SQL password that was being received.
We resolved this issue using the following method
- Delete the Dynamics GP ODBC connection
- Recreate the Dynamics GP ODBC connection using the recommended settings (since Dynamics GP is a 32 bit application, remember to use ODBCAD32.exe, not the 64 bit version)
- Create additional ODBC connections for the use of other applications, such as Excel, or use a direct SQL connection rather than the Dynamics GP data source.
While this behavior was seen on an RDP server, it could in theory happen to any user desktop. With the increased use of hosting and terminal server implementations of Dynamics GP, this issue will certainly appear more often.