Where is the keystore for domain controller certificates in Single Sign-On 5.1?
A couple of weeks ago I got a request from one of our Technical Account Managers that I would have thought to be a rather quick question on how to look at the contents of a java key store.
I recommended Portecle (brilliant tool btw.) but was soon to learn that there was some follow up questions.
What the customer actually wanted to do is to have a peek at the key store of the domain controller certificates which were saved in SSO to remove PoC certificates and move to production.
So there were a couple of follow up questions involved:
1) Where is that key store actually saved on the file system?
2) Is there a direct relationship between the identity source and the SSL certificate?
3) How would we actually remove the certificates from the key store?
Turns out the answer did involve quite a bit of work.
First I tried to find the file on the system by simply starting Process Monitor (another rather awesome tool, go watch the “Case of the Unexplained” presentations of Mark Russinovich if you haven’t done so to see the tool in action) and setting several filters for default SSO directories and key store file extensions but turned out to be unsuccessful.
Only place left to look would therefore be the SSO database and as it turns out this is actually the place where these certificates are persisted after being uploaded via the Web Client or using the ssocli command line tool.
This leads us to question number 2, can we determine a direct relationship between the certificate and any identity source. Short answer is “no”. The longer answer leaves us looking at some SQL Server queries.
By simply browsing through all the tables of the SSO database one quickly jumps out as being called in an interesting way, namely “IMS_CERTIFICATES”.
Let’s take a look at the select statement output for that table.
SELECT TOP 1000 [DESCRIPTION],[PURPOSE],[REF_ID] FROM [RSA].[dbo].[IMS_CERTIFICATES]
|RIAT Root CA Certificate||STS_TRUST_CERT||NULL|
I have cut unimportant fields here that would contain additional data but not serve the purpose. I did add one single domain controller SSL certificate into the Web Client and we do have one entry with the purpose of “LDAP_TRUSTED_CERT” in our table. Interestingly enough we do not have a REF_ID entry in that row though. So it looks like we actually are not having any binding between a specific identity source and that certificate.
Trying to understand further if anything else was updated in the database I rolled back the backup I made before importing the cert and started again to get an exact timestamp. Afterwards I ran the following SQL query.
SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query] FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest ORDER BY deqs.last_execution_time DESC
This gave the following output.
2014-04-08 12:39:47.240 (@P0 nvarchar(4000),@P1 int,@P2 nvarchar(4000),@P3 nvarchar(4000),@P4 nvarchar(4000),@P5 nvarchar(4000),@P6 nvarchar(4000),@P7 nvarchar(4000),@P8 datetime)INSERT INTO IMS_CERTIFICATES (ID, ROW_VERSION, NAME, DESCRIPTION, PURPOSE, DATA, REF_ID, LAST_UPDATED_BY, LAST_UPDATED_ON) VALUES ( @P0 , @P1 , @P2 , @P3 , @P4 , @P5 , @P6 , @P7 , @P8 )
2014-04-08 12:39:47.220 (@1 varchar(8000))SELECT [ID],[ROW_VERSION],[NAME],[DESCRIPTION],[PURPOSE],[DATA],[REF_ID],[LAST_UPDATED_BY],[LAST_UPDATED_ON] FROM [IMS_CERTIFICATES] WHERE [PURPOSE]=@1
So we can actually see that no other table was touched during the transaction and can confidently conclude that we do not have any identity source to certificate mapping within our SSO 5.1 instance.
As for the last question…
Disclaimer: This is not a tested or officially approved procedure by VMware at any means, use at your own risks, don’t come to me complaining if everything breaks, DO A BACKUP!
Well we do know the text that we need to look for within the purpose field of that table to get the proper rows, we then can filter by the date or try to unscramble the data encoding in which I will not go into any further for today. Deleting certificates is as easy as deleting the row but it is playing around with a backend database which could potentially break your most central vSphere component so it is not really recommended to do so.