Blog-Mania

My conundrum: http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=3223680&SiteID=1

When people asked me if SharePoint 2007 was capable of multiple authentication methods; I knew enough to reply with an enthusiastic 'yes'! But until I was recently asked to resolve an oddly configured SharePoint URL scenario, I had no idea how it achieved multiple means of authenticating and how much real power their method provided developers/administrators.

In short, my supervisor desired a single SharePoint URL, capable of authenticating users via both Windows integrated AND Asp.net membership (forms) authentication. What I discovered is that, while SharePoint can 'easily' be configured to allow for multiple authentication types of the same content (i.e. Web Application), each unique URL can only have a single method of authentication. In hindsight, the answer seems obvious, from a logical standpoint, but not knowing how SharePoint did what it did; I couldn't intelligently reply to his request without first doing some research.

In point of fact, the multiple authentication ability within SharePoint 2007 is achieved by allowing multiple entry-points (zones) within a single web application; each capable of their own form of authentication. However, the phrase 'web application', to me is a bit misleading since I naturally liken it to 'web site' - when in fact each web application can warrant up to 5 IIS web sites; one for each zone. Since each zone earns its very own IIS website, each also posseses a distinct URL. These zones share a common content database; comprising the 'single' web application. Thus multiple authentication methods via a single URL is not possible.

And amidst my Google encounters with multiple web application zones; I stumbled onto the new fan dangle Alternate Access Mappings (AAM) functionality. Initially this further confused my evaluation of what I could/couldn't do with a single URL and SharePoint authentication, but in a nutshell AAM is just a mechanism with which to 'redirect' users SharePoint-style. Normal proxy re-direction will still display all of SharePoint's auto-generated hyperlinks with the address users are re-directed from, but AAM will notify SharePoint of the intended re-direction and update the links appropriately.
Some of the URL's I consulted to iron out my own understanding:

• http://blogs.msdn.com/sharepoint/archive/2006/08/16/configuring-multiple-authentication-providers-for-sharepoint-2007.aspx
• http://blogs.msdn.com/sharepoint/archive/2007/03/06/what-every-sharepoint-administrator-needs-to-know-about-alternate-access-mappings-part-1.aspx
• http://technet.microsoft.com/en-us/library/cc263208.aspx
• http://www.sharepointsecurity.com/content-97.html
• http://technet.microsoft.com/en-us/library/cc261814.aspx
• http://www.sharepointsecurity.com/content-97.html

For those of you that don't know; I recently changed employers and subsequent software platform(s)/task(s). I'm now working (occasionally) with MOSS 2007. Below you'll find my first relevant narrative contribution of MOSS 2007 encounters.

As one of my first MOSS 2007 assignments, I was tasked with developing a mechanism to display reporting dashboard/graphic metrics within SharePoint 2007 for an open-source, online, non-compatible, ticketing system we use that conveniently publishes its data as a web service. I knew it was possible via Sql Server Reporting services, but that would mean not only (potentially) importing the ticketing data into Sql Server but also upgrading our current version of Sql Server (to 2008). I thought perhaps there may be another option.

An Excel spreadsheet seemed the obvious answer with its built-in graphic metric capabilities and SharePoint's Excel Service, Excel Access webpart; a hypothesis I confirmed when I stumbled onto this little URL gem: Creating a User-Defined Function for Excel Services.

After much trial and error, I'm happy to say I was finally able to generate a dynamic Named Range of data via this User Defined Function and wanted to provide my readers with a list of the obstacles and conundrums I encountered and how I resolved them. Let me start by saying that it would be ideal if you can develop, not only the User Defined Function (UDF) class file itself, but also the accompanying Excel spreadsheet directly on your MOSS server since you'll need Excel services and the Microsoft.Office.Excel.Server.Udf namespace dll reference for development. But if you're like me, and that wasn't an option; don't worry, I'm going to list all the problems and workarounds associated with such an environment.

A couple potential gotcha's to remember:

• There are a number of variables and potential problems associated with subscribing to the web service itself which I'm not going to explore in this posting; so confirm you've got that functionality working correctly.
• Excel Web Services DOES NOT support tables - convert all tables to pivot tables/charts.
• Excel Web Services DOES NOT support macros of any kind - you will need to integrate all of your macro functionality into either Excel formulas or UDF's.

Now then, I found the coding of the UDF to provide input for the spreadsheet to be fairly straight-forward; just generate and return a two-dimensional array. However, when it came time to associate an Excel named range with my UDF function; I think the article did a poor job of explaining the procedure so here goes:

1. Select the cells you intend to populate with your UDF 2D array
2. Type in the relevant formula/method call: =GetMyRange()
3. Press Ctrl-Shift-Enter
4. You should then see all your selected cells become populated with proverbial #NAME? text and your function get surrounded by {} braces to indicate its attachment to a named range.

If your ultimate desire is to share the Excel dashboard within a SharePoint 2007 Excel web access web part; you'll need to enable the UDF functionality of your trusted location(s) as well as direct SharePoint to the chosen location of your UDF.dll's; easily accomplished via a couple of URL's within the Shared Service provider. Once done, however, you may find it exceedingly difficult to update your UDF .dll reference and functionality. Point of fact, I was unable to change my originally uploaded UDF .dll functional reference even after deleting the old one, uploading a new one, AND closing out all instances of my browser (in an attempt to end any sessions). It wasn't until I created an entirely new .dll with a DIFFERENT UDF method name that I was finally able to witness my UDF functionality change within my SharePoint web part (if you're still not seeing your changes, make sure you updated the newly named method call within your associated Excel spreadsheet).

In reference to the previously mentioned shortcomings relevant to developing outside of the SharePoint Server machine; there are a few. You'll need to copy the Microsoft.Office.Excel.Server.Udf namespace dll off of your server environment (usually found within the i386 folder) and add it as a reference into your UDF solution so that it will compile/build. Also, your testing capabilities of the Excel spreadsheet itself outside of the SharePoint server are virtually non-existent since you're not likely to have an instance of Excel services installed on your local dev machine - this of course can prove troublesome when combined with the resistant UDF updating capability I've listed above while attempting to test/troubleshoot.

In spite of the plentiful headaches I encountered while creating a SharePoint dashboard comprised of web service data; I'm still elated at the speed and efficiency with which I was able to plug into an external data source and display real-time, updating, graphic metrics within a SharePoint dashboard. I'm convinced this was brain-cells and time well spent since I'm certain I will be using this method again and again throughout our company's implementation of MOSS 2007.

A random weekday with a glass of slightly chilled red wine on my couch and hours worth of Scrubs episodes ahead of me.

Check out my old college geek-fest: Strong-Bad

blog-ol-o-gy (noun) [b-log-olly-jee] - the science of blogging; the science that deals with all forms of blog, including its classification, physiology, chemistry and interactions.