Skip to content
February 18, 2008 / red1s

Connecting a web based InfoPath form to a data source


 

In this brief pictorial article I will display and show you how to add a web based Infopath form to your site with a data connection.

Note: It is brief, so for full details contact me…….

1) Configuring the InfoPath Forms services for all necessary and required settings as per picture:

clip_image002

2) Then enabling the forms proxy settings for the relevant web application:
clip_image004

3) Creating the necessary UDC file; which I’ve shown below and create and upload it to the newly created connection document library (make sure you approve it):
clip_image006

<?xml version="1.0" encoding="UTF-8"?><?mso-infoPathSolution solutionVersion="1.0.0.404" productVersion="12.0.0" PIVersion="1.0.0.0" href="file:///C:\Users\ridwan.sassman\Desktop\Universal%20Data%20Connection%20V2.xsn" name="urn:schemas-microsoft-com:office:infopath:Universal-Data-Connection-V2:http—schemas-microsoft-com-office-infopath-2006-udc" language="en-za" ?><?mso-application progid="InfoPath.Document"?><?MicrosoftWindowsSharePointServices ContentTypeID="0x010100B4CBD48E029A4ad8B62CB0E41868F2B0"?><udc:DataSource MajorVersion="2" MinorVersion="0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:udc="http://schemas.microsoft.com/office/infopath/2006/udc" xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2005-03-23T08:18:13" xmlns:xd="http://schemas.microsoft.com/office/infopath/2003">
    <udc:Name>New Conn</udc:Name>
    <udc:Description>New Conn</udc:Description>
    <udc:Type MajorVersion="2" MinorVersion="0" Type="Database">
        <udc:SubType MajorVersion="0" MinorVersion="0" Type=""></udc:SubType>
    </udc:Type>
    <udc:ConnectionInfo Purpose="ReadOnly" AltDataSource="">
        <udc:WsdlUrl>
http://</udc:WsdlUrl>
        <udc:SelectCommand>
            <udc:ListId></udc:ListId>
            <udc:WebUrl>
http://</udc:WebUrl>
            <udc:ConnectionString>Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=yourdatabase;Data Source=yourserver;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;</udc:ConnectionString>
            <udc:ServiceUrl UseFormsServiceProxy="false"></udc:ServiceUrl>
            <udc:SoapAaction></udc:SoapAction>
            <udc:Query>Select * FROM USERS</udc:Query>
        </udc:SelectCommand>
        <udc:UpdateCommand>
            <udc:ServiceUrl UseFormsServiceProxy="false"></udc:ServiceUrl>
            <udc:SoapAction></udc:SoapAction>
            <udc:Submit></udc:Submit>
            <udc:FileName></udc:FileName>
            <udc:FolderName AllowOverwrite="0">
http://</udc:FolderName>
        </udc:UpdateCommand>
    </udc:ConnectionInfo>
</udc:DataSource>

4) Once done add a connection to the form using: search for connections on the moss server, select and add your servers data  connection library
clip_image008

5) Once you’ve added the data connected information to the page as necessary by either dragging on the components or connecting pre-created components on, publish the form to your SharePoint server and click on the link provided(the easiest way) or browse to the published page:
clip_image010

6) Your final step once you click on this should look like the following:

clip_image002[4]

7) Please note if you are using a multiple server layout meaning that your SQL machine, WFE Server are on separate machines you might run into double hop issues and the like.

The following is an extract from Sheetal’s blog and can be located here: http://sheetal-d.spaces.live.com/blog/cns!237C3DEA7120098B!658.entry

I extracted it as it seems to be the most comprehensive and correct write-up I’ve found regarding errors on data connected InfoPath web based forms.

“In this blog post I will be talking about the data connection issues with Browser Enabled InfoPath forms. When we try to preview a browser enabled form connected to any data source (SQL server/Web Service/XML, etc) or when we try submitting the InfoPath form to a data source, we might get any of the errors listed below:

There was an error accessing a data source

or
An error occurred when accessing a data source

This is a very generic error message and it might occur due to several reasons. Well, I have tried to collect and post some root causes of this error message and workarounds/resolutions but these are not the only ones:-

