Certificate can't be loaded from the database.

Products

• IT Management Suite

Issue/Introduction

The customer ran a repair on ITMS and these messages started to show up from approx. 100 different client machines:
 

Entry 1:
Certificate can't be loaded from database: serial='4E4442A4D99E7C51E14FED0D949CA52FA2136E40, thumbprint='BB59494C8770E66712217FC173E7BC847FCC04AF'

Entry 2:
Failed validation of certificate from 172.16.16.75 : Thumbprint 'BB59494C8770E66712217FC173E7BC847FCC04AF' Serial '4E4442A4D99E7C51E14FED0D949CA52FA2136E40' Issuer 'CN=<SMPserver-01>.<yourdomain>.com Agent CA' Subject 'CN=<W10C-desktop>.<yourdomain>.com'

Entry 3:
Failed validation of certificate from 172.16.141.78 :  Thumbprint '4863D21AE910D0BD50EDB02728BD1A6EF1CE270E' Serial '304BC63D675CB04F5CA444473C370D77B3660507' Issuer 'CN=<SMPserver-01>.<yourdomain>.com Agent CA' Subject 'CN=Server communication profile exported at: 11/21/2019 12:05:07 PM.' 

Environment

ITMS 8.1 or later

Resolution

The recommended way is to create a new CEM package and run it on those affected machines or wait for those machines to connect internally in order to update the proper information in the database.
 
In case that there are machines that can't install a new CEM package nor connect internally, the following steps are provided as a suggestion and it hasn't been fully tested by the Development team since the preferred method is to use a CEM Package:

1. Identify the actual machine having the issue. The NS logs entries (as the ones mentioned above) show the IP address of the machine. In most cases, it will also show the machine name. If not, you can use "ipconfig" from a command prompt to identify the hostname or use the following query if we have already records of that machine:
   
   select c.Name, ip.* from Inv_AeX_AC_TCPIP ip
   join vRM_Computer_Item c on c.Guid = ip._ResourceGuid
   where [IP Address] = '172.16.16.75'

2. Check what these machines have in "Inv_Digital_Certificate_Details" table. See if there any certificates associated with the found client machine ("Name" must be the client machine name). 
   
   select * from Inv_Digital_Certificate_Details
   where name like 'W10C-desktop%'

3. Also check resource association for those machines:
   
   select * from ResourceAssociation
   where ( ResourceAssociationTypeGuid = 'fd859758-beca-4cac-992f-555803651c0d'
   OR ResourceAssociationTypeGuid = 'd2c3dacc-5e96-4d23-b39b-87dca7b74533')
   AND ParentResourceGuid = 'found agent resource guid'

4. This script corrects situation for a client machine that has some certificates in the SMP database, but these are not certificates that are on the client machine:

   --KeyValue - is thumbprint taken from event log. In this example 'BB59494C8770E66712217FC173E7BC847FCC04AF' from the log entry above
   --cd.Name - is client machine name taken from NS log (for example: W10C-desktop')

   update ResourceKey set KeyValue = 'BB59494C8770E66712217FC173E7BC847FCC04AF'
   where KeyName = 'thumbprint'
   and ResourceGuid =
   (
   select ca._ResourceGuid from Inv_Digital_Certificate_Association ca
   join Inv_Digital_Certificate_Details cd on cd._ResourceGuid = ca._ResourceGuid
   where ca.Scope = 'Client Agent Certificates'
   and cd.Name = '<W10C-desktop>.<yourdomain>.com'
   )

5. After script execution, the client machine can connect to SMP in CEM mode and you as Administrator can go to the Certificate Management page (under Settings>Notification Server) and renew certificates for this client:

• Under Certificate Management, change the "View" from "Notification Server" to "Cloud-enabled Agents"
• Search for the affected machine. You may get 2 entries for that machine. Make sure to select the machine that has "Certificate is used by CEM agent to connect to NS" on its description (the other one usually is for the gateway).
• Click on "Renew" and follow the prompts. 

After renewal is finished, the client machine will have the correct certificates.