Infopath has the ability connect to an external data source for data. The problem is there’s not a lot of information available on the net in guiding you to do this. You will most likely bump into problem when publishing the form to run on browser, to your production server where there’s multi-tier architecture (i.e. having multiple servers).
Basically, what happens is the database server cannot verify incoming credential from the front-end server. See this post: http://blogs.msdn.com/b/infopath/archive/2006/06/14/advanced-server-side-authentication-for-data-connections-part-1.aspx for better explaination.
What I’ve done is to use an UDC (Universal Data Connection) file, hosted in a Sharepoint Data Connection Library for the Infopath form to use to connect to an SQL data source. To find out more about UDC file, read this http://blogs.msdn.com/b/infopath/archive/2006/10/30/the-anatomy-of-a-udc-file.aspx
There’s a number of things need to be checked before it would work:
Go to Central Admin -> General Application Settings -> Configure InfoPath Forms Services: Make sure the followings are ticked:
- Allow users to browser-enable form templates
- Render form templates that are browser-enabled by users
- Allow embedded SQL authentication
- Allow user form templates to use authentication information contained in data connection files
- Allow cross-domain data access for user form templates that use connection settings in a data connection file
In your Infopath form template, you’d need to convert your datasource to a Data Connection File. See this post: http://msdn.microsoft.com/en-us/library/ms772101(v=office.14).aspx. You’d need to publish the file to a Sharepoint’s Data Connection Library.
Now go to the library and download a copy of the UDC file, open it with Notepad and change the existing connection string with your database server name, database name, login account and password. And most importantly, remove SSPI from it. This would not work if it’s there, even if you set it to false.