AD CS database schema

Using certutil we can query the schema of the Certification Authority database:

certutil -schema


If the AD CS role is not installed locally but on a remote computer the -config flag is required:

certutil -config <CAConfigString> -schema

The commands above return the column names (useful for any certutil database query using the -view flag), the column friendly name, the data type and the maximum length in bytes, including an "Indexed" comment on those indexed columns (useful for query design).
Querying the database schema is useful prior to preparing a CA database query, in order to understand the columns available in the database, their data types and whether they are indexed or not.
The CA database has 4 different tables, and certutil -schema returns by default the schema of the Request and Certificate table, but adding a parameter to the command will display the schema of the other 3 tables available:

Extension table - Ext

certutil -schema Ext


The extension table contains all the extensions of the certificates (and requests, even if certificates were not issued), where ExtensionRequestId matches the Request.RequestId column of the Request and Certificate table, but in the Extension table there are as many rows with the same ExtensionRequestId as extensions in the certificate with that Request.RequestId.
 
Attribute table - Attrib

certutil -schema Attrib


Similar to the Extensions table, the Attribute table contains all attributes (one per row) with the AttributeRequestId matching the Request.RequestId of a request.

CRL table - CRL

certutil -schema CRL


The CRL table contains the published CRLs and their information.

By default, a CA database query using certutil -view will display data from Certificate and Request, Extension and Attribute tables, linked by the Request.RequestId value. For example:

certutil -view -restrict "Request.RequestID=28354"

will display data from the Request and Certificate table where Request.RequestId=28354, data from the Extension table where ExtensionRequestId=28354 and data from the Attribute table where AttributeRequestId=28354. In order to get the data only from the Extension or Attribute tables that must be specified in the query and the correct column name must be used, e.g.:

certutil -view -restrict "ExtensionRequestId=28354" Ext

certutil -view -restrict "AttributerequestId=28354" Attrib

Comments

Post a Comment

Popular posts from this blog

Decoding OCSP GET requests

Many clients default to OCSP requests via HTTP GET, encoding the request details as part of the URL. These kind of requests can be found in the logs: http://ocsp.ekaitza.net/ocsp/MFQwUjBQME4wTDAJBgUrDgMCGgUABBQ1uvBCJo3G3TPBvK%2BGVqszOaLAawQUb38ZjesMwNeYLEzdGvP%2FZi9TlkACE3cAEdYaIhMmuymSdxoAAAAR1ho%3D As per RFC 6960 , the request is constructed as: GET {url}/{url-encoding of base-64 encoding of the DER encoding of the OCSPRequest} So in order to find out the details of the request we have to: 1. Remove the URL encoding This can be done with different tools, depending on the OS/platform being used.Given the input above: MFQwUjBQME4wTDAJBgUrDgMCGgUABBQ1uvBCJo3G3TPBvK%2BGVqszOaLAawQUb38ZjesMwNeYLEzdGvP%2FZi9TlkACE3cAEdYaIhMmuymSdxoAAAAR1ho%3D the output would be: MFQwUjBQME4wTDAJBgUrDgMCGgUABBQ1uvBCJo3G3TPBvK+GVqszOaLAawQUb38ZjesMwNeYLEzdGvP/Zi9TlkACE3cAEdYaIhMmuymSdxoAAAAR1ho= We can then create a text file with it, e.g.:  $ echo "MFQwUjBQME4wTDAJBgUrDgMCGgUABBQ1uvBCJo3G3TPBvK+GVqsz...
Read more

Signing a CSR with an Enrollment Agent certificate

Image
Often certificate templates in AD CS are configured to require a set of authorised signatures for issuance: This is useful e.g. when issuance approval is delegated to a component outside of AD CS roles, such as some CA management or registration authority portal software. Combined with the template security settings this can prevent requests from being raised directly from the CA without going through a custom standard request process, and depending on the level of security required, multiple signatures can be enforced and optionally also additional CA certificate manager approval. In order to produce each of this signatures an "Enrollment Agent" certificate is used, a certificate with the "Certificate Request Agent (1.3.6.1.4.1.311.20.2.1)"  Enhanced Key Usage. Sometimes it is required to raise requests directly towards the CA, e.g. using the RPC/DCOM interface from the command line, but it is not desirable and/or possible to change the template configuration, so t...
Read more

Compacting an AD CS database

Given the healthy trend towards decreasing certificate validity periods, it is very likely that the size of an AD CS Certification Authority database will grow following that same trend. Despite regular maintenance activities including deletion of records such as failed, denied etc. it could be that at some point the CA database reaches a problematic size. Deleting records from an AD CS CA database will internally free up the space previously occupied by those records but will not decrease the size of the database file itself. I.e. new records will reuse the freed up space but the total space used by the database file will not decrease. This helps keeping the database size under control but does not help if the size had already increased over acceptable limits; in this case the database must be compacted. The CA is often a critical resource, so it is recommended to keep various backups of different nature, to try to make sure we can recover it in case something goes wrong. Here a ...
Read more