Major achievement! Single Sign On through BusinessObjects Restful Web Services

If you've ever tried to implement Single Sign On using Windows AD and Kerberos, you'll understand why the title of this post is Major Achievement!  Heck, even a blogger from Microsoft Support will tell you:

Kerberos is one of the more complicated technologies we deal with at Microsoft support. It is complex and can be utilized in highly customized ways between clients and servers which adds to the difficulty in troubleshooting. Application specific implementations are commonly unique in ways which aren’t documented well.  An additional part which makes troubleshooting Kerberos so very difficult though is the fact that it typically just works without problems. So no ‘under the hood’ knowledge is needed in the first place. 

I've implemented Kerberos a couple of times, and it has always been a major deal.  Luckily, I've been able to declare victory in the end and jump up and down on the top of my desk in a victory dance once the battle is won  (okay, the dance was in my head lol).

Hopefully these instructions will save you some pain!

**One fairly LARGE assumption I'm making is that you have already achieved a Kerberos SSO for normal BuisnessObjects login.  If you haven't done that, here is some documentation from SAP.  You'll want to refer to the original article written by Steve Fredell.**

The WACS

Since the BO RESTful Web Services uses the BO Server named "Web Application Container" or "WACS".  The WACS has to be configured for Kerberos.  If go to the properties, you will see some configuration fields related to Windows AD and Kerberos, but they are all for services other than the RESTful Web Service.  Kerberos settings are conspicuously absent from that section!

So what you have to do is modify the following file:

<Install Location>\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\java\pjs\services\RestWebService\biprws\WEB-INF\web.xml

You only have to specify your domain (realm) and SPN.  The other settings can be left untouched.

The one thing that remains a mystery to me is why I did not have to specify the location of my krb5.ini and bsclogin.conf files.  For my BI LaunchPad configuration, I specified this in the Java Options of the Java tab in the Tomcat Configuration like this:

-Djava.security.auth.login.config=C:\Windows\bsclogin.conf
-Djava.security.krb5.conf=C:\Windows\krb5.ini
-Dcom.wedgetail.idm.sso.password=xxxxxxxxxxx