1) Convert all the data connections into UDCX file. (Refer http://sheetal-d.spaces.live.com/blog/cns!237C3DEA7120098B!606.entry for information on UDCX files)

Note: Make sure the user/client has required permissions to access the UDCX file location.

2) In case you do not want to convert the data connections into UDCX files, you have to make the InfoPath form template fully trusted.
(Refer http://msdn2.microsoft.com/en-us/library/bb251025.aspx and http://blogs.msdn.com/infopath/archive/2004/05/10/129216.aspx for information on Fully Trusting InfoPath forms)

3) In case the InfoPath form is connected to SQL server,

– Make sure the user has all the required permissions on SQL Server

– If you have hardcoded the credentials (in connection string, UDCX file, etc), “Allow embedded SQL Authentication” must be enabled under Central Admin -> Application Management -> Configure InfoPath Forms Services

– Make sure we have cross-domain access enabled for InfoPath Forms Services under Central Admin -> Application Management -> Configure InfoPath Forms Services and also have this setting enabled in Internet Explorer for the zone InfoPath form loads

– Have both SQL and Windows authentication enabled on SQL server

– If your browser enabled form is not working with Windows credentials, try using SQL credentials.

4) In case your InfoPath form is connecting to an XML file, you need to have the XML file in the same domain/site collection and not on physical drive. If this is not what you need, have a web service which can access XML file from physical drive for you.

5) In case you have Alternate Access Mappings (AAM) enabled, please re-check your AAM configuration.

6) Is SQL server and SharePoint server installed on different machines?  If yes, you might be facing double-hop authentication issue, to resolve this (implement any one of the steps mentioned below):
– Setup Kerberos authentication

(Refer http://blogs.msdn.com/martinkearn/archive/2007/04/23/configuring-kerberos-for-sharepoint-2007-part-1-base-configuration-for-sharepoint.aspx which talks about configuring Kerberos for SharePoint 2007)
– Setup Single Sign On (SSO) and make sure it is configured properly

(Refer http://technet2.microsoft.com/Office/en-us/library/841080ca-3e3b-4dbc-a081-43c29c76b3551033.mspx?mfr=true for information on SSO Configuration)

Note: If you have both Forms Server Proxy and SSO turned ON, we might want to turn off the Forms Server Proxy and check the SSO configuration once. Or you can turn off SSO and have Forms Server proxy enabled. For more information, see http://msdn2.microsoft.com/en-us/library/ms771995.aspx
– Embed SQL server credentials in UDCX file

– Hardcode UserName and Password in the Web Service/.NET code, if any.
– In case of Web Service, enable anonymous access (if the webservice is used for data lookup kind of functionality.)

7) Do you get the error only when the form is accessed via Internet Explorer 6 browser (the issue doesn’t occur when you access the form via IE7 or when you open the form in thick client)?
To workaround this, setup Explicit credential in UDCX file:
<udc:Authentication>

   <udc:UseExplicit CredentialType="NTLM">

     <udc:UserId>domain\UserName</udc:UserId>

<udc:Password>Password</udc:Password>

   </udc:UseExplicit>

</udc:Authentication>

8) Do you get the error when InfoPath form is connected to userprofileservice.asmx web service and trying to call GetUserProfileByName web method? If YES, make sure in Shared Service Provider (SSP), we have the user who is trying to access the web service is added in the user profile database.

Additional Information

==================

Advanced Server-Side Authentication for Data Connections in InfoPath 2007 Web-Based Forms

http://msdn2.microsoft.com/en-us/library/bb787184.aspx

Advertisements

2 Comments

Leave a Comment
  1. Jose Parra / Dec 29 2011 1:47 am

    Many thanks. I still cannot believe how this old article saved me a bunch of time trying to connect an InfoPath form to a SQL DB using UDCX files. BTW, there is a type in the sample UDCX file :
    Cheers

    • red1s / Jan 11 2012 10:05 am

      Glad it was of help to you, yeah guess I should’ve mentioned it in the blog post as well (Well now it is 😛 )

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: