Dataconnections in InfoPath and SharePoint
This walkthrough is based on screenshots of a dutch environment. This however should not be a problem. These screens in any other language would have the same options on the exact same spot. You even might learn a little Dutch while you’re at it.
What we are going to do here is create a dataconnection and eventually save it to a connectionslibrary in SharePoint as a .udcx file.
Create a dataconnection in infopath.
Select Database (Microsoft SQL Server only)
And click Next
Click op Database Selecteren
Select New SQL serverconnection.odc
And Click open
Enter the servername and select the type of credentials you would like to use. Click Next.
When you choose to use username and password it recommended to save these credentials in the file.
Select a table and Click Next.
Select the fields that need to be read.
and Click Next, Next and Finish.
Convert the dataconnection
Select the connection we’ve just created and Click convert.
Select the dataconnectionslibrary in SharePoint and name the file. Make sure to add the .udcx extension.
And Click OK.
Delete the original dataconnection and Click Add.
Select search the Microsoft Office SharePoint Server for connections.
And Click Next.
By clicking the Manage Sites button add the site that holds the dataconnectionslibrary.
Open the dataconnections folder and select the newly created dataconnection.
Name the new connection and click Finish.
The new dataconnection is ready for use.
Security settings in SharePoint
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 to 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.