Tearing apart the Certificate Lifecycle Manager 2007 database

Over the past couple of months I have been working a lot with CLM 2007. During this time, I wrote a bunch of custom reports, built a notifications module, and tweaked a number of other things to get CLM kinda of the way it should have been (out of the box). Naturally, while doing all of this I have gotten to know the structure of the CLM database a bit better. And, considering that there is very little documentation about CLM and how to customize it. I figured that some sharing of my notes from the notifications module I developed might be in order.

Below you will find two blurbs. The first blurb is just some notes about how values within the database relate to the notification workflows that were developed. After that, there are some notes that describe known request Statuses, Types, and Flags from the Requests table. This is stuff that is fairly easy to figure out. But, if would be so much easier if the product group just published a data dictionary (perhaps on Technet). Actually, I should infer that all product groups provide such information. After all, my assumptions are based on testing, and might be wrong. Compounded with the time it takes to map out the database myself, leads to an unhappy IT Pro.

For notifications, Request with flags of 1, 258, and 131330 are currently not included. However, Smart Card requests appear to be all over the place, do not be surprised if further logic changes are needed. Additionally, these are only dropped because the current profile is for self enrollment. If this changes, then the workflows need to change. Lastly, with self enrollment (no approvals) there may be a small timing window where a notification is sent out. It appears that with self enrollment profiles that CLM steps through all of the approval steps despite being none. I.E. it writes the request record as 16 and changes the request status to 3. Odd! Also, the workflows that are built into this function are hardcoded! If you make modifications to the workflows in CLM or changes to certificate templates, you may need to update this function!

Known Request Status

• 3 = approved and pending execution by requester
• 4 = denied
• 8 = approved and executed by requester
• 16 = waiting approval

Known Request Types

• 1 = Certificate Request
• 2 = Smart Card Request Related
• 3 = Smart Card Request Related
• 4 = Revocation Request
• 7 = Smart Card Revocation Request

Known Request Flags (Use this to determine what type of request this is!)

• 1 = Smart Card Related
• 258 = Smart Card Related
• 131330 = Smart Card Related
• 131073 = General Certificate Request

If you like this, check out some other posts from Tyson:

• When a computer science degree matters, and when it doesn't
• Since when did cloud computing become/need a manifesto?
• Why would one phish using a Certificate Authority (CA) as bait?
• Would I trust you, if everyone else trusted you?
• Here is a good question: Is scripting programming or just systems administration?
• PowerShell boy and the case of the missing cmdlets!
• Fun with PowerShell 2.0 Eventing!
• Creating a custom 404 page to handle link redirection for ASP.NET web applications

Or if you want, you can also check out some of Tyson's latest publications:

• Windows PowerShell Unleashed (2^ndEdition)
• Windows Server 2008 Unleashed (Yes, I did help on this book)

Lastly, visit the Microsoft Subnet for more news, blogs, and opinions from around the Internet. Or, sign up for the bi-weekly Microsoft newsletter. (Click on News/Microsoft News Alert)

• Microsoft
• CLM 2007
• database
• Requests