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.

[David House]

I've been tinkering with the points brought up by @Ingimar and the idea proposed by @LaurentChicoine , and I've settled on a process that seems to work for our team for now (at least until ServiceNow comes up with an OOB solution to this).

My main requirements were:

1. Minimise the amount of manual interaction time
2. Avoid customisations
   
   

With that in mind, I've put together 2 Fix Scripts - one to run pre-clone on the target instance, and another to run post-clone on the target instance. It's not the most elegant solution, but this has successfully turned a tedious 30+ minute manual process into a less than 5 minute job in total (plus up to 30-60 minutes background waiting time).

See the fix scripts below, but this is the process:

1. Run Pre-Clone script on target instance (non-production)
2. Save the output array string somewhere safe (password manager, or other tool)
3. Clone over target instance
4. Navigate to Post-Clone script on target instance
5. Paste in the array string as the content of the variable "jsonStringInput"
   NOTE: It is important for this to remain a single line to work in this format, and the type of quote matters (single quotes), the output of the pre-clone script is in this format already, but if you've added formatting, I like to use VSCode's command "Join Lines"
6. Save the change
7. Run Post-Clone script on target instance
8. Cleanup - remove array string, and revert back to:
   var jsonStringInput = 'PASTE_JSON_STRING_HERE';
9. Wait up to 30-60 minutes for OAuth token to automatically refresh
10. Test connection on each email account to confirm success

Pre-Clone Fix Script:

/*
  * Name: PRE-CLONE: Get Email OAuth (JSON)
  * Description:
* ** RUN THIS IN TARGET NON-PROD INSTANCE BEFORE A CLONE **

** COPY THE OUTPUT JSON STRING AND SAVE FOR USE IN POST-CLONE FIX SCRIPT **
--------------------------------------------------
Gathers JSON output mapping OAuth Requestor Profiles to Email Accounts

This needs to be run BEFORE a Clone and the output JSON needs to be stored somewhere safe for use AFTER the Clone in the following fix script:
"POST-CLONE: Fix Email OAuth (JSON)"

This is a workaround to the issue of tediously re-authenticating every email account after a clone, but requires this script being run prior to the clone in order for the 2nd script to work correctly post-clone.
*/

var oauthReqProf = new GlideRecord("oauth_requestor_profile");
oauthReqProf.query();

oauthReqProfMap = [];

while (oauthReqProf.next()) {
	var emailAccount = new GlideRecord("sys_email_account");
	if (emailAccount.get(oauthReqProf.requestor_id)) {
		oauthReqProfMap.push({
			"sysID" : oauthReqProf.sys_id.toString(),
			"emailAccountName" : emailAccount.name.toString(),
			"emailAccountUserName" : emailAccount.user_name.toString(),
			"emailAccountSysID" : emailAccount.sys_id.toString()
		});
	}
}

gs.info(JSON.stringify(oauthReqProfMap));

 Post-Clone Fix Script:

/*
  * Name: POST-CLONE: Fix Email OAuth (JSON)
  * Description:
** RUN THIS IN TARGET NON-PROD INSTANCE AFTER A CLONE **

** USE THE JSON STRING SAVED FROM THE PRE-CLONE FIX SCRIPT **
--------------------------------------------------
Gathers JSON output mapping OAuth Requestor Profiles to Email Accounts

This needs to be run AFTER a Clone assuming the fix script "PRE-CLONE: Get Email OAuth (JSON)" was run BEFORE the Clone.
Paste the JSON string gathered from that pre-clone script into the variable jsonStringInput before running this script.

The format of the variable jsonStringInput should look something like this:
var jsonStringInput = '[{"sysID":"SYS_ID_HERE","emailAccountName":"MAILBOX_NAME_HERE","emailAccountUserName":"EMAIL_ADDRESS_HERE","emailAccountSysID":"SYS_ID_HERE"}]';

Once this script has been used, be sure to reset the variable definition line to the following:
var jsonStringInput = 'PASTE_JSON_STRING_HERE';

This is a workaround to the issue of tediously re-authenticating every email account after a clone, but requires the 1st script being run prior to the clone in order for the this script to work correctly post-clone.
*/

var jsonStringInput = 'PASTE_JSON_STRING_HERE';

var oauthReqProfMap = JSON.parse(jsonStringInput);

for (var i = 0; i < oauthReqProfMap.length; i++) {
	var item = oauthReqProfMap[i];

	var emailAccount = new GlideRecord("sys_email_account");
	emailAccount.addQuery("user_name", item.emailAccountUserName);
	emailAccount.addQuery("name", item.emailAccountName);
	emailAccount.query();

	if (emailAccount.next()) {
		gs.info('Found Email Account: ' + item.emailAccountName);
		var oauthReqProf = new GlideRecord("oauth_requestor_profile");
		oauthReqProf.addQuery("requestor_id", item.emailAccountSysID);
		oauthReqProf.query();

		while (oauthReqProf.next()) {
			oauthReqProf.requestor_id = emailAccount.sys_id.toString();
			oauthReqProf.update();
			gs.info(item.emailAccountName + " - Updated Requestor ID");
		}
	}
}

Hopefully this helps others as it has helped our team.