Share
Report
95My 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 (
Answers (
