SharePoint External Architecture & Implementation - Part 6

Previous Blog - Part 5

Note:- This is a maxi post.

This blog will deals specifically with Microsoft BI scenario in external. As all roads lead to Rome, all problems lead us to single cause in case of externally facing environments – Authentication.

In case end users of an externally facing Microsoft BI environment are vendors and customers, you do not want them to see each other’s data. However you also don’t want to create separate set of reports for each of these different users. The best, optimized and accepted solution is to provide row level security and handle the authorization of user at the data source level. Different databases have different mechanism to handle row level security. I’m going to talk about how to enable row level security for SSAS (SQL Server Analysis Services) in absence of active directory with SharePoint as front end.

I’ve always wondered what was the reason that Microsoft didn’t allow any other authentication except for Windows authentication in SSAS. It would have provided architects some leg room to try it with non-Microsoft products, nevertheless, they left the window open with “CustomData” implementation as query banding (read here). optional parameters can be passed in connection string with binding it with CustomData. Once this CustomData value is retrieved on the SSAS server, it be used to run logic using DAX (Data Analysis Expressions).

Important thing to note here is that only two solution can be used in an external Microsoft BI environment (assuming its non-windows authentication environment) with row level security; namely SSRS (SQL Server Reporting Services) and PPS (Performance Point Service). Other service such as excel services, Power Pivot, Power View etc.… don’t provide a way to delegate end user’s credentials to the data source.

Now lets look at the implementation of row level security for an Microsoft BI solution with SSAS as data source. The problem has to be divided in two parts.

1. Send end user’s credentials to data source
2. Return only those rows where end user has access

Send end user’s credentials to data source

a) SSRS - Use following connection string within SSRS report data source. Make sure its an embedded connection string i.e. connection string passed as a text string within SSRS data source because you can use SSRS inbuilt parameter USERID to pass end user’s identity this way. If you chose to use connection string by specifying it in SharePoint UI, this inbuilt variable will not be available. This is how a connection string would look like.

="Data Source=<SSAS Server Name>:<Port>;Initial Catalog=<Database Name>; Character Encoding=UTF-8; Customdata=" & User!UserID

b) PPS – PPS provides direct way to insert CustomData into the connection string. All you have to do is to click the checkbox “Provide the authenticated user as the value of the connection string property "CustomData"” on the PPS data connection properties page.

Because SharePoint keeps the login name in an encoded format (read here) the CustomData that is passed to the SSAS would be in encoded format.

Return only those rows where end user has access

On the SSAS cube, create a role and add member those accounts which were supplied in SSRS data source and PPS unattended service account. Make sure you don’t provide these accounts admin access on SSAS else the whole exercise of row level security would fail and all the users will see whole data.

Because CustomData has been passed via connection string, you can use its value and apply the logic on it. The first logic that you need to apply is to decode the encoded string passed by SharePoint. This can be achieved by using DAX in the role created for the cube access.

Right(right(CustomData(),len(CustomData()) - search("|",CustomData())),len(right(CustomData(),len(CustomData()) - search("|",CustomData())))- 
search("|",right(CustomData(),len(CustomData()) - search("|",CustomData()))))

Above DAX expression will return you the actual user login ID from encoded value passed from SharePoint. You can use the login ID to run logical operation to return only those rows which match certain values.