Complete Microsoft Certificate Authority maintenance procedure

I got entrusted with the wonderful job of doing an audit/cleanup for both our certificate authorities, its a very interesting task but I learned that documentation on the certutil tool is very limited or non existent…so I decided to write my own.

This post describes how to identify which certificate templates are being used and audit all requests/certificates generated with them so its easier to decide which of them are obsolete and can be unpublished or deleted, remove all expired certificates and requests and finally defrag the CA database…I got one of our databases down to 60MB from 1.7GB.

First step is the analysis of the certificate templates that are published on the CA. Before cleaning up all obsolete certs and requests you need to make sure that all the published templates are actually the templates you use. I deleted unused templates that had been created and not used anymore and unpublished unused default templates (not a good idea to delete default templates). Here’s a list of all default templates

To see which templates are being used, we need to see the requests/issuance of certs for all templates. It is pretty straight forward query (after getting it right the first time). This is the query to get all requests for the User template (one of the default templates)

certutil -view -restrict “certificate template=user” –out [covered later]

The –restrict works as a filter and you just paste the name of template there, BUT this will only work for the default templates. To query user created templates (non-defaults) you need to query using the OID of the template like so

certutil -view -restrict “certificate template=” -out [covered later]

To get the OID of all published certificate templates issue this from PSH

Certutil –catemplates –v | select-string displayname,msPKI-Cert-Template-OID

Now we are ready to get all the requests/issued certs/failed requests/denied requests/revoked certs for all the published certificate templates, the last thing to mention are the disposition values that are used to filter, below are their descriptions.

  • 20 certificate was issued
  • 21 certificate is revoked
  • 30 certificate request failed
  • 31 certificate request is denied

Last part of the query is the –out field, this enables you to get only the certification information that you require i.e. cert issue date, expiry date etc. To get all the available fields, you need to do a certificate query and omit the –out field, this will give you all the fields that you can choose from, for simplicity’s sake below are all the fields that you can query for.

Request.RequestID         Request ID
Request.RawRequest        Request
Request.RawArchivedKey        Archived Key
Request.KeyRecoveryHashes     Key Recovery Agent Hashes
Request.RawOldCertificate     Old Certificate
Request.RequestAttributes     Request Attributes
Request.RequestType         Request Type
Request.RequestFlags        Request Flags
Request.StatusCode        Request Status Code
Request.Disposition         Request Disposition
Request.DispositionMessage    Request Disposition Message
Request.SubmittedWhen         Request Submission Date
Request.ResolvedWhen        Request Resolution Date
Request.RevokedWhen         Revocation Date
Request.RevokedEffectiveWhen    Effective Revocation Date
Request.RevokedReason         Revocation Reason
Request.RequesterName         Requester Name
Request.CallerName        Caller Name
Request.SignerPolicies        Signer Policies
Request.SignerApplicationPolicies    Signer Application Policies
Request.Officer         Officer
Request.DistinguishedName     Request Distinguished Name
Request.RawName         Request  Name
Request.Country         Request Country/Region
Request.Organization        Request Organization
Request.OrgUnit         Request Organization Unit
Request.CommonName        Request Common Name
Request.Locality        Request City
Request.State             Request State
Request.Title             Request Title
Request.GivenName         Request First Name
Request.Initials        Request Initials
Request.SurName         Request Last Name
Request.DomainComponent     Request Domain Component
Request.EMail             Request Email Address
Request.StreetAddress         Request Street Address
Request.UnstructuredName    Request Unstructured Name
Request.UnstructuredAddress     Request Unstructured Address
Request.DeviceSerialNumber    Request Device Serial Number
RequestID             Issued Request ID
RawCertificate             Certificate
CertificateHash         Certificate Hash
CertificateTemplate         Certificate Template
EnrollmentFlags         Template Enrollment Flags
GeneralFlags            Template General Flags
SerialNumber              Serial Number
IssuerNameID            Issuer Name ID
NotBefore             Certificate Effective Date
NotAfter            Certificate Expiration Date
SubjectKeyIdentifier        Issued Subject Key Identifier
RawPublicKey             Public Key
PublicKeyLength         Public Key Length
PublicKeyAlgorithm        Public Key Algorithm
RawPublicKeyAlgorithmParameters    Public Key Algorithm
PublishExpiredCertInCRL     PublishExpiredCertInCRL
UPN                 User Principal Name
DistinguishedName         Issued Distinguished Name
RawName             Issued  Name
Country             Issued Country/Region
Organization            Issued Organization
OrgUnit             Issued Organization Unit
CommonName            Issued Common Name
Locality            Issued City
State                 Issued State
Title                 Issued Title
GivenName             Issued First Name
Initials            Issued Initials
SurName             Issued Last Name
DomainComponent         Issued Domain Component
EMail                 Issued Email Address
StreetAddress             Issued Street Address
UnstructuredName        Issued Unstructured Name
UnstructuredAddress         Issued Unstructured Address
DeviceSerialNumber        Issued Device Serial Number

