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

(0) Share

Report

Posted on by Jeff Bloch

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

All responses (2)

Answers (0)

Suggested answer

John Lowther 5,122 on at

Like (0)

Report

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 Gee... 28,021 Super User 2024 Season 1 on at

Like (0)

Report

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.