Email Account OAuth 2.0 refresh tokens after clone

[David House]

We are using OAuth 2.0 for our email accounts in ServiceNow, and while following this guide we have been successful in configuring several email accounts this way:
https://docs.servicenow.com/csh?topicname=t_SetUpOAuth2ForEmail.html&version=latest

The issue we face is that, since our environment utilises SSO, when we get to step 15 "Click Authorize Email Account Access to obtain the access and refresh tokens.", we have to launch an incognito window in order to prevent our own personal account from slipping through via SSO, and not allowing us to enter the credentials for the email account we're trying to authorise.

For the initial setup, this is an acceptable configuration step.

However, this is not just a once off action as, after every clone we perform, we are forced to re-authorised every email account, with the following message showing on all email accounts after the clone:

"OAuth Access or Refresh tokens are not available. Verify the OAuth configuration and click the 'Authorize Email Account Access' link below to request a new token."

The following process works, but since we have several email accounts and we have to follow these steps for each email account separately, you can imagine how tedious this becomes:

1. Launch incognito window

2. Login to ServiceNow instance with non-ACR account through side_door.do

3. Navigate to the email account

4. Click 'Authorize Email Account Access'

5. Login through SSO with the email account details

6. Email account is now authorised in ServiceNow

7. Close incognito window to end the SSO session

We have clone preservers and table exclusions in for the email accounts and OAuth tokens, but this doesn't appear to solve the issue.

Has anyone else had this issue and been able to find a suitable solution?

[Max4]

Did you get any updates on the PRB1714708? We are facing the same issue. 

[Ingimar]

@Max4 , PRB1714708 was updated today and there seem to be some internal (ServiceNow) discussions going on which I can only see a small part of and no real context.  So hopefully that is at least some good news that there is some activity going on within the PRB.

I hope they will resolve this since having to do this manual process every time after cloning is not optimal.

[LaurentChicoine]

I've recently configured OAuth for a customer and was surprised it broke after the clone.

Thanks to @Ingimar clue that the sys_email_account records are recreated following clone, that gave me a clue on how to fix it.

I assumed there was a reason for the recreation VS a simple preservation, so I didn't touch that part.

I debugged the SQL calls when running the script responsible to refresh the access token so I could find which tables could be missing the required data. I found it's the OAuth Requestor Profile [oauth_requestor_profile] table that contains that data. That table is automatically preserved OOB but it includes a field "requestor_id" that contains the sys_id of the email account. Since the email account is recreated, the email account gets a new sys_id following the clone therefore causing a broken reference.

In our use case we use a single email account for SMTP making the fix relatively simple (however with multiple email accounts using the same OAuth entity profile this could be more challenging to link back the proper email account). If I had to do it I would probably add a custom field to the OAuth Requestor Profile table to identify which profile belongs to which email account with the "User name" rather than the sys id, so I would be able to restore following a clone.

Here is the clone cleanup script I came up with for a single email account configuration per OAuth profile:

(function(){
    // OOB clone recreates the email accounts therefore breaking OAuth Requestor Profile preferences
    // This script fixes that but only for a single email account per OAuth profile
    var SCRIPT_NAME = '**Restore Email OAuth**';
    var oauthProfileEmailAccountMap = {};
 
    var oauthEmailAccount = new GlideRecord('sys_email_account');
    oauthEmailAccount.addActiveQuery();
    oauthEmailAccount.addQuery('authentication', 'oauth2');
    oauthEmailAccount.addNotNullQuery('oauth_profile');
    oauthEmailAccount.query();
 
    while(oauthEmailAccount.next()){
        var oauthProfileId = oauthEmailAccount.getValue('oauth_profile');
        if(typeof oauthProfileEmailAccountMap[oauthProfileId] == 'undefined'){
            oauthProfileEmailAccountMap[oauthProfileId] = [];
        }
 
        oauthProfileEmailAccountMap[oauthProfileId].push(oauthEmailAccount.getUniqueValue());
    }
 
    for(var oauthProfileId in oauthProfileEmailAccountMap){
        if(oauthProfileEmailAccountMap[oauthProfileId].length > 1){
            gs.warn(SCRIPT_NAME + ' Unable to restore OAuth Email Profile for accounts ' + oauthProfileEmailAccountMap[oauthProfileId].join(', ') + ' since there is more than one account sharing the same OAuth Profile');
            continue;
        }
 
        var emailAccountId = oauthProfileEmailAccountMap[oauthProfileId][0];
 
        var oauthRequestorProfile = new GlideRecord('oauth_requestor_profile');
        oauthRequestorProfile.addQuery('oauth_entity_profile', oauthProfileId);
        oauthRequestorProfile.query();
        if(oauthRequestorProfile.getRowCount() > 1){
            gs.warn(SCRIPT_NAME + ' Unable to restore OAuth Email Profile for account ' + emailAccountId + ' since there is more than one ÒAuth Requestor Profile sharing the same OAuth Profile');
            continue;
        }
 
        if(!oauthRequestorProfile.next()){
            gs.warn(SCRIPT_NAME + ' Unable to find OAuth Requestor Profile for OAuth Profile ' + oauthProfileId + ' that is linked to email account ' + emailAccountId);
        }
 
        // Update the requestor profile with the new email account ID
        oauthRequestorProfile.requestor_id = emailAccountId;
        oauthRequestorProfile.update();
    }
})();

I tested that script in background and it works but I haven't tested it yet in a real clone. I'm unsure if the email accounts are properly created at the moment that cleanup scripts are running. I will try to remember to update this post if ever the cleanup script does not work as expected on our next clone.

I hope this can be helpful to some.