To get all certificate requests for a non-default template use the below, I use these fields in the following query for the –out, but you can use any of the above.

certutil -view -restrict “certificate template=” -out request.submittedwhen,Request.RequesterName,Request.CallerName,UPN,CommonName,NotAfter,Request.Disposition > c:\Template1-Requests.txt

For default templates use the name instead of the OID like so

certutil -view -restrict “certificate template=user” -out request.submittedwhen,Request.RequesterName,Request.CallerName,UPN,CommonName,NotAfter,Request.Disposition > c:\Template2-Requests.txt

You will have this output

Row 1:

Request Submission Date: 9/26/2005 11:58 AM

Requester Name: “DOMAIN\ab154777”

Caller Name: “DOMAIN\ab154777”

User Principal Name: “ab154777@domain.local”

Issued Common Name: “Amy Bott”

Certificate Expiration Date: 9/26/2006 11:48 AM

Request Disposition: 0x14 (20) – Issued

 To filter by issued, revoked etc. you just need to add the disposition you want in the certutil query (see table above), so if you want to what certificates where issued for the User certificate template use the below. Note that if you do not filter by disposition you get all the requests for that certificate template.

certutil -view -restrict “certificate template=user, disposition=20” -out request.submittedwhen,Request.RequesterName,Request.CallerName,UPN,CommonName,NotAfter,Request.Disposition > c:\Template2-Requests.txt

You should now have a clear idea of what certificate templates are being used and which can be unpublished or deleted. Before deleting any certificate templates I suggest that you back up the CA and also keep a dump of all templates using certutil –catemplates –v > c:\templatedump.txt.

The time to clear the CA database from the thousands of expired certificates and requests has arrived, backup the CA database before starting this. Actually this is the simplest step; you just decide the appropriate date and start deleting using the certutil –deleterow command with either the ‘Request’ or ‘Cert’ value, see table below

Name Description Type of data
Request Failed and pending requests Submission date
Cert Expired and revoked certificates Expiration date

If you need to delete all failed and pending requests up to 15th March 2012 use

Certutil –deleterow 3/15/2012 Request

To delete ‘all’ certificates expired by 15th March 2012 use

Certutil –deleterow 3/15/2012 Cert

The problem with this is that the command will just delete around 2500 entries and return an error (the deletions are successful; it just won’t go on deleting all entries. Luckily Mr Pork Chop Express wrapped the command in this batch file which works wonders, just replace the date and replace Request with Cert if you need to delete certificates.

@echo off


Certutil -deleterow 8/31/2010 Request

If %ERRORLEVEL% EQU -939523027 goto Top

In my maintenance I noted that most (if not all) of the user certificates were not deleted using this approach, I’m not sure why but maybe it’s got something to do with private key archiving being enabled in the template. However I managed to get rid of them using the RequestID field of the expired certificates with the certutil –deleterow i.e. certutil –deleterow 305 (where 305 is the RequestId), this has to be done a row at a time so PSH is best used.

In order to get all expired certificates before 1/1/10 open PSH and issue

certutil –view –restrict “notafter<=1/1/2010” –out request.requestid | Select-string –SimpleMatch “(“

The output will be like this ‘Request ID: 0xc8cb (51403)’, you only need the 51403 so choose all the bracketed numbers (the RequestIDs) and paste them in a notepad, then replace the white space and brackets to end up with only the numbers then save the file (this is parsing the hard way, use any other method you find comfortable).

Load the RequestIDs from the text file into an array $rqst = get-content c:\RequestIDs.txt. Now just use the array to delete all the expired certificates (rows) in the CA database with this command

$rqst | Foreach-Object { $_ ; certutil –deleterow $_ }

Now with a clean CA and no errors (you should have fixed any issues with autoenrollment and bad templates by now) we are ready to defragment the CA database and bring it to an almost new condition. A backup is again very called for here, so backup the database first.

In order for a successful defrag you need to have free space on the partition at least equal to the DB size, this because the esentutl will copy the database to another location on the same partition. Take note of where the CA database is located, you can easily check by going into the properties of the CA (from the GUI) and checking the Storage tab or running the certutil -databaselocations.

Stop the ‘Certificate Services’ service and disable it (Active Directory Certificate services from services.msc) then run the following command esentutl /d c:\windows\system32\CertLog\YourCA.edb.

This should be the last step in your Certificate Authority and (hopefully) all should have gone well, let me know if you found this useful.

The only useful link I found and used