Controlling Automated Renew Requests Generation Time in FIM CM/ILM 2007 CLM /MIM

February 26, 2018

tags: , ,
no comments

Abstract:

CLM in all of its versions has a mechanism to auto initiate Renew certificate Requests when the certificate is about to expire .

This article will explain how this mechanism works and how to better control it, for instance in a case where there are a lot of certificates that will require an update at a particular date and there is a need to update them in batches instead of all together.

In this article:

· General explanation on how the Auto Renewal mechanism works

· Method To control the Automated Renew Process

· Navigate the CLM DB to Find expiring certificates request id’s and more

How the Auto Renewal mechanism works:

· At the profile template – either in the renew policy or the online update policy the OTP definition is comprised of 4 positions for example 2,8,30,30

o number of OTPS – in the example there will be 2 OTPS

o length of each OTP – in the example each OTP will comprise of 8 characters

o when the certificate is in expiry period (when the renew \online update request will be generated) – in the example 30 days before the certificate expire date a renew request will be generated

o validity period of the OTP – in the example the OTP will be valid for 30 days

this is described here: https://social.technet.microsoft.com/Forums/en-US/12128753-b443-49d4-a4a3-9881ddc02cec/fim-cm-default-password-provider-settings?forum=ilm2

· when a certificate is enrolled those definitions are calculated (changing the profile template will not affect old certificates) and the designated renew request generation date is inserted into the database (table – Certificates –column cert_renew)

· the ClmService (it has different names thru the different versions for fim cm it is called “Forefront Identity Manager CM Update Service”) goes thru that table and for each certificate that has a cert_renew date older than the current time the service create a renew/update request this happens only once per certificate – the Service Does this by default every 5 hours or at a service restart this configurable as described here : https://technet.microsoft.com/en-us/library/ee534907(v=ws.10).aspx#BKMK_ConfigureRenewalRequests

How To control the Automated Renew Process

· change the cert_renew column for the relevant certificate (if changed to any point at the past the service will create a renew request) – this can only work once per certificate

for example the sql query:

UPDATE [CLM].[dbo].[Certificates]SET [cert_renew]=dateadd(dd,-1,getdate())where cert_issued_serial_number=‘certserialnumber’

Replace certserialnumber with the relevant certificate serial number

a similar process is described here https://technet.microsoft.com/en-us/library/ee534907(v=ws.10).aspx

· if the otp is still valid and there is a need to notify the user again ,you can http post to the CLM website to redistribute (resend the mail with the OTP) for instance in powershell (version 4 and above):

Invoke-WebRequest -Uri “https://clmserver/Clm/content/common/requests/DisplayRequestStatus.aspx?ID=$($uuid)” -Method Post –UseDefaultCredentials

$uuid – is the request UUID for the renew request in the DB

· create automated renew requests using the SQL API – the SQL API Reference is here : https://msdn.microsoft.com/en-us/library/windows/desktop/bb468093(v=vs.100).aspx . there are 2 store procedures relevant for the renewal request creation ,one for renew https://msdn.microsoft.com/en-us/library/windows/desktop/bb468071(v=vs.100).aspx , and one for online update https://msdn.microsoft.com/en-us/library/windows/desktop/bb468088(v=vs.100).aspx for example this is how to call the online update Store Procedure (the renew works the same only the Store procedure name is changed):

DECLARE @request_uuid uniqueidentifier

EXEC [dbo].[externalSubmitOnlineUpdateRequest]

@serial = N’smartcard manufacturer:{Smart card serialnumber}‘,

@reason = 0,

@template_oid = NULL,

@registration_data = NULL,

@comments = N’test’,

@request_uuid = @request_uuid OUTPUT,

@request_priority = NULL

SELECT @request_uuid as N’@request_uuid’

The SP require you know 2 parameters of the renewd smart card manufacturer and it’s serial number.

The SP creates a new line at a table called ExternalRequests The CLM Service is going thru that table (at its regular interval) and open request for the new lines at that table when it does it sets the column er_submitted_fl to 1 if the service was unable to create a request there will be an error code at the er_error_number column (instead of 0 which is an operation successful code)

Finding Useful Data in the Clm DB

General:

· all the dates in the DB are in UTC

Relevant tables:

Smartcards table: Contains all the data FIM has on SmartCards , each row represents a smartcard. examples to some of fields in that table :

· sc_uuid – the primary key of the table that exists as a foreign key at other tables

· sc_serial_number – the smart car serial number

· sc_manufacturer_id – the manufacturer id (usually a name like aladin/athena/ MSBaseCSP and such)

· sc_status – the state of the smartcard (2 for active ,5 retired etc)

Certificates Table: Contains all the Certificates issued by FIM CM each row represents a Certificate. examples to some of fields in that table :

· cert_id – the primary key of the table

