TM1 application maintenance utility and SingleSignOn

http://www.sebastienmillon.com

IBM reworked the way approval hierarchies work quite a bit in TM1 10.2. Refreshing any changes to approval hierarchies or updating application security now requires calling a special application maintenance utility. It’s all fairly straightforward, you do the approval subset changes as usual and then run the command line to trigger app_maintenance.bat file. You can even grab a copy Infocube’s TI’s to run this utility to do it in a more ’bundled-in TM1’ approach (just don’t forget to change ExecuteCommand Wait parameter to 0 in most of them). Application maintenance works nice and easy up until you start using it against CAM secured TM1 with Single SignOn configured.
You run exactly the same command, but get an error like:

<br />
com.ibm.cognos.fpmsvc.exception.FPMSVCException: INVALID_IDENTITY<br />
exitCode=700<br />

Turning off SSO makes everything work.

And I’m not alone in encountering this error and I expect a lot of others just don’t bother raising the voice. After all, SSO is a good thing, TM1 10.2 is a good thing, shouldn’t they just work together? 😉

I danced the whole IBM APAR ritual that ended up with a fairly standard ‘it’s by design’ answer. I’ll quote it here:

“Customer Statement: This tool is essentially a Cognos SDK tool that uses cognos SDK logon to manage applications.

An SDK logon is credentials that consist of user:password:namespace. The Cognos SDK does not support SDK logon when SSO is used.Customers using the Cognos SDK can write applications that use a web logon. It is some what difficult.

An SDK developer described one way to do this as –
“1. create a custom web application in the same domain as Cognos, and log into Cognos in same browser session (SSO creates Cognos Cookies).

2. have your new web app put ALL the Cognos cookies in the right location in the BIBus SOAPHeader with no CAM section, and send a query to CM with SDK.
3. It should return a SOAP response with a valid CAM_PASSPORT; take that BIBus SOAPHeader use it to call SDK APIs.

“If you use a procedure such a above to get a CAM_PASSPORT value you can use this value to run the app_maintenance tool.instead of specifying -useruid -userpwd -CAMNamespace, use the -credentials option and prefix the cam passort value with CAM:.i.e. -credentials CAM:MTsxMDE6MGNlMzIyMzMtYTc0Yi05YWFkLWNlNmMtOTg4YWNjNmJiMWIxOjM5NjE5NjQxNTU7MDszOzA7”

The first 10 times I read that, I thought: “SDK, web application, all this is definitely complicated and requires additional SDK licenses”. Running the app_maintenance with -credential parameter works, but writing a whole application to get CAM? Who in the world has the time to write that?

So we had a choice between forcing all users to input passwords all the time, or having to press the Refresh Rights button every time something changes. It’s obvious what we chose and fast forward 6 months and a lot of pressing refresh rights, I found a working solution to this problem on the weekend.

Updated 2017/09/01: Please see this post for the updated scripts.

The main idea is fairly simple (if you’re traumatised with VBscript enough):

  1. run Internet Explorer via vbscript
  2. login to Cognos BI by using LogonAs URL parameter
  3. grab the cookie that IE gets once authenticated to Cognos Connection portal
  4. strip the CAM passport out of that cookie
  5. run app_maintenance with -credentials and that CAM passport
  6. logoff user from Cognos Connection portal using the Logoff URL

Easy, right?

A sample VBScript would look like:

<br />
sLogonUrl = cognos_gateway_url &amp; &quot;?b_action=xts.run&amp;encoding=UTF-8&amp;m=portal/main.xts&amp;CAMNamespace=&quot;&amp;CAMNameSpace&amp;&quot;&amp;CAMUsername=&quot;&amp;CAMUser&amp;&quot;&amp;CAMPassword=&quot;&amp;CAMPassword&amp;&quot;&amp;h_CAM_action=logonAs&quot;<br />
sLogoffUrl = cognos_gateway_url &amp; &quot;?b_action=xts.run&amp;encoding=UTF-8&amp;m=portal/main.xts&amp;h_CAM_action=logoff&quot;<br />
logToFile &quot;Logon URL &quot; &amp; sLogonUrl</p>
<p>Set IE = CreateObject(&quot;InternetExplorer.Application&quot;)<br />
IE.visible=false<br />
IE.navigate sLogonUrl<br />
while IE.Busy<br />
WScript.Sleep 555<br />
wend<br />
sCAMPassport = &quot;&quot;<br />
aCookie = split(IE.Document.cookie,&quot;;&quot;)<br />
for i=0 To ubound(aCookie)<br />
    ' a name/value pair (a crumb) is separated by an equal sign<br />
aCrumb = split(aCookie(i),&quot;=&quot;)<br />
     '     WSCript.Echo aCrumb(0)<br />
    if (&quot;cam_passport&quot; = trim(aCrumb(0))) then  if (ubound(aCrumb) = 1) then sCAMPassport=unescape(aCrumb(1))<br />
NEXT<br />
LogToFile &quot;CAM Passport &quot; &amp; sCAMPassport</p>
<p>IF (sCAMPassport &lt;&gt; &quot;&quot;) THEN</p>
<p>     'create an execute string to run app_maintenance<br />
     sExecString = chr(34) &amp; app_maintenance_path &amp; &quot;\app_maintenance.bat&quot; &amp; chr(34) &amp; &quot; -serviceURL &quot;&amp;  serviceURL &amp; &quot; -credentials &quot; &amp; &quot;CAM:&quot; &amp; sCAMPassport &amp; &quot; -applicationid &quot;&amp; chr(34) &amp; application_id &amp; chr(34) &amp; &quot; -op refreshrights &quot; &amp;&quot;-logfile &quot; &amp; chr(34) &amp;sLogFileLocation &amp;application_id &amp; &quot;_refresh_rights_debug.txt &quot;  &amp; chr(34) &amp;&quot; -loglevel DEBUG&quot;<br />
     WSCript.Echo sExecString<br />
     Set WshShell = WScript.CreateObject(&quot;WScript.Shell&quot;)<br />
     WshShell.Run sExecString,1,true<br />
     LogToFile sExecString<br />
     Set WshShell = Nothing<br />
else<br />
     LogToFile (&quot;Couldn't authenticate and get CAM passport&quot;)<br />
end if</p>
<p>' Logging off Cognos<br />
IE.navigate sLogoffUrl<br />
logToFile &quot;Logoff URL &quot; &amp; sLogoffUrl<br />
while IE.Busy<br />
     WScript.Sleep 555<br />
wend<br />
IE.Quit<br />

I can share the full VBS or even the TIs we use (we’ve updated the original Infocube’s ones a while ago and added more stuff to support this), drop me a line if you need it.

Images by Sebastien Million, check him out, he’s awesome!

Update 27/04/2016: If you’re running into issues with IE logging and grabbing the cookie, it might be related to ‘modern’ IE techniques of silently relaunching the process when you go to Local Intranet or Trusted sites. Try using Set IE = GetObject(“new:{D5E8041D-920F-45e9-B8FB-B1DEB82C6E5E}”) as per https://blogs.msdn.microsoft.com/ieinternals/2011/08/03/default-integrity-level-and-automation/