I'm trying to copy production CRM MSSQL data to another isolated server stack (server 2) via a backup-and-restore process. Server 2 already has most of the data after a restore I did from the production data backup a few months ago. The initial restore worked fine because the active directory wasn't set up yet on server 2. A brand new CRM installation took care of most of the AD and SQL login creation.

When the restore is completed, I get the following error when I try to login into the CRM: enter image description here

There seems to be a problem with connecting the SQL users/logins with the AD ones. I've checked the IDs of the users and groups that appear in both SSMS and AD, and they do seem to correspond. I've also made sure that the SQL users corresponded to the SQL logins, using "alter user with login" queries. The interesting thing is that sometimes after restoring the DB, the CRM works fine, but then after a few minutes it starts giving the error again. More often, though, the error appears right away after the restore. I've also tried to append "alter login with name" queries after "alter user with login" queries, to ensure a 2 way correspondence, however, this didn't seem to help.

The restore would only change the SQL DB users (along with the CRM data). The SQL logins are the ones actually connected to the AD, and those don't change. This makes me wonder why an AD related error would pop up here at all. Maybe it has something to do with the way CRM links its users (CRM users, not SQL users), to AD, since the CRM uses ADFS SSO. That info would have been stored in the CRM and overwritten by the restore. Since the server 2 AD is not the same as on server 1, the matching may break.

If I restore the copy of the database that server 2 initially had, the CRM works fine again, the data is just outdated.

Do you have any other suggestions on how to keep the SQL users and logins in sync with AD and prevent the error from happening? This may very well be an issue with some other part of the infrastructure, so I'm open to suggestions.


Can you restore an uptodate Active Directory to a third server as a destination ? (It can be a virtual machine on that Server2)

You can have the SQL data uptodate but it'S important to have a uptodate Active Directory backuped, especially if your SQL use AD as authentication scheme.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.