Appreciations accepted

Vladlen Litvinov, the author: If you have some job offer for me, I'm ready to discuss it. View Vladlen Litvinov's profile on LinkedIn

Password

Tuesday, September 24, 2013

How to open FileNet Global Configuration Data without any accounts (Part1)

This article is dedicated to IBM technical support what could not help me in my small problem.
As said - if you want to do something well - do it youself.

Disclaimer: If you attempt repeat this solution - you'll do every your step on your own risk. Don't try to execute something on your working environment. I washed my hands.

Recently I have put in a lot of time in exploring of a new system in my suitcase of experience. This is FileNet.
We make preparations for several projects which use this product.

One customer gave us the FileNet environment as a virtual machine and databases. We deployed all on our own servers.
But FileNet configuration has one not very friendly property - it linked with some LDAP users store. You cannot open the configuration if you have not this store and the user (from this store) what has a Full Access to the configuration.

I opened the PMR - bla-bla-bla - the support cannot help me, because only its engineers can use engineering utility - bla-bla-bla

As usually I have to do this myself.



I started from the WebSphere security. There are no any secrets here - I have already written about this. (Link)

1. I decrypted the password of WebSphere administrator. Then I was able to open ISC.

2. I decrypted the password of the bind user for LDAP. Then I found out the user from the Directory. To tell the truth, in my case this user was the same as FileNet GCD administrator. But it is not very important.

3. I needed the LDAP repository (MS Active Directory). I created it on the same server and then I created the account from the step 2. Surely, this "dummy" domain must have the same name as original and the user must have the same Canonical Name.

4. I restarted my WebSphere and tried connect to FEM (FileNet Enterprise Manager). The first fiasco - I have been authenticated but have not got the Full Accees into GCD. To be continued...


I make the conclusion - user's SID stored in the GCD configuration.



5. I tried to look through the database GCD. It is a very interesting database, especially the table FNGCD. It contains 100 last configuration's snapshots. Every time you change the FileNet configuration its snapshot is being saved into GCD database. There is a problem here - the configuration is being saved as blob in the table. OK, try to open it.

There is an utility in the standard MS SQL Server installation - bcp.

Open SQL Management Studio and execute (for permission of shell commands):

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell',1
GO
RECONFIGURE
GO


Then another one:

Declare @sql varchar(500)
SET @sql = 'bcp GCD.dbo.FNGCD out c:/temp/gcd.xml -N -T -S localhost'
select @sql
EXEC master.dbo.xp_CmdShell @sql


And we have got the full xml file (gcd.xml) with all configurations of GCD.

If you want to explore one of the configurations, you can use this group of commands:

Declare @sql varchar(500)
SET @sql = 'bcp "select gcd_blob from GCD.dbo.FNGCD where epoch_id=54" queryout c:/temp/gcd.xml -N -T -S localhost'
select @sql
EXEC master.dbo.xp_CmdShell @sql


where epoch_id - it is the number of a snapshot.

But, in this case, you will not be able to import one snapshot of the configuration into your database.

6. Let's think. Where are the security definitions in the configuration? It is easy to guess. There are the attributes called "SecurityDescriptor" in the every configuration's object. Someting like this:

<?xml version="1.0" encoding="UTF-8"?><version epoch="54"> <It is the number of a snapshot>
<object flags="2" ObjectTypeName="Domain"> <We need the domain security descriptor>
<attribute id="300013" name="DefaultSite" flags="0">
<value type="4" id="{07539A4C-DD66-492B-8A0B-BC2363C9BDB9}"/>
</attribute>
<attribute id="300282" name="VersionString" flags="0">
<value type="1" string="5.0.1.04"/>
</attribute>
<attribute id="300257" name="IsAvailable" flags="0">
<value type="6" int="1"/>
</attribute>
<attribute id="500002" name="Epoch" flags="0">
<value type="3" long="11"/>
</attribute>
<attribute id="500007" name="SecurityDescriptor" flags="0">
<value type="2" blob="02000000000000000000000000000000000000000200000000001c00ffffffff03020700010500000000000515000000CC3986F9693A31D1F5F0FAEEF3130000008000c00ffffffff0100000001010000000000050b00000000000000"/></attribute>

I know the SID of my GCD administrator. It is S-1-5-21-4186... etc Probably I must convert it into HEX format. Do this (you can use this utility - SIDTranslator).
The result is:

010500000000000515000000CC3986F9693A31D1F5F0FAEEF3130000

I have already seen something like this!!! Our descriptor!!!

02000000000000000000000000000000000000000200000000001c00ffffffff03020700
010500000000000515000000CC3986F9693A31D1F5F0FAEEF3130000
008000c00ffffffff0100000001010000000000050b00000000000000

7. Then, no any problem yet.
  • I found the NEW SID in the ADUC console and converted it into HEX format.
  • I changed all the entries of OLD SID to NEW SID in the gcd.xml file (I use FAR. I like this utility. It is the best of the file commanders).
  • I deleted all the rows in the table FNGCD (Thanks, Timmy!)
  • I used this group of commands for importing the gcd.xml into database:
Declare @sql varchar(500)
SET @sql = 'bcp GCD.dbo.FNGCD in c:/temp/Currency.xml -N -T -S localhost'
select @sql
EXEC master.dbo.xp_CmdShell @sql


8. Start of WebSphere. Big Bang! FileNet GCD was broken.



Used: Windows Server 2008 R2, SQL Server 2008, IBM FileNet Content Engine 5.1 and skillful hands.

5 comments:

  1. Hi, I following your tutorial above, but in the step of importing gcd,xml back into the database, I got error start from Invalid time format, Unexpected EOF encountered in BCP data-file, text column data incomplete, cannot insert duplicate key row in object 'dbo.FNGCD' with unique index 'I_FNGCD01'. the duplicate key value is (0), and the statement has been terminated. Do you know how to solve it?

    ReplyDelete
    Replies
    1. I've forgotten this moment. Truncate the table FNGCD because you load the same configuration into it and you have index error.

      Delete
    2. Thanks for the tricks.
      I've tried successfully and it save my days of waiting time for the PMR.

      Actually no need to truncate because we export only the last_epoch_id row, so we can update the last row only. First, we must copy FNGCD table to EMPTY FNGCD table, Import to the new and update via SQL statement the gcd_blob field only. It works!.

      Thanks a lot.

      Delete
  2. Just want to add IF the database is DB2
    For export: use this command
    export to gcd.xml of del lobfile result_lob_modified select gcd_blob from fngcd where epoch_id= last_epoch_id
    Duplicate the result_lob_modified file and After replacing the SecurityDescriptor
    Export with this command
    import from gcd.xml of del lobs from result_lob_modified.001.lob modified by lobsinfile insert into fngcd_blank
    Last: do the update from fngcd_blank to fngcd table.

    ReplyDelete