BCS in a multi tenant scenario

Question

I'm planning on creating a BCS layer which will be used in a multi tenant scenario. The plan is as follows: multiple clients will all get a SharePoint site. They will sync data from their ERP into a central database, which is then queried by BCS. The database is setup to be multitenant, so all records have a key which includes the tenant Id. I need to setup the BCS so that it only reads data with the correct tenantId, and never allows users to see data from another tenant.

What I have thought of is deploying the BCS model as an assembly and using the code to sniff the context of the call and use that contact to query the correct tenant. But I was wondering if this is fool proof. What would happen when for instance Word connects to the SharePoint environment and query the BCS?

Another approach would be to deploy the BCS sandboxed, so each site gets it's own BCS definition instead. In that case I can probably insert the tenant info into the definition and handle it that way. The only thing to look out for is that a user can never adapt the definition and paste in another id (which would be quite hard when using a guid).

Has anyone ever done this? What approach (maybe there are other ones even better) would you choose and why?

Edit: I've gone with the .net assembly approach to make sure all content is filtered based upon the current SPContext. But it wasn't untill first deployment that I found out that a partitioned BCS app doesn't allow DotNetAssembly type LOBsystems. You can import it fine, and create a new external list; but it won't load any data. Don't know why, don't know how to circumvent this. Either way, I'm kind of screwed now since this means my whole idea isn't feasible at all.

Edit 2 (and I'll create a blog post for this); you can work around the partitioned BCS approach by creating a non partitioned BCS app and including that in your proxy group. Apparantly there are no restrictions on calling a non-partitioned service app from a site subscription bound site (at least not for BCS).

Answer 1

Let me check your requirements: You have a multi tenant database full with information, and all information is provided with a tenantID. You DON'T have SharePoint have setup as multi tenant.

what users do have access to create new external lists? If that is only you (as developer, administrator), You could go with separate BCS models where you create a query/stored procedure which includes the tenant ID. For each site, create a new External list which can be used by the customer.

However, If you users do have access, it will become harder. Key is: do your customers have access to the BCS models or not? If they do have, then all I can come up with, is: use a custom .Net connector which handles which tenantID has to be used. That is quite a complex solution (I like to keep it clean and simple).

as far as i know is deploying a BCS model using a sandbox, not possible.

Answer 2

I can think of two approaches:

1) Model the ECTs based on database views. You can create views based on TenantID.

2) Create and use ECT based on a WCF Service. Using WCF, you can provide access to data filtered by any business rules and security policies.

Answer 3

You can't make a filter when creating your External Content Types? You can do that through SharePoint Designer. Whatever the Tenant ID is you can filter it by the appropriate value, whether it be Username or other ID.

Also, you can create a database view that you can only grab items that have that specific tenant id and then filter through the BCS content and create an external content type based on that in SharePoint Designer. Each client can have his or her own tab with that External Content Type.