Use an External List in SharePoint Online to show BizTalk errors that are stored in SQL Azure

In the previous post I showed how to configure the Exception Management Framework in the ESB toolkit to route BizTalk fault messages to SQL Azure. In this post I want to create a portal to show the BizTalk errors to a user. As always, there are several ways to do this. SharePoint Online seems like a nice option because it already has building blocks to connect with Azure and you don’t have to worry about authorization, credentials, etc. It’s included in Office 365 and is a cloud-based service. I’m not a SharePoint developer but on iLoveSharePoint.com already is a great article about connecting SQL Azure to SharePoint Online so let's see if it can be converted to my example!

Steps

To use data from a SQL Azure database, you have to create an External List by using Business Connectivity Services (BCS) and Secure Store. BCS connects SharePoint to external data, and Secure Store enables user authentication for the data. By using an External List, you can display the contents of a table from SQL Azure in SharePoint Online.

The following tasks must be performed:

  • Route fault messages from BizTalk to SQL Azure
  • Create a Secure Store Mapping
  • Create the External Content Type in SharePoint Designer 2013
  • Create the External List in SharePoint Designer 2013
  • Grant permissions to manage the ECT

 

Route fault messages from BizTalk to SQL Azure
See the previous post how to create the EsbExceptionDb database in SQL Azure that I’m going to use
in this example and how to route the fault messages from BizTalk to it.
 
 
Create a Secure Store Mapping
First map a SharePoint user or a SharePoint group to the SQL Azure account.
Go to the SharePoint admin center within Office 365.
SharePointAdminCenter
 
Click secure store.
In the ribbon, click New.
ManageTargetApplications_000
 
In the Target Application Settings section set the Target Application ID, Display Name, etc,.
In the Credential Fields section, enter the field names that you want to use for the user name and password of the external data system.
In the Target Application Administrators field, enter the name of a group or a list of users who
can edit this Target Application.
In the Members section, in the Members field enter the name of the group that will use the Target Application.
TargetApplicationSettings
 
After you create the Target Application, you can enter the credentials that Secure Store uses to
access the external data.
Click Set Credentials.
SetCredentials
 
In the Set Credentials for Secure Store Target Applications dialog box, enter the user name and
password of the SQL Azure account.
SetCredentials_1
 
 
Create the External Content Type in SharePoint Designer 2013
You can create an External Content Type (ECT) by using Microsoft Visual Studio, or by using Microsoft SharePoint Designer 2013. (Don’t use the SharePoint Designer 2013 Preview version!!!)
Start Microsoft SharePoint Designer.
Click the Open Site button to open the SharePoint Online site.
SharePointDesigner_OpenASite
 
After the site opens, in the Site Objects tree on the left of the application window, click
External Content Types.
Select the External Content Types tab and in the ribbon click External Content Type.
ExternalContentTypes_NewExternalContentType
 
In the External Content Type Information section of the page, change the Name and Display Name.
The Display Name is a friendly name for the ECT.
Select the hyperlink Click here to discover external data sources and define operations to open the Operation Designer page.
ExternalContentTypes_Information
 
Click Add Connection to open the External Data Source Type Selection dialog box.
Select SQL Server to access the EsbExceptionDb database in SQL Azure.
ExternalContentTypes_AddConnection
 
In the SQL Server Connection dialog box:
Specify the Database Server, Database and the Name.
Select Connect with Impersonated Custom Identity. 
In the Secure Store Application ID text box, type the Secure Store Application ID that stores
credentials for the target database.
SQLServerConnection
 
To see a list of possible operations for the Fault table, right-click the table to open a shortcut menu.
Select a specific option such as New Read Item Operation Or just select Create All Operations.
ExternalContentTypes_CreateOperations
 
Click Create All Operations to open a wizard, and then click Next.
Select Finish to accept the operations properties that you configured.
AllOperations_Parameters
 
 
Create the External List in SharePoint Designer 2013
On the ribbon, click Create Lists and Forms.
ExternalContentTypes_CreateList
 
In the Create List and Forms for Faults dialog, type a meaningful name for the External List in the List Name text box.
Select a Read Item Operation from the list of Operations.
Enter the name of the SQL Azure database in the System Instance text box.
CreateList
 
 
Grant permissions to manage the ECT
Set Object Permissions for the admins to manage the ECT.
Go to the SharePoint admin center, and then click bcs.
Select Manage BDC Models and External Content Types.
Select the check box next to the name of the ECT that you just created, and then click
the Set Object Permissions.
Select Propagate permissions to all methods of this external content type.
Doing this overwrites any existing permissions.
image
 

 

Test the solution

When you now open your site and navigate to your new external list you will most likely get an error because SharePoint Online is not allowed to create an connection to SQL Azure.

ExternalList_Error
 
Go to the Azure Management Portal and select the EsbExceptionDb database.
Click on Manage allowed IP addresses to set the IP address of SharePoint Online.
esbexceptiondb_allowedIpAdresses
 
Now you can navigate to your external list and view the BizTalk errors in the Fault table.
When you click on … you can create a View to only see specific fields.
ExternalList_Faults
 
Right click on a row and select View Item to see all the fields in the Fault table.
ViewItem
 
Right click on a row and select Delete Item to delete an BizTalk error from the Fault table.
DeleteItem1
 

Conclusion

External Lists in SharePoint Online are a good choice if you only want to have a simple overview of the contents of the Fault table from SQL Azure. You do not need to have a lot of SharePoint development experience to generate the External List and the standard operations like View Item or Delete Item. It's also very easy to create a view in SharePoint Online for the External List. If you want more functionality like resubmitting the BizTalk message, External Lists are not sufficient but in that case you can use SharePoint Apps!

Sign up for an Office 365 Developer Site:
http://msdn.microsoft.com/en-us/library/fp179924.aspx

About the author

Tomasso Groenendijk lives in Netherlands and is a senior integration consultant at Motion10. He has over 15 years’ experience in software development and software design. Tomasso is specialized in application integration with a strong interest in the Windows Azure cloud platform. In 2014, 2015 and 2016 Tomasso was awarded with the Microsoft Azure MVP award. He is an active contributor to the integration community through his work on his blog, MSDN samples, TechNet Wiki and also speaking on events.

 
 
 
MVP profile Tomasso Groenendijk
 
 
Sentinet Product Specialist
 
 
BizTalk360 Product Specialist

Month List