Office 365 “Access denied by Business Data Connectivity” connecting to SQL Server / Azure

I haven’t wrote a blog post on SharePoint in awhile but this one seemed worthy of the effort.

I’ve been experimenting with Office 365 lately and all I wanted to do was add a connection to a Windows Azure SQL Database and surface it up as an external content type.

There is of course no straightforward documentation on how to do this but plenty of content advising that you can. I walked through the motions of creating a DB. I then went into the secure store and created a Target Application ID (Removing from User Name and Password) and then set the user name and password (a separate step).

Next I loaded SharePoint Designer (why? because I couldn’t find NEW External Content Type button anywhere else) and created a new external content type. I selected the appropriate Application ID and tried to connect and was met with the ever so useful error:  “Access denied by Business Data Connectivity”

I’ve searched for quite a while and the most common resolution is someone saying that you cannot connect to SQL Azure directly and to use WCF wrapper or some 3rd party product.

I hate writing wrappers for things that should be simple so I kept looking. I finally tracked it down and I’m blogging about it so I can look it up later when I forget and hopefully help someone else 🙂

Step 1:

In the SharePoint admin center click BCS then Manage BCS Models and External Content Types
step1

Step 2:

Select Set Metadata Store Permissions
step2

Step 3:

Add your user account and select appropriate permissions.

You will now be able to complete creation of the external content type. You will need to come back in here and use the Set Object Permissions once it is created..

Hope this helps!

Querying for SPListItems using an array of ID’s

There should be a super easy method of pulling back an SPListItemCollection by passing in a list of the ID’s you are interested in. Unfortunately, I haven’t seen one…

I wrote a short function that generates the CAML query needed.. Haven’t thoroughly tested it but so far it seems to do the trick.

public static String GetCAMLForMultipleIDs(int [] ids)
{
    StringBuilder sbCAML = new StringBuilder();
    sbCAML.Append(“<Where>”);

    if (ids.Length > 1)
        sbCAML.Append(“<Or>”);

    for(int i=0; i < ids.Length; i++)
    {
        int id = ids[i];
               
        String EQ = “<Eq><FieldRef Name=’ID’ /><Value Type=’Counter’>” + id + “</Value></Eq>”;
               
        sbCAML.Append(EQ);

        if ((i + 1) < ids.Length-1)
        {
            sbCAML.Append(“<Or>”);
        }
    }

    for (int orCount = 0; orCount < ids.Length – 1; orCount++)
    {
        sbCAML.Append(“</Or>”);
    }

    sbCAML.Append(“</Where>”);
    return sbCAML.ToString();



To use:

SPQuery spq = new SPQuery();
spq.Query = MyUtils.GetCAMLForMultipleIDs(EntityIDS);
System.Data.DataTable dtEntities = SPContext.Current.Web.Lists[ListName].GetItems(spq).GetDataTable();

Accessing PeopleEditor/Picker content from within a JQuery modal dialog.

The problem: Accessing the LoginName from the SharePoint PeopleEditor control from JavaScript did not work (results were blank) when the PeopleEditor control was hosted within a JQuery modal dialog.

Quick Solution:

In your dialog code

function ShowActivityDialog() {
$(document).ready(function () {
if ($dlgActivity == null) {
$dlgActivity = $(“#dlgActivity”).dialog(
{  bgiframe: true,
height: 500,
width: 1000,
modal: true,
position: ‘center’,
title: dlgActivityTitle,
resizable: false
});
}
else {
$dlgActivity.dialog(“open”);
}
$(“#dlgActivity”).parent().appendTo($(“form:first”));
});
}

Add the line in Bold.

Then you will be able to access PeopleEditor data. Here is something I put together to extract the LoginName:

// example: var loginName = GetPeoplePickerLoginName(‘peTaskOwner’);

function GetPeoplePickerLoginName(pickerid) {
// find the people editor hidden key to get the AssignedTo login name
var peopleEditHiddentEntityKeyID = “”;
var elem = document.forms[0].elements;
var loginName = “”;
var peoplePickerKey = pickerid + “_HiddenEntityKey”;

for (var i = 0; i < elem.length; i++) {
if (elem[i].id.indexOf(peoplePickerKey) != -1) {
var hiddenEntityElement = document.getElementById(elem[i].id);
if (hiddenEntityElement.value.length > 1) {
loginName = hiddenEntityElement.value;
break;
}
else {
// find the one with data in it
continue;
}
}
}
return loginName;
}