Director of Software Development & Integration for Sta-home Health & Hospice, Inc.Chairman of the Board of Advisors for the Dynamics GP User Group (GPUG)
10+ years with Dynamics-GP (GL, AP, HR, Payroll, Maintenance, Training, Upgrades, Security) 15+ years as a Database Administrator (Maintenance, Performance, Warehousing, Validation, Correctness, Alerts, BI & Reporting)20+ years as a Developer (Written software for IBM, BCBS, Schools, Nursing Homes, & Home Health)
You can follow my blog here or my other networks below:
Any views contained within are my personal views and not necessarily Microsoft policy or the policy of Sta-home Health & Hospice, Inc.All posts are provided "AS IS" with no warranties, and confers no rights.
Blogs Outside of the Dynamics CommunityIn Recovery by Paul RandalSQL Skills by Kimberly Tripp
Post SeriesCreating an EDW for CBI SeriesGetting Data from Point A to Point B
Moving Dynamics-GP Databases:
When I was moving my databases in a previous post, I had a problem with the user passwords. David has done a wonderful job of explaining exactly what was happening internally. Specifically the part about Dynamics-GP 10 using the server name from the ODBC DSN in the encryption algorithm in the section labeled “The v10.00 encryption algorithm”.
Using the information there I came up with this addition to my move database steps. So, here are the modified steps to moving your databases to another server.
· Removed the ODBC connection on the terminal server where we run Dynamics. (Easy way to keep users out.)
· Backup all the Dynamics-GP databases on the old sql server. (That’s Dynamics and all company databases)
· Restore all Dynamics-GP databases on the new sql server.
· Don't forget to change the compatibility level to SQL Server 2008 (100). (If you are moving from 2005 to 2008)
· Transferred the logins from the old sql server to the new sql server. (Used the script from Microsoft kb918992 with these changes)
o In the script that is created, add the following line after each CREATE USER statement
§ ALTER LOGIN <UserName> WITH PASSWORD=N’<Somepassword>’
o NOTE: I STRONGLY SUGGEST DIFFERENT PASSWORDS FOR EACH USER
o Execute that script according to the documentation in kb918992
· Edit Dex.ini file to set SQLLoginCompatibilityMode=TRUE
o Note: This was also in David’s Post. And don’t forget to check your Account Lockout Threshold.
· Recreated the ODBC connection on the terminal server where we run Dynamics for the new sql server.
· Now all the users are on the new sql server with a new password that will work.
· Send an email to each user telling them their new password.
And you are done!
Now, you may ask exactly what will your users see when they login with their new passwords that you sent them. Well, here it that information as well:
1. First, they start Dynamics-GP and enter their new password on the standard Dynamics-GP Login screen.
2. As soon as they hit the “OK” button they get the message “You must change your password before accessing Microsoft Dynamics GP. Do you want to change your password now?”
3. If they select “No” then they are sent back to the login screen and not allowed into the system. Which is the exact behavior you would expect given the message; but we all know, there are some users that for whatever reason just does not pay attention to the message. (Also, I must admit there have been times when I was guilty of not paying attention to message as well so I guess I cannot complain.)
4. Now, if they select “Yes” then they see the Change Login screen. Where they enter their old password (the one you sent them) and then a new password twice.
5. Upon clicking the “OK” button on the Change Login screen the user is taken back to the Dynamics-GP Login Screen where they can login to Dynamics-GP with their new password.
I would like to point out a couple of things you could or should do:
· At some point I would suggest that you either remove the “SQLLoginCompatibilityMode=TRUE” line from the DEX.INI file or set it to “FALSE”. Just a suggestion.
· If you have a lot of users and you know T-SQL; you could edit the first script from kb918992 to automatically add the “ALTER LOGIN <UserName> WITH PASSWORD=N’<Somepassword>’”
· And if you really want to get fancy you could change the first script so that it adds the lines to the second script to email each user their passwords automatically when you run the second script.
And as always if I have left something out, messed something up, or just because you want to, please feel free to comment.
Pay particular attention to the first paragraph abut the ramiifications of changing the Server name in the ODBC DSN, (wish I had earlier today).
This means if you change the name you are referencing the SQL Server with, even though it resolves back to the same server, that ODBC definition will not work with their current GP password.
Other Microsoft Sites
I'm a Customer
I'm a Partner
Follow Microsoft Dynamics