I was silent for about 3 months or so. There was no interesting topics to discuss and I hardly worked on my public projects, including PowerShell PKI module, PS Cmdlet Help Editor and ASN.1 Editor (not yet public). Today I completed new PS PKI module release and there is something to talk about.
New milestone
Hellya! We reached 2000 downloads in 6 months! I think, it is a good progress! :)
As promised, there was a need to move forward and use some more efficient tools. As the result, my underlying API library was migrated to .NET Framework 4.0. There are no plans to move to 4.5, so you can be safe about this. For some time. Windows PowerShell 2.0 do not support CLR 4.0 assemblies, therefore, Windows PowerShell 2.0 is out of support. Although, PowerShell scripts are written in PS v2.0 and do not use any features of new languages, I don’t think, it will be a big problem. Minimum supported PowerShell version is 3.0.
Existing changes
Get-DatabaseRow
The biggest change in this release is CA database access experience. Previously we had only four commands to access some CA database contents:
In v2.8 I added another command: Get-IssuedCRL. Actually, this command was a PoC (or draft) to provide enhanced access to a CA database. What next? Get-Attribute, Get-Extension to access Attribute and Extension tables? It was simple solution, but would add too many commands. Finally, I decided to regroup the code and provide one universal CA dumping function called Get-DatabaseRow. This function allows to access any database row in any table. In other words, it is a super-function, while already mentioned 4 functions are subset of this function. These functions will not be discontinued, as they provide the most common access functionality. The syntax of new command is the same as in familiar Get-*Request. The only difference is that there is a new parameter which accepts the table name to access.
Let’s recap general access techniques. First, we need to get the schema of the table to be accessed:
PS C:\> get-ca dc2* | Get-CASchema -Table crl Name DisplayName DataType MaxLength IsIndexed ---- ----------- -------- --------- --------- CRLRowId CRL Row ID Long 4 True CRLNumber CRL Number Long 4 True CRLMinBase CRL Minimum Base Number Long 4 False CRLNameId CRL Name ID Long 4 False CRLCount CRL Count Long 4 False CRLThisUpdate CRL This Update DateTime 8 False CRLNextUpdate CRL Next Update DateTime 8 True CRLThisPublish CRL This Publish DateTime 8 False CRLNextPublish CRL Next Publish DateTime 8 True CRLEffective CRL Effective DateTime 8 False CRLPropagationComplete CRL Propagation Complete DateTime 8 True CRLLastPublished CRL Last Published DateTime 8 True CRLPublishAttempts CRL Publish Attempts Long 4 True CRLPublishFlags CRL Publish Flags Long 4 False CRLPublishStatusCode CRL Publish Status Code Long 4 True CRLPublishError CRL Publish Error Information String 8192 False CRLRawCRL CRL Raw CRL Binary 536870912 False PS C:\>
We can use column names to create filters. For example, retrieve CRLs issued during last week:
PS C:\> get-ca dc2* | Get-DatabaseRow -Table CRL -Filter "CRLThisUpdate -gt $((Get-Date).AddDays(-7))" CRLRowId : 15320 CRLNumber : 5079 CRLThisUpdate : 2014.08.05. 9:09:37 CRLNextUpdate : 2014.08.06. 10:29:37 CRLPublishStatusCode : 0 CRLPublishError : - RowId : 15320 RequestId : 0 ConfigString : dc2.contoso.com\contoso-DC2-CA Table : CRL CRLRowId : 15321 CRLNumber : 5079 CRLThisUpdate : 2014.08.05. 9:09:37 CRLNextUpdate : 2014.08.06. 10:29:37 CRLPublishStatusCode : 0 CRLPublishError : - RowId : 15321 RequestId : 0 ConfigString : dc2.contoso.com\contoso-DC2-CA Table : CRL <...>
There are many. You can display additional properties by using “-Property” parameter:
PS C:\> get-ca dc2* | Get-DatabaseRow -Table CRL -Filter "CRLRowId -eq 15349" -Property *
CRLRowId : 15349
CRLNumber : 5086
CRLMinBase : 5082
CRLNameId : 0
CRLCount : 0
CRLThisUpdate : 2014.08.11. 16:32:57
CRLNextUpdate : 2014.08.12. 17:52:57
CRLThisPublish : 2014.08.11. 16:42:57
CRLNextPublish : 2014.08.12. 16:42:57
CRLEffective : 2014.08.08. 9:09:25
CRLPropagationComplete : 2014.08.11. 17:42:57
CRLLastPublished : 2014.08.11. 16:42:57
CRLPublishAttempts : 1
CRLPublishFlags : 70
CRLPublishStatusCode : 0
CRLPublishError : Published by CONTOSO\Administrator
CRLRawCRL : MIICAjCB6wIBATANBgkqhkiG9w0BAQsFADBHMRMwEQYKCZImiZPyLGQBGRYDY29t
MRcwFQYKCZImiZPyLGQBGRYHY29udG9zbzEXMBUGA1UEAxMOY29udG9zby1EQzIt
Q0EXDTE0MDgxMTE2MzI1N1oXDTE0MDgxMjE3NTI1N1qgcDBuMB8GA1UdIwQYMBaA
FGYgLGecDj5NRfL5lwWXZhNUEyY2MBAGCSsGAQQBgjcVAQQDAgEAMAsGA1UdFAQE
AgIT3jAcBgkrBgEEAYI3FQQEDxcNMTQwODEyMTY0MjU3WjAOBgNVHRsBAf8EBAIC
E9owDQYJKoZIhvcNAQELBQADggEBABiCowGlISbadz3hyBkJBBvp/DarZdoznNUu
rA5xofYNqmFUIqDnBXOSYguqAPxfW2l4IVtyS6wC7WRd8YMvQxUV778wskQVORs4
WTUY99zJjPDnK0WU8VL2YmPX99vSw5E13E+mo/vt1Q3CN+XQmC/jgf/YmZedOUyy
B5QDbLZP6/SwWQy3bRP0+PxwL89wzdrBvzLYSNqNErReeRgdPlYXoeH4neFAUhNZ
9NjwpZGYIMX1V4/b+i0q7dqQechdmKctfIr5r15PmQ3vz782UduNAp+5GkDPt3VD
Jf1PB2qUtlPfv7TWy4f0+Wa+ZS6/ff6XtunW5HzAOWuy8XfR+0k=
RowId : 15349
RequestId : 0
ConfigString : dc2.contoso.com\contoso-DC2-CA
Table : CRL
PS C:\>
We can see all aspects associated with the specified CRL. CRLPublishError column tells us that this CRL wasn’t issued automatically, but was manually triggered by a specified user account.
The same concept is used to access Attribute and Extension tables:
PS C:\> get-ca dc2* | Get-CASchema -Table Attribute Name DisplayName DataType MaxLength IsIndexed ---- ----------- -------- --------- --------- AttributeRequestId Attribute Request ID Long 4 True AttributeName Attribute Name String 254 False AttributeValue Attribute Value String 8192 False PS C:\> get-ca dc2* | Get-DatabaseRow -Table Attribute -Filter "AttributeRequestId -eq 1510" AttributeRequestId : 1510 AttributeName : RequestOSVersion AttributeValue : 6.0.6002.2 RowId : 1510 RequestId : 0 ConfigString : dc2.contoso.com\contoso-DC2-CA Table : Attribute AttributeRequestId : 1510 AttributeName : RequestCSPProvider AttributeValue : Microsoft Software Key Storage Provider RowId : 1510 RequestId : 0 ConfigString : dc2.contoso.com\contoso-DC2-CA Table : Attribute AttributeRequestId : 1510 AttributeName : ccm AttributeValue : dc2.contoso.com RowId : 1510 RequestId : 0 ConfigString : dc2.contoso.com\contoso-DC2-CA Table : Attribute PS C:\>
I just collected attributes associated with the RequestID = 1510. As we can see, the command returned multiple entries, each entry per attribute. To get the associated request, we can grab a corresponding row from Request table:
PS C:\> get-ca dc2* | Get-DatabaseRow -Filter "RequestId -eq 1510" RequestID : 1510 Request.StatusCode : 0 Request.DispositionMessage : Issued Request.RequesterName : CONTOSO\DC2$ Request.SubmittedWhen : 2012.12.14. 20:57:51 Request.CommonName : CertificateTemplate : OCSP Response Signing RowId : 1510 ConfigString : dc2.contoso.com\contoso-DC2-CA Table : Request PS C:\>
Ok, this row contains a OCSP Signing certificate. Ok, now we can try to get extensions associated with the request:
PS C:\> get-ca dc2* | Get-CASchema -Table Extension
Name DisplayName DataType MaxLength IsIndexed
---- ----------- -------- --------- ---------
ExtensionRequestId Extension Request ID Long 4 True
ExtensionName Extension Name String 254 False
ExtensionFlags Extension Flags Long 4 False
ExtensionRawValue Extension Raw Value Binary 4096 False
PS C:\> get-ca dc2* | Get-DatabaseRow -Table Extension -Filter "ExtensionRequestId -eq 1510"
ExtensionRequestId : 1510
ExtensionName : 1.3.6.1.4.1.311.21.7
ExtensionFlags : 131074
ExtensionRawValue : MCcGHysGAQQBgjcVCImQBoO+uDuHwY8PhLjyKoHh71UlASACAWUCAQI=
RowId : 1510
RequestId : 0
ConfigString : dc2.contoso.com\contoso-DC2-CA
Table : Extension
ExtensionRequestId : 1510
ExtensionName : 2.5.29.37
ExtensionFlags : 131072
ExtensionRawValue : MAoGCCsGAQUFBwMJ
RowId : 1510
RequestId : 0
ConfigString : dc2.contoso.com\contoso-DC2-CA
Table : Extension
ExtensionRequestId : 1510
ExtensionName : 2.5.29.15
ExtensionFlags : 131073
ExtensionRawValue : AwIHgA==
RowId : 1510
RequestId : 0
ConfigString : dc2.contoso.com\contoso-DC2-CA
Table : Extension
ExtensionRequestId : 1510
ExtensionName : 1.3.6.1.4.1.311.21.10
ExtensionFlags : 131072
ExtensionRawValue : MAwwCgYIKwYBBQUHAwk=
RowId : 1510
RequestId : 0
ConfigString : dc2.contoso.com\contoso-DC2-CA
Table : Extension
ExtensionRequestId : 1510
ExtensionName : 1.3.6.1.5.5.7.48.1.5
ExtensionFlags : 131072
ExtensionRawValue : BQA=
RowId : 1510
RequestId : 0
ConfigString : dc2.contoso.com\contoso-DC2-CA
Table : Extension
ExtensionRequestId : 1510
ExtensionName : 2.5.29.35
ExtensionFlags : 131072
ExtensionRawValue : MBaAFJ39/KrFuybixJrV0EtdamEKirpD
RowId : 1510
RequestId : 0
ConfigString : dc2.contoso.com\contoso-DC2-CA
Table : Extension
ExtensionRequestId : 1510
ExtensionName : 2.5.29.14
ExtensionFlags : 65536
ExtensionRawValue : BBR+s0tID9GKTJbXTuuBdrCgF4Zkng==
RowId : 1510
RequestId : 0
ConfigString : dc2.contoso.com\contoso-DC2-CA
Table : Extension
ExtensionRequestId : 1510
ExtensionName : 2.5.29.31
ExtensionFlags : 131074
ExtensionRawValue : MDgwNqA0oDKGMGh0dHA6Ly93d3cuY29udG9zby5jb20vcGtpL2NvbnRvc28tREMy
LUNBKDIpLmNybA==
RowId : 1510
RequestId : 0
ConfigString : dc2.contoso.com\contoso-DC2-CA
Table : Extension
ExtensionRequestId : 1510
ExtensionName : 1.3.6.1.5.5.7.1.1
ExtensionFlags : 131074
ExtensionRawValue : MF8wNAYIKwYBBQUHMAKGKGh0dHA6Ly93d3cuY29udG9zby5jb20vcGtpL2RjMmlj
YSgyKS5jcnQwJwYIKwYBBQUHMAGGG2h0dHA6Ly9kYzIuY29udG9zby5jb20vb2Nz
cA==
RowId : 1510
RequestId : 0
ConfigString : dc2.contoso.com\contoso-DC2-CA
Table : Extension
ExtensionRequestId : 1510
ExtensionName : 2.5.29.19
ExtensionFlags : 131075
ExtensionRawValue : MAA=
RowId : 1510
RequestId : 0
ConfigString : dc2.contoso.com\contoso-DC2-CA
Table : Extension
ExtensionRequestId : 1510
ExtensionName : 2.5.29.17
ExtensionFlags : 131072
ExtensionRawValue : MBGCD2RjMi5jb250b3NvLmNvbQ==
RowId : 1510
RequestId : 0
ConfigString : dc2.contoso.com\contoso-DC2-CA
Table : Extension
PS C:\>
Here we see all extension published in the issued certificate. These are not extensions from request, but extensions published in the end certificate. CA server may ignore some request extensions and adds its own extensions, like CDP, AIA, AKI and so on.
Remove-DatabaseRow
The next improvement was in Remove-Request. The biggest problem with this command was that it removed database rows one-by-one and did not supported non-Request tables. I rewrote the function and renamed to more generic Remove-DatabaseRow, so it supports:
- single database row removal (one-by-one, as it did previously);
- Bulk removal by using time condition. This removal is more efficient in CA database cleanup scenarios.
Bulk database removal is implemented in three parameters:
- CertificationAuthority – specifies the CA server to cleanup;
- Filter – specifies the table to cleanup;
- Date condition.
With these parameters we can specify the date and time before database row are considered old (I doubt that you are interesting in certificates expired 5 years ago) and safe for removal. In this case we don’t need to pass individual rows. The last example shows how you can greatly cleanup CA database from old entries in just two lines.
In the next post I will discuss about Set-Extension command and other interesting stuff.
Just to keep reference:
>> PowerShell PKI Module v3.0 <<
Post your comment:
Comments: