There are many advantages to Dynamics CRM on-premise. One of them is the ability to use existing Active Directory accounts for single sign-on. Setting up a user in CRM is quick and simple. Enter the user’s domain name, email address, populate a few other fields and the new user is ready to go.
As with many automated processes, we generally are ignorant about what goes on behind the scenes until something goes wrong. Corrupted CRM user and Active Directory connections are one of these things that, in my opinion, becomes ridiculously complicated to deal with.
Potential Hazards of Deleting an Active Directory Account
There are several scenarios that can create this mess. The precipitating event is deleting an Active Directory account of a CRM user, whether the user is enabled or disabled. An IT department may choose to delete user accounts when a person leaves the company. In a perfect world, all instances of the user should be deleted. Active Directory user accounts can be deleted. Dynamics CRM users cannot. The appropriate CRM recommendation is disabling the user.
Real Hazards of a Returning User
If the employee never returned this would not be an issue. Companies in competitive vertical markets often swap employees, particularly sales representatives. In these cases, it is not uncommon for a salesperson to “return home.” The IT staff will need to create a new user account if the old one was deleted.
Another scenario I have come across is when an Active Directory account needs to be deleted and recreated and a CRM user was already added using the first account.
Understanding the Problem
Most of this I have pieced together through experience and reading a handful of blog posts. By no means do I completely understand why Microsoft chose to use the relationships I am about to describe, and some of what I have witnessed defies explanation. Regardless, I believe this will help you determine how to handle a few common scenarios.
Every Dynamics CRM on-premise installation is a collection of databases in a SQL Server instance. The MSCRM_CONFIG database precedes the creation of an organization. It contains two tables that control user authentication and association with organizations.
System User Authentication
This table has one and only record pair for each domain username.
- AuthInfo: SID or email address
- Id: identity GUID
- User Id: reference to the SystemUserOrganization table
Record Pair Example
|C:Bmarley@org.com||GUID A||GUID C|
|W:[Active Directory SID]||GUID B||GUID C|
Apparently, Microsoft elected to create two rows to store the email address and Active Directory SID in one column as opposed to adding an extra column and using one row. The Id column is unique to each row whereas the User Id is unique to each record pair.
System User Organizations
This table has one record for each organization the user is associated with.
- CrmUserId: reference to the SystemUserId in CRM
- Id: identity GUID
- OrganizationId: Organization GUID
- UserId: reference to the SystemUserAuthentication table
With this background, we can now look at a few scenarios.
Active CRM user, associated with a deleted Active Directory account. A new Active Directory account with the same domain name is created.
The user receives an error attempting to log in to CRM.
Authentication relies on two pieces of information obtained from the Active Directory account – the SID and the Object GUID. The former is stored in the AuthInfo column in MSCRM_CONFIG System User Authentication table (see the above diagram) and the latter is stored in the ActiveDirectoryGuid field in the organization’s System User table.
Both values refer to the old Active Directory account which of course no longer exists. The result is failed authentication resulting in an error.
These values must be associated with the new Active Directory account. Create a dummy Active Directory account or use an account that is not associated with CRM. Edit the user record, changing the domain name to the other user. Click
This will now create records in MSCRM_CONFIG for the new user and update both values.
Edit the user record again, changing the name back to the original user. Click
Save. As before, the values will be updated with the current Active Directory settings.
The user should now be able to access CRM.
Disabled CRM user, associated with a deleted Active Directory account. A new Active Directory account with the same domain name is created.
The user receives an error attempting to log in to CRM. You attempt to follow the steps for the last example, but CRM will not allow you to activate the account.
The situation is identical to the first example with an extra twist. When you attempt to enable the account CRM attempts to authenticate the user. Since the ActiveDirectoryGuid points to a non-existent account, this operation fails.
At this point, things get a bit dicey. The only solutions I find involve directly modifying the CRM tables, something that is unsupported and under any other circumstances absolutely forbidden.
The first step is to get the Object Id for this Active Directory account from IT.
Next, use a SQL update statement to replace the value of the ActiveDirectoryGuid column for the appropriate row. Since this is likely a live production instance, you really don’t have the luxury of getting a snapshot of the organization that can be restored. Therefore, take all precautions to verify that you are selecting only one row – and the correct row – to update before you execute the statement!
Once this is completed, you should be able to activate the user account and follow the steps in the first scenario.
Disable CRM user, associated with a deleted Active Directory account AND a new active CRM user associated with a new Active Directory account that uses the SAME domain name as the previously disabled CRM user.
The user receives an error attempting to log in to CRM.
The new CRM user has the valid ActiveDirectoryGuid. The original CRM user has the old value. This is a case where I simply do not know what CRM is doing behind the scenes – I only know the outcome. The bottom line is that the SID stored in AuthInfo does not get updated if a user with the same domain name as the newly created user already exists. The incorrect SID causes authentication to fail.
There are likely multiple ways to fix this, and based upon my limited experience my approach may not be the simplest one.
In hindsight, I would like to propose an alternate approach to the one I used. Since there are two users, and only one is required, consider disabling the new user and then following the steps in scenario two to update the ActiveDirectoryGuid for the original disabled CRM user. That user record can then be activated and the user swap should fix the problem.
In the event this does not work, you can employ the more complex solution I implemented.
Step 1: Verify that the ActiveDirectoryGuid for the new user is correct. If not, update it.
Step 2: Find all records in the System User Organizations table where the CRM User Id is equal to the old user’s SystemUserId in the System User table. Replace the old SystemUserId with the one associated with the new user.
Step 3: Employ the user swap technique on the new CRM user record. This should force an update of the System User Authentication table.
I did an extra step of manually updating the SID in the System User Authentication table. Based upon subsequent experiences, I do not believe this is required as CRM seems to update it automatically.
- Case Studies
- Customer Updates
- Dynamics 365
- Dynamics Portals
- Executive Order
- Microsoft Dynamics
- Microsoft SQL Server
- Microsoft Teams
- Open Source
- Press Release
- Project Management
- Software Development
- Unified Service Desk
- What We've Done
- Women in Technology