(Mass) deletion of entries in the certification authority database (certificates, requirements, revocation lists)

Sometimes it happens that the database of the certification authority becomes extremely large. Perhaps a large number of certificate requests have arrived unnoticed and have been rejected, or perhaps there are many certificates in the database that have been issued twice. Before the certification authority database compacts can be used, these entries must first be deleted in order to free up the storage space in the database.

Do you know TameMyCerts? TameMyCerts is an add-on for the Microsoft certification authority (Active Directory Certificate Services). It extends the function of the certification authority and enables the Application of regulationsto realize the secure automation of certificate issuance. TameMyCerts is unique in the Microsoft ecosystem, has already proven itself in countless companies around the world and is available under a free license. It can downloaded via GitHub and can be used free of charge. Professional maintenance is also offered.

A deletion of entries is possible with the -deleterow switch of the certutil program possible.

certutil [Options] -deleteRow ROWID | Date [Request | CERT | Ext | Attrib | CRL

Below are some examples of using certutil with the -deleterow switch.

Before deleting entries, it is recommended to check the Putting the Certification Authority into warning modeso that no new certificates can be requested, and to create an up-to-date backup of the certificate authority database.

Delete individual lines

Individual rows within the certification authority database can be deleted - identified by the row number (Row ID) with the following command:

certutil -deleterow {RowId}

Deleting failed requests

Deletion of failed requests is possible with the following syntax:

certutil -deleterow {date} Request

In the case of requests, the date describes the time when the request was sent to the Certification Authority.

The date must be specified in the local format (region setting of the computer on which the command is executed).

To determine the correct date format, the following PowerShell command can be used:

$((Get-Date).ToString($(Get-culture).DateTimeFormat.ShortDatePattern))

This can also be used for calculation. Here is an example to determine the local date 180 days ago:

$((Get-Date).AddDays(-180).ToString($(Get-culture).DateTimeFormat.ShortDatePattern))

With very large datasets the database query will timeout after a while. Certutil will then return the error code -939523027. This can be worked around by looping the command in a batch file.

@echo off
Top
certutil -deleterow {date} Request
If %ERRORLEVEL% EQU -939523027 goto Top

Deletion of all expired certificates from a certain date onwards

ImportantThis command deletes all expired certificates, which is not desired in most cases. How to formulate more precise criteria is explained later in the article.

Deletion of expired certificates is possible with the following syntax:

certutil -deleterow {date} Cert

Here the date describes the expiration date (NotAfter) of the certificate.

It should be noted that the date must be in the past. Deletion of certificates that are still valid is not possible in this way.

Here, too, it is important to note that with very large data sets, the database query will time out after a while. Here is an appropriately adapted example for expired certificates.

@echo off
:Top
certutil -deleterow {date} Cert
If %ERRORLEVEL% EQU -939523027 goto Top

Deleting certain blacklists

The identification and deletion of certain blacklists is described in the article "Viewing the certificate authority database revocation list table" described.

Deletion of certificates according to certain criteria

The previous examples are unfortunately not suitable for all cases as far as the search criteria are concerned. In addition, they only work with makeshift methods for larger databases, since there is a timeout value after database queries are terminated.

To avoid both, it is a good idea to first formulate a precise database query and then cache it. Caching is necessary because of the timeout problem; direct processing in a pipeline would be possible, but would also be aborted after a while.

Here is a sample command to identify certificates expired on a specific date from a specific certificate template and save the request IDs to a CSV file.

certutil ^
-view ^
-restrict "CertificateTemplate={OID-of-template},NotAfter={date}" ^
-out "RequestId" ^
csv > {path-to-file}.csv

The certificate template is identified by its object identifier, which can be determined, for example, from the Certificate Template Information extension of one of the certificates.

The generated file should now contain the corresponding entries. It is important to determine the exact name of the header, this differs depending on the operating system language set.

The next step is to read the CSV file via PowerShell and initiate a deletion for each entry.

Import-Csv -Path {path-to-file}.csv | ForEach-Object -Process {
    certutil -deleterow $_. "Issued Request ID".
}

This method is potentially slower than the previously mentioned ones, but queries can be formulated more precisely and there is no danger of the database session running into a timeout, since each deletion of a row is a database operation in its own right.

Before the deletion is triggered, the results should be checked carefully to avoid deleting the wrong certificates.

Compacting the CA database

The certification authority's database can now be compacted. The exact steps for this are described in the article "Compacting (defragmenting) the certification authority database" described.

Trivia

If audit event monitoring is enabled, the certification authority will record a deletion in the security event log (Event no. 4896) write.