Check System Integrity failure for SSL certificate

I discovered an issue when trying to delete an unused environment. I needed to first delete an environment binding for a certificate, but this wasn’t allowed because of an unexpected value.

Digging a little deeper, i was able to find the integrity checks that were failing for the same reasons.

The RelatedDocumentId 'certificates' for Event Id 'Events-17215' is not in the expected format.
The RelatedDocumentId 'certificates' for Event Id 'Events-17216' is not in the expected format.
The RelatedDocumentId 'certificates' for Event Id 'Events-17218' is not in the expected format. 

Digging even deeper into the SQL database itself. I can see that Event.RelatedDocumentIds has a reference to a bunch of IDs, including one called certificates. I also looked in the EventRelatedDocument and see that there is an entry in there for “certificates” in the RelatedDocumentId column.

I had a look on the following Github issue, but the proposed SQL query fix doesn’t appear to be appropriate for my issue.

We’re currently running version v2020.6 (Build 4915).

Is there anything we can do to resolve this? As it’s stopping us tidying up some old applications, certificates and environments.

Hi @amadiere,

Thanks for reaching out.

Can we please take a look at those events with a SQL query?

SELECT * FROM dbo.Event WHERE Id = 'Events-17215' or Id = 'Events-17216' or Id = 'Events-17218'

Then save the results as a CSV and send them to me in a private message?

Please let me know if thats something you’re allowed to do. I can also give you a link to directly upload them to our support files site.

Best,
Jeremy

Thanks for the reply. I’ve messaged you privately with the .csv as requested. But in the interest of openness for anyone else that comes into this issue, this is a redacted version.

Note: JSONBlob and ChangeDetails are encrypted fields and both have values and I chose not to include them on this screenshot. If they turn out useful, I can decrypt and redact parts of them.

Hey Alex,

Thanks for sending that through.

In column 2 you can see where there are Environment IDs as well as Certificate IDs. For some reason, the certificates don’t have IDs like they should. I’m not sure how they got in this state, but I think that is the root of the issue. I believe we have two paths we could go down.

The better option would be you could find the correct certificate ID that should be associated with those events and inject and remediate that column for those event IDs.

Alternatively, if you can’t find which certificate it should be, you could delete “certificates” from the column for those rows.

In either case, I would take a database backup before making any changes and confirm everything works after the change. We only like to directly manipulate the SQL database when it’s absolutely necessary as it can have unintended consequences. This change should likely not cause any issues but I would test it just in case after the change you decide to make.

Please let me know how it goes and if you have any questions.

Best,
Jeremy