A clone can corrupt the discovery_credentials table on the target instance, leaving orphan/ghost records with a class that no longer exists, preventing MID Server using all credentials - Known Error

A clone can corrupt the discovery_credentials table on the target instance, leaving orphan/ghost records with a class that no longer exists, preventing MID Server using all credentials Description A clone can corrupt the discovery_credentials table on the target instance, leaving orphan/ghost records in the table, who's class is for child tables that no longer exist after the clone . This prevents the MID Server from fetching the Credentials from the instance, and so all probes and features using those credentials are broken.

The clone automation deliberately adds all discovery_credentials sub-classes to the exclude and preserver list, based on the tables that are in the source instance. This is necessary to prevent credentials being corrupted in all clones, because out-of-box, only excludes and preservers are included for top level discovery_credentials but only some of the child tables. (Fixed PRB1305469 and PRB1403259 for excludes, PRB1391898 for preservers, specifically for discovery_credentials).

However those fixes do not take into account that target instances may have a different set of plugins installed (or different customisations) which means there may be additional child tables only on the target, and those may have records in them. An upgrade of the target instance, which adds more credential types, and has the tables populated with records, would also cause this issue, if being cloned over from a source instance on a previous version. These are common scenario where an sub-production instance has been used for upgrade testing, or evaluation of new features. It is those record's Sys IDs that remain in the top level discovery_credentials table. The sys_class_name of those records are now tables that no longer exist in the target instance.

The symptoms in features such as Discovery don't obviously point to corruption of the table, causing long support cases. As these records are corrupt, they cannot be deleted by the customer using gliderecord scripts or lists/forms. A custom sys_auto_flush table cleaner job will delete the records, but this is risky and can cause accidental data loss.

Symptoms: Where a credential does exist, and has been proven to work on the target, and yet probes fail, could be caused by this. Even though valid credentials exist in the instance, due to the table corruption the mid server is not able to cache those credentials in the MID Server and can't use them.

An issue reported as failing after an upgrade, on a sub-production instance, could be due to this because a Clone is usually done just before an upgrade test.

The MID Server agent log will confirm that it was not able to fetch Credentials from the instance with this error in the Agent log (agent\logs\agent0.log.0). It is actually "Unable to retrieve data from instance" because the table is corrupted by the bad records. Everything else is then a side-effect of this. Worker-Interactive:CommandPipeline-xxx SEVERE *** ERROR *** An error occurred while decrypting credentials from instance com.snc.automation_common.integration.exceptions.AutomationIOException: Unable to retrieve data from instance. This MID may not be validated. at com.glide.util.MIDServerInfoPayloadDecrypter.decryptPayload(MIDServerInfoPayloadDecrypter.java:25) at com.service_now.mid.creds.provider.standard.StandardCredentialsProvider.loadCredentials(StandardCredentialsProvider.java:315) at com.service_now.mid.creds.provider.standard.StandardCredentialsProvider.load(StandardCredentialsProvider.java:285) at com.service_now.mid.creds.provider.standard.StandardCredentialsProvider.init(StandardCredentialsProvider.java:304) at com.service_now.mid.creds.provider.MIDCredentialsConfigProvider.getCredentialsProvider(MIDCredentialsConfigProvider.java:57) at com.service_now.mid.pipeline.command.TestCredentialCommandImpl. (TestCredentialCommandImpl.java:68) Discovery is likely to fail probes with the error: No credential found for type [xxx] Event Management Connectors may fail to start, with errors similar to: Connection test failed: Credentials not found, id: xxx Method failed: (/SolarWinds/InformationService/v3/Json/Query) with code: 401 - Invalid username/password combo Orchestration activities will fail with authentication errors. This could be tested from the Activity Designer e.g. the Response raw output will include something like: The remote server returned an error: (401)

The true cause needs confirming from the MID Server agent log e.g. Worker-Expedited:PowershellProbe- SEVERE *** ERROR *** An error occurred while decrypting credentials from instance

..and similar authentication/credential errors for any feature that runs jobs via a MID Server and uses Credentials from the instance table for the connection to the endpoint/target.