· cert_request_revoked_when – the date when a revoke request happened (if the certificate is not revoked this should be null

· cert_issued_certificate_hash – the certificate hash

· cert_issued_serial_number – the certificate Serialnumber

· cert_not_before – the certificate vaild from date

· cert_not_after – the certificate expiration date

· cert_user_uuid – a foreign key to the UserNameCache that translate to a user name

· cert_renew – the date when the clmservice will create a renew request

ProfileCertificates table: contains a translation from the profiles table(profile_uuid column) to the certificates table cert_id Primary key

Profiles table: this table connects between most of the other tables in the db. Every row in that table represents a Certificate connection to a smart card (if a smart card has 2 certificates in it, it will have 2 rows),examples to some of fields in that table:

· pr_status – represents if the SC is active or revoked (2 represents active)

· pr_current_fl – represents if this is the latest profile in a case when a smartcard being reused old profiles will be marked as 0 and the active profile will be marked with 1

· pr_sc_uuid – a Foreign key to the smartcard table

· pr_assigned_user_uuid – a Foreign key to the UserNameCache table

· profile_uuid – a Foreign key to the ProfileCertificates which in turn has a Foreign key to the certificates table

Requests table : Contains all the requests in the system and their state, examples to some of fields in that table

· request_uuid – the primary key of the table

· req_type – code that represents the type of request for example

o 1 – enroll

o 2- Recover

o 3 – renew

o 4 – disable

o 5 – online unblock

o 6 – duplicate

o 7 – retire

o 10 – online update

o 14 – offline unblock

· req_submitted_dt – the utc date when the request was created

· eq_completed_dt – the completion date of the request (if null then the request is open )

· req_sc_uuid – foreign key to the smartcards table

· req_profile_uuid – foreign key to the profile table (null before completion )

· req_target_user_uuid – the user the request is assigned to

ExternalRequests table: Contains all the external Requests opened with the Store Procedure , examples to some of fields in that table

· external_request_uuid – primary key of the table

· er_created_dt – the date the External request was created

· er_submitted_fl – 0 if the service still hasn’t processed that External request, 1 if it did

· er_req_request_uuid – a foreign key to the requests table (after the service processed the external request and created a real request)

· er_sc_serial – the manufacturer id + smartcard serial as supplied in the external request creation (can be crossed referenced with the smartcards table)

· er_error_number – after the service processed the external request if the real request generation succeeded it wiil contain 0 all other values represents an issue at the real request creation proccess

· er_error_data – an explanation of the issue in the real request generation

useful queries:

get all smart cards expiring in a certain date

SELECT unc.unc_user_nt4_name

,sc.sc_manufacturer_id

,sc.sc_serial_number

,c.cert_not_after

,c.cert_renew

FROM [CLM].[dbo].[Profiles] p

left join [CLM].[dbo].[SmartCards] sc on p.pr_sc_uuid=sc.sc_uuid

left join [CLM].[dbo].[UserNameCache] unc on p.pr_assigned_user_uuid =unc.unc_user_uuid

left join [CLM].[dbo].[ProfileCertificates] pc on pc.pc_profile_uuid = p.profile_uuid

left join [CLM].[dbo].[Certificates] c on pc.pc_cert_id =c.cert_id

where

convert(date,c.cert_not_after) = ‘2010.01.01’ and sc.sc_status =2 and c.[cert_request_revoked_when] is null and p.pr_status=2 and p.pr_current_fl=1

open external requests for all Smart cards in return from this query

/****** Script for SelectTopNRows command from SSMS ******/

use clm

go

declare @scid nvarchar(300)

declare @unc_user_nt4_name nvarchar(80)

declare @sc_manufacturer_id varchar(50)

declare @sc_serial_number uniqueidentifier

declare @cert_not_after datetime

declare @cert_renew datetime

declare @cert_issued_serial_number varchar(128)

declare SC_cursor cursor for

SELECT unc.unc_user_nt4_name

,sc.sc_manufacturer_id

,sc.sc_serial_number

,c.cert_not_after

,c.cert_renew

,c.cert_issued_serial_number

FROM [CLM].[dbo].[Profiles] p

left join [CLM].[dbo].[SmartCards] sc on p.pr_sc_uuid=sc.sc_uuid

left join [CLM].[dbo].[UserNameCache] unc on p.pr_assigned_user_uuid =unc.unc_user_uuid

left join [CLM].[dbo].[ProfileCertificates] pc on pc.pc_profile_uuid = p.profile_uuid

left join [CLM].[dbo].[Certificates] c on pc.pc_cert_id =c.cert_id

where

convert(date,c.cert_not_after) = ‘2010-01-01’ and sc.sc_status =2 and c.[cert_request_revoked_when] is null and p.pr_status=2 and p.pr_current_fl=1

open sc_cursor

fetch next from sc_cursor into @unc_user_nt4_name,@sc_manufacturer_id,@sc_serial_number,@cert_not_after,@cert_renew,@cert_issued_serial_number

while @@FETCH_STATUS = 0

begin

print @unc_user_nt4_name

set @scid= @sc_manufacturer_id+’:{‘+convert(nvarchar(200),@sc_serial_number)+’}’

EXEC [dbo].[externalSubmitOnlineUpdateRequest]

@serial = @scid,

@reason = 0,

@template_oid = NULL,

@registration_data = NULL,

@comments =@unc_user_nt4_name,

@request_uuid = @request_uuid OUTPUT,

@request_priority = NULL

fetch next from sc_cursor into @unc_user_nt4_name,@sc_manufacturer_id,@sc_serial_number,@cert_not_after,@cert_renew,@cert_issued_serial_number

end

close sc_cursor

deallocate sc_cursor

SAGI  U.  & SHMUEL H.

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*