How to keep Active Directory and SQL users from breaking after a database restore?

Hi Guys,

Recently I am facing a crazy problem. I am trying to copy production CRM MSSQL data to another isolated server stack 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:

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 a 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. 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, 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 an 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.

Thanks & Regards
Camillelola