Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics GP (Archived)

How to restore live db to test on different instance of SQL Server

Posted on by 95

My client has two instances of SQL insalled.  One is a live instance and one is a test instance.  The live instance houses all the Production Databases and the test instances houses all of the Test Databases.

We have two installs of GP using different DSNs so they can access either the live dbs or the test dbs.

The problem we are running into is restoring a live db to the test instance.  Whenever we do this, we are forced to remove the users from the db via SQL and then go into GP and give the users access to the db and then assign Uer security to each user.

Microsoft says there is no way around this because they changed their security algorithim.  What they said is:

For v8.00 it was possible to use SQL Enterprise Manager (SQL 2000) or SQL Management Studio (SQL 2005) to change the user's password and so have an un-encrypted password which can be used with other applications.  For v9.00 and v10.00 this is no longer possible as the application will request that the password be changed so that it is stored using encryption.  For v10.00, the encryption algorithm was strengthened as part of Microsoft's Trustworthy Computing initiative.

 

The algorithm includes the User ID (case sensitive) and the Server name from the ODBC DSN (not case sensitive) in the encryption key.  What this means is that if the Server name is changed or if you swap from using a Machine Name to an IP address, the old password will no longer work.  It also means that once a password has been created for a specific User ID, the case used for that User ID must remain the same.  For example: a password created for JoeBloggs will not work if the User ID is entered as joebloggs.  The reason is that if the encryption key is different, then the password sent to the SQL Server will not match regardless of what is entered by the user. So if you swap to a different workstation, make sure that the ODBC DSN Server Name is the same and that the User ID has been entered the same case.

Does anyone have any ideas on how to do a restore where you do not have to go through all these hoops?

*This post is locked for comments

  • Suggested answer
    John Lowther Profile Picture
    John Lowther 5,122 on at
    Re: How to restore live db to test on different instance of SQL Server

    Hey Jeff,

    I just saw your question and noticed that is was not marked as answered.

    So, I assume that you still have this problem.


    As I understand you already have your users on the test system with everything working until you refresh the test system with a new copy of the production databases.

    Please see this post to change a dex.ini setting on your test system and the necessary SQL code to change all users passwords on the test machine and send the new password to them via individual email.

    let me know how it goes,

  • Beat Bucher  GP Geek  GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,021 Super User 2024 Season 1 on at
    Re: How to restore live db to test on different instance of SQL Server

    Hi Jeff,

    Unfortunately there is no workaround, and SQL will always restore your 'PROD' company's user definitions. Your only chances are to write some handy SQL script that would read off the Dynamics Security tables the users that are part of the access to the TEST company and remove all other users remaining in the TEST DB security.

    Be sure to run also the SQL script from the KB871973. There is another tread in the community about this : https://community.dynamics.com/product/gp/f/32/p/23423/37756.aspx#37756

    Have a nice day.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans