Serving Data Via Web Services – InfoPath

One of the more mind boggling requirements of developing InfoPath online browser based forms (i.e. the type that are intended to be served through Microsoft Office Sharepoint Services 2007 online form services) is that in order to update data on a back end database from the form you must do so through a web service. There is a reason for this that has to do with the way security is handled in the Windows world. This is in stark contrast to developing forms to be served through the InfoPath client itself where you can connect straight away to the database you need.

Now, if your organization already has SOAP document/literal type web services in place that you can retrieve a WSDL from to subsequently use to update your data consider yourself ahead of the game, and your Information Workers will thank you so they can get their work done.

But suppose that SOA hasn’t really caught on yet at your company and those web services aren’t there. How can you serve an InfoPath form in SharePoint that needs to update a backend database? You’ll need to write a web service to do that. This isn’t something a typical InfoPath form creator is going to be comfortable doing or will even want to try. At that point they will typically have to enlist I.T.’s help and get a developer involved. Thus breaking most any chance that Information Workers will be able to develop browser based forms on their own.

“You need a new web service over the customer database? No Problem!” your developer might say since they’ve got the latest and greatest with Visual Studio 2008 and .NET 3.5 they can whip up ADO.NET data services in short order. Unfortunately the developer isn’t going to go home early having knocked one out of the park. They are going to be gravely disappointed after they quickly whip up that service, demo their RESTful services coupled with the ease of querying content from the database, only to find out that InfoPath isn’t compatible with that most excellent RESTful ADO.NET data service.

Bonk! Here’s where I insert a plea to MS InfoPath team — can we please have a service pack to make AtomPub or JSON web services consumable?

Go talk to the Astoria team if you need to. I hope the InfoPath team has found a new Program manager by now.

So our developer heads back to the drawing board.

Let’s say you’re fortunate enough to be using SQL Server 2005 or newer on the back end. And let’s face it, SQL Server ROCKS!

Your developer, who is a DBA on the side, knows that stored procedures already exist on the server and all he’ll need to do is wrapper those in service endpoints and away we go. Of course opening up service end points on SQL Server will undoubtly raise eyebrows with your “real” DBA, so our developer likely doesn’t have high enough level permissions to instantiate those service end points.

Time to reassign the project task to the DBA who will need to get those services up and running for our end user. Luckily our DBA can reference the InfoPath team blog where they’ve detailed this procedure out. The DBA reluctantly opens up service end point on the server and lets the form creator know that they are ready to be consumed.

Can you guess what’s next? Let’s go back a second, we’re developing online browser based forms. Not InfoPath client only forms. Can you consume that SQL Server data service from Online Form services? Perhaps, but you will need to become familiar with a tricky little concept known in the Windows Authentication world as the double hop.

Oh crikey, we’re going to also have to enlist the services of the SharePoint Farm Administrator to get SSO working and that is just plain scary and time consuming. Are you using Kerberos? Do we have service accounts set up for our SQL server instance? Could we just use a UDC or ODC file? What are those mysterious xml connection files anyway? Do they go in the farm or on the site….. etc, etc…. ++ points of failure.

Never fear, your SharePoint Admin understands all of that and will get it set up straight away. Hold on, is your SharePoint Admin a domain admin as well? Can they even add accounts to Active Directory?

At this point in the chain, the original InfoPath form creator is probably getting tired of waiting for it to “just work” and decides that it if it takes at a minimum 3 IT personel working in coordination to get a simple web service so they can do a simple submit form that it may be time to “just leave InfoPath development to I.T”.

This is the unfortunate usability and development path of InfoPath online services and consequently SharePoint. Although it’s really not all SharePoint’s fault, it merely the middleman that makes things complicated from an authentication and authoriztion stand-point; it is a problem of trying to create a development environment utilizing InfoPath inside of SharePoint supposedly targeted at Information Workers.

Contrast this by an agile development life cycle used to get the requirments from the business user and complete a short iteration using ASP.NET, ADO.NET Data Services, .NetTiers, or heck even Silverlight to make it prettier and it’s a hard sell to see what is gained by the former rather than the later.

Here’s hoping that the next version of InfoPath and Online form services cross that boundary to get to a truly Information Worker based creation space.

I do have my doubts though countless years of hearing how end users will be creating their own BI reports in super easy no development required reporting tools; This process seems quite similar. The trouble is, it always seems to end up back in I.T’s court.  There is nothing wrong with that, but it needs to be understood before embarking on an InfoPath Online Form solution.