Dave Ireland's CRM & Stuff

Enabling a CRM User after their AD account has been deleted and re-created

Posted on March 23rd, 2010.

That’s the longest blog title I’ve ever written, so the blog post itself will be succinct.

This happens all the time.  An employee leaves the company, so their CRM user profile gets disabled and then one of the guys in IT deletes their AD account.  A few months later, the ex-employee decides that grass was not greener elsewhere, and they’re back.  So, IT creates a new AD account with the same login id, and the CRM Administrator tries to re-enable their CRM user profile, but gets an error for their trouble.  So the CRM Administrator, no stranger to CRM error messages, tries to create a new user profile only to be told that a user profile for that user already exists.

The reason for this is that the CRM User Profile is linked to an Active Directory account through a piece of data that’s normally not exposed to the user – the ActiveDirectoryGuid field.

So each CRM user has this ActiveDirectoryGuid that corresponds to the AD ObjectGuid.  The problem starts when the user account is deleted from AD – the linkage is broken between the AD user and the CRM user.  When IT sets up the new AD account, the user gets a brand new unique ObjectGuid which does not match what’s in CRM.

So your job is to:

a) find out what the new AD ObjectGuid is for the newly created AD account.

b) update the CRM user record so that it now references this new Id.

Usual caveats here: Microsoft does not support or encourage direct updates of the CRM databases.  Please ensure you have a good database backup before doing anything in this article.

So, let’s get on with it.

a) Find the new ObjectGUID

The rotten thing about ObjectGUID is that you can’t see it anywhere using any of the AD tools like DSA or ADSIEDIT (or at least not in the format we need it in).  There are some complicated vbscripts that you can fiddle with to get it, but the easiest way IMO is to use the SQL AD provider and just query the value.  After all, AD is really just a big database of data/value pairs.

Open SQL Management Studio and log in as a user with the sysadmin role (yes, you need it).

The first thing to do is create a new linked server that will basically act as our connection to the AD database.  To create your linked server, execute the following script:

sp_addlinkedserver ‘ADSI’, ‘Active Directory Service Interfaces’, ‘ADSDSOObject’, ‘adsdatasource’

After you execute this, you should receive a ‘Command completed successfully’ message from SQL.

Now you can query the AD database for new ObjectGuid of the user.  In the script below, replace the following items:

Replace	With
sandbox	your domain name
local	your domain component
wilson	part of the full name of the user you are looking for

select displayName, cast(objectGUID as uniqueidentifier) as NewADObjectGuid from OpenQuery(ADSI, ‘SELECT objectGUID, displayName FROM ”LDAP://DC=sandbox,DC=local” where objectClass = ”User”’)
where displayname like ‘%wilson%’

Executing this query should produce a result like this:

That hex string is the new AD ObjectGuid, and is what we want to update the CRM user profile with.  Copy the string into Notepad or somewhere handy.

b) Update the CRM SystemUserbase record

If you issue the following query against your Organization_MSCRM database (again, replace ‘wilson’ with part of your user’s fullname), you should get a result of one row:

select SystemUserId, FullName, DomainName, ActiveDirectoryGuid
from systemuserbase where FullName like ‘%wilson%’

In this result set, you can see the DomainName (sandbox\dennis) and the ‘old’ ActiveDirectoryGuid.

Armed with this information, you are now ready to update the CRM database.  In the script below, replace the following items:

Replace	With
040D657F-8A60-42A2-A734-1FA8BE9DD64E	your new AD Object Guid from step (a)
Sandbox\dennis	your domainName from the query ran just a moment ago

Update SystemUserBase Set ActiveDirectoryGuid=’040D657F-8A60-42A2-A734-1FA8BE9DD64E’
where DomainName=’Sandbox\dennis’

When you execute this, it will update 1 row in the database, and you have now re-established the link between CRM and AD for this user.

You still have a few things left to take care of:

i) Remove the linked server you created back in step 1.  Do to this, execute this SQL:

sp_dropserver ‘ADSI’

ii) Recycle the CRMAppPool application pool on the CRM server

iii) Recycle the CRM Asynchronous Service on the CRM server.

Now you should be able to enable this user in CRM.

Make a Comment

Make A Comment: ( 1 so far )

One Response to “Enabling a CRM User after their AD account has been deleted and re-created”

Comments RSS Feed

Where's The Comment Form?