Issue with Azure SDK on a SharePoint Server

Few weeks ago I and Hilton Giesenow prepared togheter a SharePoint & Azure session for the TechEd Africa and the European SharePoint conference. During the preparation we discovered an issue running the Azure Compute Emulator on a SharePoint box (Azure SDK 1.5 and 1.6). First it seemed that the user profile service screwed up after you had started the emulator for the first time, but further investigations had shown that many service applications didn’t work properly anymore. The problem was, that the emulator sets the IIS anonymous user account to the application pool’s identity. Many thanks to Wictor Wilen for telling us which IIS setting has been affected ( we owe you a pint=>summit 2011 :)

The Error

The SharePoint error after you have started the Azure Compute Emulator for the first time:  “Object reference not set to an instance of an object”

SharePoint trace:

Unexpected System.NullReferenceException: Object reference not set to an instance of an object.    
at Microsoft.Office.Server.Administration.UserProfileApplicationProxy.get_ApplicationProperties()     
at Microsoft.Office.Server.Administration.UserProfileApplicationProxy.get_PartitionIDs()     
at Microsoft.Office.Server.Administration.UserProfileApplicationProxy.IsAvailable(SPServiceContext serviceContext)     
at Microsoft.Office.Server.WebControls.MyLinksRibbon.get_PortalAvailable()     at Microsoft.Office.Server.WebControls.MyLinksRibbon.EnsureMySiteUrls()     
at Microsoft.Office.Server.WebControls.MyLinksRibbon.get_PortalMySiteUrlAvailable()  

Fix it

To fix this you have just to set the IIS anonymous user back to IUSR. But unfortunately you have to do this every time after you have run the emulator. 

Open IIS Manager (inetmgr) and do the following steps.

 



Done.

Fix it with PowerShell

If you don’t like clicking like a monkey, you can fix it with PowerShell
PS> import-module webadministration

PS> set-webconfigurationproperty /system.webServer/security/authentication/anonymousAuthentication -name userName -value "IUSR"


Hope this will save you time.

Programming with External Lists In SharePoint Online (Office 365)

In my previous post I announced that SharePoint Online (SPO) in Office 365 now supports Business Connectivity Services (BCS) and shown an example how to connect SPO via BCS to SQL Azure. In this post we’re going to have a look at what you can do programmatically with external lists in SPO. In general we’ve three options: Sanboxed Solutions, Client Object Model and SharePoint Web Services.

Sandboxed Solutions

Especially using the sanboxed object model to program against  external lists in SPO  sounds promising. Just imagine, event receivers and workflow actions that can access external systems through an external list. I was really excited! But the disillusionment followed soon. It seems you can’t connect external systems in SPO via BCS from within sandboxed solutions!
If I try to programmatically access an external list within a sandboxed web part, I get an “The shim execution failed unexpectedly - Access is denied….” exception.

SPList externalList = SPContext.Current.Web.Lists["Customers"];

foreach (SPListItem externalItem in externalList.Items)
{
  writer.Write(externalList.Item["CustomerID"] + ", ");
  writer.Write(externalItem["CompanyName"] + "<br/>");
}

The sandbox stripes out the user’s security token and this means that the credential mapping (e.g. All Users) defined in the Secure Store Service doesn’t work within in the sandbox. The behavior is described in following MSDN article. The suggested work around is that the managed account that runs the user code proxy service (SPUCWorkerProcessProxy.exe) is mapped to the external credentials. But this is not possible in SPO

Client Object Model

What indeed does work is using external lists with Client Object Model. There is nice post about this from Steve Fox.
You can use the SharePoint ECMA Client Object Model to access the external customer list from my previous post.

<script type='text/javascript'>
ExecuteOrDelayUntilScriptLoaded(function () {

  var ctx = new SP.ClientContext.get_current();
  var web = ctx.get_web();
  var list = web.get_lists().getByTitle('Customers');
  var query = new SP.CamlQuery();

  query.set_viewXml('<View><ViewFields><FieldRef´Name="CustomerID" /><FieldRef Name="CompanyName" /></ViewFields></View>');
var items = list.getItems(query);

//If you try to use a "default" ClientContext.Load of ListItem data from an external list, 

//you will get the following error: "The given key was not present in the dictionary." Instead, you need to explicitly specify the fields you want in the CamlQuery, 

//and also in the ClientContext.Load method.
  ctx.load(items, 'Include(CustomerID, CompanyName)');

  ctx.executeQueryAsync(
    function (sender, args) {
      var strHtml = "";
      var listItemEnumerator = items.getEnumerator();
      while (listItemEnumerator.moveNext()) {
         var item= listItemEnumerator.get_current();
         strHtml += "<p>" +  item.get_item('CustomerID');
         strHtml += "," + item.get_item('CompanyName') + "</p>";
      }
      $get('output').innerHTML = strHtml;

    }, function (sender, args) { alert(args.get_message()); });

 }, "sp.js");