Steps to confirm you have this problem: Open a list of credentials on the clone target instance, and personalize the list layout to add the "Class" column, and then "Group By" Class: /discovery_credentials_list.do?sysparm_query=GROUPBYsys_class_name

Any records where the Class value is "label" are records in the top level discovery_credentials table, that have a actual sys_class_name value that no longer exists

Attempt to open those records in a form, and the URL will tell you what class the record is. e.g. /openstack_credentials.do?sys_id=..., where "openstack_credentials" is the class.

If you get a "Page not found" error, it indicates the table doesn't exist in the instance any more. That can be confirmed by looking for the table in the "Tables" table (/sys_object_source_list.do). You would expect to to find the table.

If you get "Record Not Found" then it may still be this problem, but only if the table was added back to the instance by an instance upgrade, or a Store App upgrade or install, done after the clone.

Steps to Reproduce

Provision 2 new instances Install Discovery on the Target instance. Add a record in one of the child tables that Discovery plugin adds, populating any required field - it doesn't have to be real credential data e.g. openstack_credentials (which is from the CPG dependency). From the navigation, open Discovery - Credentials (/discovery_credentials_list.do), click New, and in the Interceptor select OpenStack Cerdentials (/openstack_credentials.do) Or additionally install "Discovery and Service Mapping Patterns" and use one of the ones regularly added by that out-of-band app e.g. gcp_credentials Without changing any Exclude or Preserver settings on the source instance, Clone source instance over target instance. Expected behaviour: Any extending tables not existing after the clone on the target, should have any records in them deleted as part of the clone.

Actual behaviour: The table becomes generally corrupted, breaking features that use it, by the presence of ghost/orphan records. The MID Server is unable to fetch any credentials, so cannot use them.

Workaround This problem is currently under review. You can contact ServiceNow Technical Support or subscribe to this Known Error article by clicking the Subscribe button at the top right of this article to be notified when more information becomes available.

Once you have confirmed you have got corrupt records in the discovery_credentials table, you will need to delete the bad records to repair the table. A customer admin user can repair the records without need of a Support Case or SQL level change.

This javascript will delete the remaining parts of the corrupt records caused by this problem.

Run it from the Scripts - Background page /sys.script.do, from the global scope . https://docs.servicenow.com/bundle/rome-application-development/page/script/server-scripting/concept/c_ScriptsBackground.html

// Script to delete orphan records for non-existent tables after a clone (PRB1542851) var cgr = GlideRecord('discovery_credentials'); cgr.query(); while (cgr.next()) { var table = new TableUtils(cgr.sys_class_name); if (!table.tableExists()) { cgr.sys_class_name='discovery_credentials'; cgr.update(); // changes to the top level class, which does exist... gs.log('Deleting corrupt credential record, sys_id:' + cgr.getUniqueValue(),'PRB1542851'); cgr.deleteRecord(); // ...allowing this to now be cascade deleted } } To prevent this in future clones, you could implement this same script as a Clone Cleanup Script, that will run automatically immediately after a clone. https://docs.servicenow.com/bundle/rome-platform-administration/page/administer/managing-data/concept/post-clone-cleanup-scripts.html

Notes:

This script limits itself to deletes for credential tables that no longer exist, where the sys_id in the top level table was preserved. Ghost/Orphan records may remain, caused by other problems, and so this other method may also be required in some instances: KB0716609 Detect and remove orphan records using JavaScript The corrupted records cannot be recovered, as data in fields defined for the child tables are lost already. If you do need them again, they will need re-entering in the table. These records are corrupted in a way that means they cannot be deleted from Forms/Lists or Gliderecord scripts while the class is bad. Re-cloning will not resolve this, as the corrupt records will still be there and preserved by the required discovery_credentials top level table preserver. Adding the tables to Clone Exclude and Preserver settings will not avoid this, as the table will still not be there after the clone. Adding the tables back by upgrading the instance or store Apps will also not solve this, as the records will still be missing the sys_id from the child table. The deletes should trigger the system command to tell all MID Servers to reload the credentials, after which all features should work again. If they don't, a MID Server restart should be done, and then check the agent logs for any more, or different, errors. Related Problem: PRB1542851