Microsoft Dynamics CRM (Archived)

User Email as AuthInfo entry in SystemUserAuthentication table in MSCRM_CONFIG

(0) Share

Report

Posted on by ratiusv

105

Hello,

I am looking for a while for an answer for an interresting question.

While using Dynamics CRM without Claims and IFD, we only have the possibility to connect users that can login with "Domain\username"

Using Dynamics CRM with Claims and IFD, gives CRM the possibility to connect to users over their email addresses.

I am using Dynamics CRM 2016 and want to use SingleSign On (SSO) managed by PingFederate which needs an email address to authenticate the user.

Now, the problem I am facing is that, as I read on the few pages on the Internet I have found, there is or should be an entry in the MSCRM_CONFIG database, SystemUserAuthentication table, AuthInfo column that is defined like: C:email .

How and where to find the records to determine the SID of the users is out of the scope for me now, as this is another topic.

I was wondering how is it possible to add an entry of type "C:email" in the SystemUserAuthentication table, without changing the database directly with update statements?

How, where and what should I do in CRM to realize that? Default I have only SID entries in the SystemUserAuthentication table.

And, after doing that, could anyone help me in explaining me what changes should be made on CRM in order to get the user authenticated througt PingFederate?

Thank you!

*This post is locked for comments

All responses (3)

Answers (0)

David Jennaway 14,063 on at

Like (0)

Report
RE: User Email as AuthInfo entry in SystemUserAuthentication table in MSCRM_CONFIG

Just to add to the previous post, here's a more direct way to get the UserId that is used in [MSCRM_CONFIG].[dbo].[SystemUserAuthentication]:

SELECT suo.UserId FROM [MSCRM_CONFIG].[dbo].[SystemUserOrganizations] suo JOIN [XX_MSCRM].[dbo].[SystemUserBase] sub ON suo.crmuserid = sub.systemuserid WHERE sub.fullname = <name>

Where XX_MSCRM should be replaced by the name of an organisation database, and <name> should be replaced by the fullname of the user in that organisation for which you want the UserId
ratiusv 105 on at

Like (0)

Report
RE: User Email as AuthInfo entry in SystemUserAuthentication table in MSCRM_CONFIG

Please be aware that every change you make with PING will have impact on ALL organizations on the server!!!!!

Set a user with the email address as Domain user (for example “email@address.com”)

In the “MSCRM_CONFIG” database in the “SystemUserAuthentication” table, all available information in the “AuthInfo” column contained only the SID of the users provided from the local AD. It wasn’t possible to set an email address as username (without claims) therefore no surprises here.

What we actually did was to replace within SQL the “AuthInfo” of one user by his email address, which is used as UPN. This actually allowed the user to log on successfully without having the “primarysid” attribute in the claims.

Now with the possibility to log on to CRM, which is connected to PING, it is also possible to create users by using their email addresses instead of using domain\username. These new users can connect directly without us having to modify the SQL database.

We got to the point where we amended the entries in the database after we hardcoded an admin user’s SID into the claim of the ping. This user then was able to connect to the CRM. At this point, after logging in, the CRM created a second entry in the “SystemUserAuthentication” with the email address as AuthInfo of the user. This would’ve sufficed for the admin user to connect afterwards without a SID within the claim and to create then users by their email-addresses. We just took the shortcut by directly changing the “AuthInfo” from the SID to the user’s email-addresses.

In the end, there was no PING problem.

  Now, we send the eMail Address in the “upn” claim.

  PrimarySID is NOT in the token and does not need to be.

Then, in CRM, when switched to Federation Services Mode, you can create users using their eMail address as the Username.

Then there is no AD lookup, and email is used as the key and the SID is not necessary.

Steps to do in the database:

1.       Make sure that you are a system administrator in CRM and you are the last one that logged into the CRM system. We need that because we are going to sort the[MSCRM_CONFIG].[dbo].[SystemUserOrganizations] table on [LastAccessTime]:

SELECT top 20 *  FROM [MSCRM_CONFIG].[dbo].[SystemUserOrganizations] order by LastAccessTime desc

2.       The first record should be the record with last login data. For our example we will take the one from line 6.

3.       The gray marked data (CrmUserId) represents the SystemUser guid of your system user used to log in. Now you have to copy the yellow marked “UserId”.

4.       Now we have to Update some data manually in the [MSCRM_CONFIG].[dbo].[SystemUserAuthentication] table

SELECT TOP 10 [AuthInfo],[Id],[UserId],[IsDeleted]

FROM [MSCRM_CONFIG].[dbo].[SystemUserAuthentication]

WHERE UserId =‘0F6B6552-1177-E511-8584-B499BABC6B9

5. get a result table that contains at least one record with this UserId:

The value on the first column represents the SID of the users provided from the local AD

6. we have to change the SID with the email address, or just add a new record with the email address:

Format of AuthInfo: “C:email@address.com”

7. Now you only have to change the domain registration ID in ALL the CRM Organisations that are running on this server.

Clear the Internet Explorer cache and close the IE.

Start IE again and connect to the organization.
Community Member Microsoft Employee on at

Like (0)

Report

RE: User Email as AuthInfo entry in SystemUserAuthentication table in MSCRM_CONFIG

Did you ever find a solution? We are running into the exact same problem.