You’re offline. This is a read only version of the page.
4,150
one of our subsidiary companies has its own GP installation. Different sql server, different GP. On that instance of GP I have a user id. On both my instance of GP and the subsidiaries instance of GP I have the exact same user id and exact same password.
I know that there is encryption at the sql level making the dynamics dbase not very portable. I have however always had no issue in backing up and restoring company databases across servers providing the sql matches and the account format matches.
Today we tried our first back up restore from the subsidiary to the parent GP and I could not get into the subsidiary company. IT had to work with the SID. Citing the encryption that exists at the sql level.
Given that they did not touch the dynamics / master dbase, they only copied (essentially via back up restore) the subsidiary company database, does it make sense that I could not get in? this is GP2010, current SP.
thanks
ian
*This post is locked for comments
I think to put this into perspective, the SID is a lot like hash algorithms based on the current machine's configuration. The SID is recycled though on the disposal of a SQL instance, and a new one is generated. Like Jonathan mentioned, the SID was reassigned when the new instance was generated, and this causes the encryption algorithm to be modified because it uses the SQL SID to help interpret the encryption / decryption methods.
Also, since GP user security is based on SQL security, not only database security, any changes to the SQL Security ID will affect all the users on the SQL Security level regardless if the database was modified.
Hi Jonathan
I can totally understand from the perspective of taking an existing Dynamics from one server to another the need to reset the password. The dynamics dbase gets copied in that case.
In this case IT only had to reset my SID. Not my password. We took our subsidiary company only, not their dynamics dbase from their server / separate GP instance and back up restored them onto our server / separate GP instance. There was no copying of the dynamics dbase at all.
you are correct that the sql server name will be different. Likely the ODBC as well.
Can you expand on what a SID is?
Hi Ian,
The password encryption is actually set by the information you put in the Server section in the ODBC connection. So if the SQL instance have a different name the passwords would be different. So when you are going between two servers or doing a server move you will always have to reset the users password.
It is a SQL issue rather than a GP one. It is possible (more likely certain) that more recent versions of SQL have had security tightened up which is why it may have become an issue.
The server I encountered it on was running SQL 2008.
thanks Ian
this must be something relatively new. When I was a reseller V 5 to V 8.5 I could take my clients dbases and log into them without issue.
The SID was exactly the issue as you describe that IT fixed up.
Hi Ian,
I've had problems when migrating databases between SQL Servers.
The user id on the database being moved may look the same as the one on the destination SQL Server when you view it via Management Studio, but it is in fact a different username and can cause access problems.
The issue I had I blogged about recently when I identified the solution: www.azurecurve.co.uk/.../error-granting-database-access-in-sql-server-management-studio
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (