Microsoft SharePoint 2010 : InfoPath Forms Services - Accessing Data in InfoPath Forms (part 2) - Data Connection Libraries, Modifying UDC Files

Data Connection Libraries

When we added our data connection, the connection details were stored along with the InfoPath form. This technique is known as “embedding connection details.” In simple cases, this approach works well; however, in larger developments, such a technique may not be appropriate. Often multiple environments exist for testing, staging, and production. Embedding connection details within a form template would require the template to be changed for each environment. Also, if an embedded connection contains user credentials, these are stored as plain text within the file.

To get around problems like this and to promote the reuse of administrator controlled data connections, InfoPath can save data connections to a data connection library. To create a new data connection library, take the following steps:

1. Browse to the home page of the sample site that we created earlier: (http://<YourServerName>/Chapter5).

2. From the Site Actions menu, select More Options, and then, from the Create dialog, select Data Connection Library. Name the new library MyConnections.

We can now publish the data connection from our Demonstration Equipment Request form by taking the following steps:

1. In InfoPath Designer Fields pane, click the Manage Data Connections link.

2. Select the Customer data connection and then click Convert To Connection File.

3. Using the Browse button on the Convert To Data Connection dialog, select the MyConnections Document Library and save the file as Customer.udcx.

4. Click OK to save the Universal Data Connection (UDC) file.

Modifying UDC Files

By default, our data connection is configured to use Integrated Windows Authentication. This means that connections to the data store are made using the credentials of the user viewing the form. There are, however, a few problems with this approach. Probably the most obvious is that all users accessing the form must have permissions to the underlying data store. Another problem that isn’t so apparent is what’s known as the “double-hop issue.” NT LAN Manager (NTLM) doesn’t allow credentials to be delegated by an intermediary system. This is a problem when using InfoPath Forms Services, because the credentials are captured on the user interface tier, but it is the middle tier, the InfoPath Forms Services layer, that actually connects to the data source. Since the middle tier can’t impersonate the user connected to the user interface tier, it’s not possible to connect to a data source on a separate server using Integrated Windows Authentication.

You can, however, deal with this problem in a few ways: One way is to make use of the Secure Store Service . The other way is to embed a username and password in the connection details. Although using the Secure Store Service is the most secure option, for the purposes of this demonstration, we’ll use embedded credentials.

1. Create a login on the SQL server that contains the AdventureWorksLT database. Create a SQL login named InfoPathDemo with a password of password. Make sure that the server is configured to use Windows and SQL authentication.

2. With the login created, grant it read permissions on the AdventureWorksLT database. Check that it can connect by using the Connect option in SQL Server Management Studio.

3. UDC files are stored as plain text files containing XML in the SharePoint document library. However, by opening a file with Visual Studio 2010, the XML Designer makes it easier to see what’s going on. Navigate to the MyConnections document library, and then click the Check Out button on the Documents tab to check out the Customer data connection file.

4. From the Library tab, click the Open With Explorer button as shown:

   

5. The document library will be opened in Windows Explorer. Open the Customer. udcx file with Visual Studio and find the udc:ConnectionString element.

6. Change the connection string to include the following:

   Provider=SQLOLEDB.1;
   Persist Security Info=True;
   Initial Catalog=AdventureWorksLT;
   User Id=InfoPathDemo;Password=password;
   Data Source=<YourServerName>;
   

7. Save the file and then switch back to the MyConnections document library and check in the updated document.

8. Before the connection string can be used, a system administrator must explicitly approve embedded credentials in connection files. Navigate to Central Administration | General Application Settings | Configure InfoPath Forms Services. Check the Embedded SQL Authentication checkbox.

   Our connection file is now set up to use embedded connection credentials and will work properly for all users accessing it.

   Note

   Within the UDC file is a udc:Authentication element that’s commented out by default. To configure the connection to use Secure Store Service for authentication, uncomment this section. The AppId is the Target Application Id and the CredentialType will either be NTLM for Windows authentication or SQL for SQL Authentication.