</script>
<div id='output'></div>

Web Services

Another way to program against the external list in SPO is using the SharePoint built-in Lists.asmx web service.

Summary

Although it's nice that SharePoint Online in Office 365 now supports BCS, but as developer I really miss the support for the sandboxed object model. I hope Microsoft will add it with the next service update.

Connecting SQL Azure To SharePoint Online with BCS

*Diret connections to SQL Azure aren't supported by SharePoint Online. You need a Azure WCF Service as a wrapper around SQL Azure. Steve Fox has written a blog post about this: Leveraging Windows Azure WCF Services to Connect BCS with SharePoint Online. Seems that it had worked was a bug ;-)*

Exciting news SharePoint Online now supports Business Connectivity Services (BCS)! What many people really missed in SharePoint online was the ability to connect to external systems. With the last update Microsoft has enabled the eagerly awaited BCS support in SharePoint Online. BCS within SharePoint Online supports SQL and WCF connections which you can easily setup with SharePoint Designer. What is not supported are custom .NET connectors build in Visual Studio. The reason for this restriction is the fact that custom .NET assemblies have to be deployed to the global assembly cache on the server and this sort of deployments aren’t allowed in SharePoint Online.
In this post I’m going to show you how to connect SharePoint Online to SQL Azure.

Setup Northwind Sample DB in SQL Azure

If you haven’t an Azure account yet, you can request a free trial here. Go to the Azure Management Portal and select database on the left pane. Choose your subscription and create a new database server. Enter your admin credentials for the server and add a firewall rule to enable connections from outside (IP Range 0.0.0.0 – 255.255.255.255). Create a new database called Northwind. In the ribbon click on manage to open the SQL Azure Management tool. Login, go to database schema and data and click open query and upload and run the following SQL script (the script has been adapted from the following CodePlex project http://nwindazure.codeplex.com).

Configure SharePoint Secure Store

In order to be able to connect BCS to SQL Azure we must add the SQL credentials to the Secure Store in SharePoint Online. Go to the SharePoint Online Admin Console within the Office 365 Management Portal. You will find two new menu items (Manage Business Data Connectivity and Manage Secure Store Service).

Go to “Manage Secure Store Service” and create a new Secure Store Application called “Northwind” as shown bellow.


After the application has been created you need to set the credentials. You could create a new user in SQL Azure or you can use the admin user that you have specified when you’ve created the database (not recommended for production).

Configure Business Data Connectivity Services

Before you can create a new external content type you have to give your user admin rights in BCS. Go to “Manage Business Data Connectivity” and  select “Set Metadata Store Permissions”.

Create the External Content Type in SharePoint Designer

Now we can create a new external content type in SharePoint Designer. Start SharePoint Designer and open a site in SharePoint Online.

Name the external content type “Customer” and choose the external system to connect to.

Choose “SQL Server” for data source type.

Copy and paste the full qualified name of your Azure SQL Server to the “Database Server” field (you can find the name in the Azure Management Portal). Choose “Connect with Impersonated Custom Identity” and enter “Northwind” (the name that we have defined for our Secure Store Application).

Create all operations for the Customer table (read list, read, edit, new, delete)

Go to the following dialogs with next, next, next and save the external content type. To create a list based on on our new external content type select “Create Lists & Form” (optionally you can decide to generate infopath forms instead of usual SharePoint list forms ).

Set Permissions for The External Content Type

To define who has access to our new external content type go back to the SharePoint Online Admin Console and navigate to “Manage Business Data Connectivity” and choose set permissions.

Test It

Now check that everything works as expected. Open your team site and navigate to your new external list.

The End

In my next post I’ll show you how to connect an Azure WCF Service to SharePoint Online.