I had a client yesterday that took a backup of their GP9 system and was trying to create a test system.
They installed SQL 2008 and restored DYNAMICS and a company database to the new server. For simplicity, I will refer to the old SQL instance as SQL1 and the new sql instance as SQL2.
They already had the GP9 client installed on their local machine (pointing to SQL1), but there was no GP9 client on the server, since it was Win Server 2008 64bit and the GP9 client was not compatible.
They followed the steps in KB:878449
They ran the Capture_logins.sql script on SQL1 and then ran the generated script on SQL2.
They ran sp_changdbowner 'DYNSA'
They ran dex_req.sql and Grant.sql
When they tried to set up an ODBC connection to SQL2 on their client, they could not connect as sa.
We did the following to correct this:
1. Reset the sa password on SQL2
2. Started the SQL Browser service on SQL2
3. Started the DTS service on SQL2
4. Set the firewall on SQL2 to allow DTS
5. Rebooted SQL2
Now we could connect with the ODBC connection, but we got errors when logging into GP as sa.
We re-ran the scripts on SQL2 and I noticed that Grant.sql was throwing an error that it could not find a view. The view was a custom object in the database with a name exceeding 64 chars. Once we shortened the name, Grant.sql ran successfully.
Now we could log into GP as sa.
The point of all this was to migrate the users from SQL1 to SQL2 along with their encrypted passwords. But we could not log in as any user other than sa.
The answer to this is found in a Note on Step 11 of KB:878449
"If the old server was running Microsoft Dynamics GP and does not have the same name as the new server, the passwords for the users will no longer be valid."
We that sucks with a capital "S". The whole point here was to be able to migrate the users with their encrypted passwords. So why? It seems the answer is that the encryption algorithm includes the server name. Since there is no way GP is handing out the encryption algorithm to the likes of us, once again, security trumps common sense, but I digress.
So we are relegated to logging into GP as sa, opening security, pulling up the users one at a time, and changing their password to something simple. We then check the box to force the user to change the password on next login and save.
Now when we try to log in as the user, GP prompts us to change the password. But when we try to we get the following error:
"The password change failed for an unknown reason. Enter a different password or contact your system administrator."
Google pops up lots of hits that address the Group Policies, like this one.
Basically, it looks like most of the time this error pops up because the Minimum password age is set to some amount other than zero. Most admins have enough headaches getting their users to change their passwords before they expires. Why in the world would anyone want to restrict them from changing it too often? But I digress again.
In our case, this is not the issue. Once again, we will be saved by a post from Dave Musgrave. The issue is the ODBC connection itself.
When they set up the ODBC connection on then client, they chose SQL Server for the driver. However, the user password change function will only work using the SQL Native Client driver.
(When in doubt, always try the newest driver first)
So we changed the ODBC to use SNAC and ....Presto! The user password change now works.
Hope this saves someone else some reading.
Did this help you? If so, please leave a comment!
