SharePoint & .NET Blog

If you are using  MS InfoPath 2007 to create a form that submits or retrieve data to/from a SQL database, be aware of the following limitations when using it.

 
The limitation with retrieving any data on a dedicated SQL database server, is that when user opens the form in a web browser (1st tier), the form will communicate with the InfoPath Forms Services (2nd tier), which in turns access the contact system data on a database server (3rd tier). However the security token from (1st tier) used with 2nd tier CANNOT be pass to the database server due to:

• Restriction inherent to NTLM authentication credential system that needs to access resources on a 3rd tier computer from resources from 1st and 2nd tier.
• If the InfoPath is trying to query or submitting to a web service (could be any other data source) and that web service is hosted as http://xyz.com/service.asmx and the sharepoint site/SQL database is hosted at another server box (e.g  http://abc.com). The InfoPath form is trying to talk to a SharePoint/databaes service which is hosted in a different domain server that can’t trust the other server hosting the infopath service. This probem is known as “Cross Domain Issues“.

Use Microsoft suggest using Single Sign-on (SSO) feature to resolve this. However if SSO can’t be implemented, then the other resolution is to:

• Populate  data from SQL database server to a new created custom list in SharePoint :- Disadvantage is that you will create an overhead as there is now 2 data sources to maintain.
• By giving full trust to the form(Tools – Form Options – Security and Trust) but its not the right way to go and your SharePoint admin might restrict that. The only way to go is setting the security and trust to ‘Domain’ and converting the existing data connections or just the cross domain connections to Universal Data Connection (UDC) format. Its very simple to do and gets rid of any cross domain issues. To create UDC connections read this article.

For those who wants the summary of setting up a UDC in SharePoint, follow these steps below:

1.  Go to the root website application in SharePoint where InfoPath is deployed.
2. Click “Create Site” on Site Action menu (If document centre subsite hasn’t been crated, then follow this step 2 to 3. Otherwise skip to go to step 4)
3. Select “Document Workspace” template under collaboration tab and fill in the rest of the details. Then click OK.
4. Click Create on the Site Actions menu.
5. On the Create page, click the Data Connection Library link in the Libraries section.
6. On the New page, type a name for the library and click the Create button.
7. Copy the URL of the new data connection library.
8. Open up InfoPath 2007 and open an existing form
9. On the Tools menu, click Data Connections.  Click the “convert” button on an existing data connection.
10. On the Convert Data Connection dialog box, enter the URL of the data connection library that you previously copied in step 7. 
11. Delete “Forms/AllItems.aspx” in the URL. Enter a name for the data connection file at the end. Click OK.
12. Browse to the SharePoint data connection library and, if necessary, Check In and Approve the UDC file created.
13. Re-publish the InfoPath form to SharePoint.
14. Go to SharePoint Central Administration->Application Management
15. Click “Configure InfoPath Forms Services” link
16. Tick “Embedded SQL authenication” & “Cross-Domain Access for User Form Templates” checkbox. Then click OK to finish.
17. Click on “Manage the Web Server Proxy” link
18. Select the appropriate web application in step 1. Then enable: i)the Web service proxy & ii) Web service proxy for user form. Click OK when finish.
19. Test the InfoPath form using the web browser and ensure that the form can now retrieve data from SQL server.

Another alternative on how to create a UDC file is to use the UDC file-creation tool. Download it and copy the xsn file to a temporary folder called “c:\document and settings\alexwein\dekstop” in order to initially view the xsn file in design mode. Then re-save the xsn file to your own preferred location and mark the xsn file as read only so that you don’t overwrite the original design. Then when you want to create a UDC file, use the original xsn file but save as another file before you begin creating the UDC file.

An example of how to practicall use the UDC file creation tool is to visit this site.

 Related sites

• How can I pass credentials from my web site to SQL server?
• How to pass credentials to SQL server?
• How to create a data connection library?
• InfoPath Blog
• How to anonymously submit InfoPath Form?

Go to top

Popularity: 4%

Add this page to your favourite social bookmark site: