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.
 
Click secure store.
In the ribbon, click New.
 
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.
 
After you create the Target Application, you can enter the credentials that Secure Store uses to
access the external data.
Click Set Credentials.
 
In the Set Credentials for Secure Store Target Applications dialog box, enter the user name and
password of the SQL Azure account.
 
 
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.
 
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.
 
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.
 
Click Add Connection to open the External Data Source Type Selection dialog box.
Select SQL Server to access the EsbExceptionDb database in SQL Azure.
 
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.
 
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.
 
Click Create All Operations to open a wizard, and then click Next.
Select Finish to accept the operations properties that you configured.
 
 
Create the External List in SharePoint Designer 2013
On the ribbon, click Create Lists and Forms.
 
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.
 
 
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.
 

 

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.

 
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.
 
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.
 
Right click on a row and select View Item to see all the fields in the Fault table.
 
Right click on a row and select Delete Item to delete an BizTalk error from the Fault table.
 

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