I finally stumbled onto this forum post. With some deduction from the info there, I found you have to modify the command line in the properties of the WACS to add the following lines (I'm not sure if location matters, but mine is after the -Xrs):

Dcom.wedgetail.idm.sso.password=yourpassword -Djcsi.kerberos.maxpacketsize=0

The maxpacketsize is the max packet size it will use for UDP before using TCP. Changing this to 0 will force the use of TCP only, which allows for bigger packet sizes.  This is important if your users are members of many AD groups, because the full membership is passed along with the token.

This still doesn't explain how the WACS knows where to find the krb5.ini and bsclogin.conf files, but mine works.  All I can figure is that my files are in the correct default location, or there is some reference to Tomcat somewhere?

The code

My implementation was done in ASP.NET, so I'll give some code snippets from that standpoint.

The documentation from SAP BusinessObjects for SSO for the RESTful web services is in a weird location.  It is found in the SAP Crystal Reports RESTful Web Services Developer Guide.  The particular version of this doc I used was 4.1 Support Package 3 - 2014-04-03.  Search for "adsso" in that document and you will find a section "To get a logon token using an Active Directory Single Sign-On (AD SSO) account".


A must have tool is the Advanced REST client from Google that plugs into Chrome as an app.  Your first order of business should be to install that.   The irony is that I got a logon token from day one using the Advanced REST client, but it took me weeks working off and on to get everything else straightened out!  If you are lucky like me, you can just paste http://yourservernamehere.com/biprws/logon/adsso into the Advanced REST client and walla, you get a logon token!  If not, you'll have some basic kerberos troubleshooting to do, which is beyond the scope of this article.

Here is the C# code from an .aspx web form that is used to formulate the request:

string url = "http://yourservernamehere.com/biprws/logon/adsso";
HttpWebRequest myRequest = (HttpWebRequest)WebRequest.Create(url);
myRequest.Method = "GET";               
myRequest.Accept = "application/xml";
myRequest.ContentType = "text/plain; charset=utf-8";
myRequest.KeepAlive = false;               

//informational for debugging to verify the user being used for kerberos--this should be you!              
userName = HttpContext.Current.User.Identity.Name;
 
myRequest.UseDefaultCredentials = true;
 
myRequest.PreAuthenticate = true;                 
 
HttpWebResponse myResponse = (HttpWebResponse)myRequest.GetResponse();
 
//get the X-SAP-LogonToken from the header
this.LoginToken = myResponse.Headers["X-SAP-LogonToken"];

In the web.config file for your project, you will need the following:

  </appSettings>
<system.web>
<compilation targetFramework="4.0" debug="true"/>
<authentication mode="Windows"/>
<identity impersonate="true"/>
<customErrors mode="Off"/>
</system.web>
<system.webServer>
<validation validateIntegratedModeConfiguration="false"/>
</system.webServer>
</configuration>




If you don't include the validateIntegratedModeConfiguration="false", you may get an error from IIS that tells you have an incompatible configuration!
Now the trick to get this code to work is to get IIS and ASP.NET to pass along the kerberos ticket.  IIS is a "man in the middle" between the client and the BO server that must be configured to forward kerberos tickets.  That is why the Advanced REST client is an important troubleshooting tool, because it bypasses the IIS middle piece, and because it returns full error message details that ASP.NET does not expose in it's error messages.

IIS the Middle Man

Step 1:  Decide on IIS Express or local IIS

Here is an article from Microsoft describing the differences between the two

I ended up getting both to work.  The simplest route is to use IIS Express, so I'll describe that scenario first.  

IIS Express

This is the web server that is installed with Visual Studio and used by default for running and debugging from VS.  It does not have the GUI configuration tools for its settings that normal IIS does.

The settings are located in the following file:  My Documents\IISExpress\config\applicationhost.config

Here is how I configured it:

        <security>
        <access sslFlags="None" />
        <applicationDependencies>
        <application name="Active Server Pages" groupId="ASP" />
        </applicationDependencies>
        <authentication>
        <anonymousAuthentication enabled="false" userName="" />
        <basicAuthentication enabled="false" />
        <clientCertificateMappingAuthentication enabled="false" />
        <digestAuthentication enabled="false" />
        <iisClientCertificateMappingAuthentication enabled="false">
        </iisClientCertificateMappingAuthentication>
        <windowsAuthentication enabled="true">
        <providers>
        <add value="Kerberos" />
        <add value="Negotiate" />
        <!--<add value="NTLM" />-->
        </providers>
        </windowsAuthentication>
        </authentication>
        <authorization>
        <add accessType="Allow" users="*" />
        </authorization>

IIS Express runs under your Windows Account, which gives it broad privileges.  Running under your user also eliminates some complexity that is present in normal IIS where specialized accounts are used.  Unless I forgot something, that should be all you need!

Local IIS

You have to right click on Visual Studio and choose "Run as Administrator" to be able to use local IIS.  Right click on your project and choose "Properties", then go to the "Web" section and change the server to Local IIS like this:

Because IIS is the man in the middle, we have to run the website as a domain user, the domain user being a service account that was previously used to create SPNs.  The domain account must also be given the right to delegate in Active Directory as shown in this screen (this should already have been configured under the premise that you have a working BI LaunchPad AD configuration):

Your website must be configured to use an Application Pool, and the Application Pool must use the domain user service account mentioned above.  Set the identity as seen below, and verify that the Managed Pipeline Mode is "Integrated"

Finally, you must have the correct authentication providers for your website.  There are 3 different levels in the hierarchy of your website, and the providers must be set at each level.  I turned on Negotiate and NTLM at the root level, and then just turned on Negotiate at the Default Web Site level and at the lowest level (RestDemo application level in my case).  You could probably remove NTLM at the root level, because it should be inherent to Negotiate.  The way Negotiate works is that it first tries Kerberos, and if that fails, it falls back to NTLM.  Click the Configuration Editor for all 3 levels as seen below. Also verify that "UseAppPoolCredentials" and "UseKernelMode" are true:

Whew!  That should do it!  Now cross your fingers and say a little prayer and run your application!

If you need to troubleshoot more, start with this article: Explained: Windows Authentication in ASP.NET 2.0, then move on to the Kerberos articles out there such as this one:  Kerberos for the Busy Admin

--Craig

9 Tips for Visual Beauty in Dashboards, Reports and Charts

A colleague of mine put together some wonderful design tips for visualizing data, and because I have seen plenty of ugly reports and dashboards, I wanted to share his ideas.  Jacob Stark says:

As I have worked with customers on finding the best ways for them to visualize their data, 9 tips have proven invaluable in transforming ordinary reports and dashboards into powerful decision-making tools. 

Usually, the default dashboard or report looks something like this:

The customer has some great data and has put as much of it as possible onto the page.  The charts and graphs are using whatever default styles are available. 

1. Remove the “Chart Junk”

Upon first glance, the report is visually overwhelming.  To reduce the clutter, let us begin by removing the “chart junk”, extra pixels and lines unnecessary to the design.  By only removing the box outlines and extra gridlines, the report already looks cleaner.

2. Get Rid of the 3D!

People love the look of 3D pie charts, but they are extremely deceptive for visualizing data.  In the “Costs” 3D chart above, how easily can one tell if the 3rd or 4th quarter is larger?   It is especially difficult to tell when one piece of the pie is in the foreground and another piece is tilted towards the back. Whenever possible, avoid 3D.

3. Obscure the Unnecessary

Often, customers will want to include all of the wonderful data they have, trying to address the needs of anyone who may see their report or dashboard.  Unfortunately, this causes information overload.  We do not want to lose that valuable data, but we can obscure it so it does not overwhelm.  When designing a printable report, this could mean moving supporting data to a later section.  For a PowerPoint presentation, it could be moving data to backup slides.  For an online dashboard, this data could be revealed through a pop-up. 

For our example report, we’ve removed the customer complaint data.  When designing, ask yourself “what are the 3-5 most important pieces of information to show?”

4. Use the Proper Chart to Tell a Story

When looking at a chart or graph, I like to immediately understand a story.  Are things getting better?  Worse?  In our example, we see it is much more powerful to look at costs as a line graph than as a pie chart.  Now, we see a clear decline in costs, while with the pie chart is was difficult to know what the story was.  With sales, we’ve broken down the stacked bars and instead utilized side-by-side columns to make it easier to see the differences between months.  In some cases, a pictorial representation may not be needed at all.  For Goals, instead of showing a pie chart, we have reverted to the actual numbers.  Now, one can quickly be reminded of what their goal was and how they actually performed.

5. Think Big, Little, and Small

Not all data is equally important.  Use size to indicate which charts and graphs you wish to emphasize. In this example, we now know that costs are very important to our organization, followed, by our goals and actuals.  Another way to emphasize importance is through position, with the top-left corner usually being the most eye-catching position.

6. Focus on the Data

When looking at a chart or graph, it is important that our eye is drawn to the data and not the axis labels or other information.  Try using subdued colors and small font sizes on chart labels while increasing the width/size of the data itself.

7. Use Beautiful Colors

Color can be extremely difficult to work with.  There are a number of helpful websites, and one such is Adobe’s Kuler. As a reminder, it is always wise to avoid the color red unless you wish to call out an area of concern.

8. Set Goals and Indicators

We now have some clean, easy-to-read data, but it is still difficult to know how to feel about the data.  Yes, costs are decreasing, but should they have been even lower?  And did we meet our sales goals?  Exceed them?  By adding goal lines, it becomes much easier to see how well performance matches expectations.  We have also added conditional formatting to our “January New Customers”, turning the text red to indicate that we are currently far short of our goal.

9. Add Design Details

As a final touch, we add some lines and boxes to divide up the space. 

There are various ways we could continue to improve upon this design, but just by following these nine tips, we can already see a significant difference:

Webi - Make up your own data groups

Ever wanted to created some data in a Webi Report?  The rigid boundary between you and the source database makes that quite difficult.  There are some new techniques that help in this regard. One such technique is a feature in BusinessObjects Webi called Grouping that makes it possible to group the values of dimensions into new values.  This can be very useful if you want to combine data into higher level groupings.

For example, say you had some product lines, and you wanted to group them into higher level categories for seasons.  You can now do this all by yourself without touching your source database.

Here is a video that shows how to do it.

Caveat:  Your system must be updated to at least 4.1 SP2.  Bring donughts to your system administrator if the system isn't already up-to-date.  Disclaimer:  I'm the system administrator for my system and I happen to love donughts!  Who doesn't?  :)

Another such more advanced technique is uploading an Excel document into the BI LaunchPad, but that is a topic for another day . . .

Web Intelligence - Sorting a section by a measure in that section

Today I was trying to figure out how to sort a section in a Web Intelligence report by a measure in that section.  Highlighting the section and clicking the sort button was getting me nowhere.  The problem lies in what I believe is a bug.  In the Java Editor, if you right-click in the section, and choose Sort, Advanced, it brings up Manage Sorts.  When you click Add . . . there is no option to select the measure.

So the issue lies in the fact that a section gets a default sort created for whatever dimension the section is based on when it is first created.  And the trick to changing it is to select the dimension and remove the sort (from the Analysis, Display tab).  Now you can select the measure and apply a sort on it.  And checking the Manage Sorts window again, it shows the correct sort on the measure!

Scatter chart with lines between the points

In one of our Deski reports, we have a "funnel" chart.  This is essentially achieved with a scatter plot graph with lines drawn between the scatter points to resemble a line chart.  This allows us to draw odd lines, such as a funnels that don't really look like funnels.  :)  (Picture drawing a concentric circle for another visualization of what we are trying to accomplish).  So our problem is that we need to convert this Deski Report to Webi in order to move to BusinessObjects 4.1.  But we cannot seem to achieve this in Webi because the scatter plot in Webi does not draw lines between the points.  Any ideas out there?

Auditing Bug - high cpu, slow login, slow performance

So after months of working with SAP, we've finally identified a major problem that was affecting login performance and probably performance in general.  The culprit was Auditing!  The auditing folder was not being cleared out as it should be.  To be more precise, the files in the Auditing subfolder are supposed to be continually roll over and get reduced to 0 KB.  In my case, the files are stacking up and never getting removed.  If I stop the SIA, and rename the auditing folder and create a new auditing folder that is empty, then start the SIA again, the performance problems go away.  SAP has recommended a permanent fix to run a database script which will remove unique key constraints in the repo.  

How to find all reports associated with a universe - Relationship Queries

Today while talking with SAP Tech Support I stumbled across a wonderful little gem!  I've been asked a few times if I could find all the reports associated with a particular universe.  I know I could accomplish this with the Query Builder tool, but it always requires a lot of research to find the correct query to write.  Also documentation is pretty sparse.

So to cut to the chase, all you do is go into the Central Management Console (CMC), right click your universe, choose Tools then Check Relationships.  Easy Peasy!  You'll see not only reports, but a few other things as well.

Web Intelligence Report: The Blank White Screen

Occasionally, I'll have a user report that they get a blank white screen when trying to run a report.  One might be tempted to try the ordinary fixes;  check the browser version, check the Java version etc.  In this case the fix is different (and easier).  To solve, the user can simply change his/her preferences to Administrator Defined.  Go to Preferences, and on the General tab check the box at the very top that says Administrator Defined and click OK.  Walla!  The problem is solved!  If desired (but not required) the user may then uncheck the box again and define whatever personal preferences he/she wishes.

Java and browser compatibility - BusinessObjects 4.1 SP2 Patch 1

Ah the magical land of compatibility in BusinessObjects land!  This is a mysterious land, where at any moment, the solid platform underneath your feet can suddenly turn to quicksand!

When we implemented BusinessObjects 4.1 SP2 Patch 1 we had some problems.  We saw the following error when trying to create or modify reports using the Java editor:

Luckily, a new version of Java (Java 7 Update 51) had been released at about the same time, and installing it fixes the problem.  Whew!  Crisis averted!

Hello BI World, I'm Craig

Hello World!  This blog will be my attempt to relay information with regards to Business Intelligence.  I have over 10 years experience in Business Intelligence, primarily in a System Administrator role for BusinessObjects.  I'll post what I love and what I hate.  That's what blogs are for right?  My posts are a dose of reality in the hyped-up world of BI.  Hopefully you find something useful here!  The bulk of my blog will center around SAP BusinessObjects, since that is where the bulk of my experience is.