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.

SharePoint External Architecture & Implementation - Part 5

Previous Post :- Part 4
One of the major problem for a DMZ environment is that it’s a strictly controlled network enclave. In most of the scenarios you can’t access internet. Your organization might provide proxy as an interface to internet, however it could be a cumbersome process especially if it requires registration of each URL to be accessed.
Why am I talking about access to internet? As it turns out that SharePoint uses its own self created certificate for communication between many of its services. Also if you are using external domain certificates, these certificates will  contact CRLs (certificate revocation list) for certificate validation. Most probably these CRLs are on internet.
Whenever these certificates are invoked, that would be every time somebody connects, authenticates or services need them, these certificates would go online for validation. SharePoint would try to connect to internet to contact CRLs. If a CRL is not accessible, SharePoint would continue to try to access them. This would result in a degraded performance for end users. In fact it could be unacceptable performance. Fortunately there is a setting available in windows that could be used to work around this issue.
Open “Local Security Policy” on all SharePoint servers – type secpol.msc in run prompt.

Select “Public Key Policies” from left tree menu and open “Certificate Path Validation Settings” from right pane.

Select “Network Retrieval” tab. Check “Define these policy settings” checkbox if its not checked.
Removed check from “Automatically update certificates in the Microsoft Root Certificate Program (recommended)”.
Set 1 second for both “Default URL retrieval timeout” and “Default path validation cumulative retrieval timeout”.
Above setting would cause CRL validation event to timeout in 1 second hence saving a lot of time and performance.

SharePoint External Architecture & Implementation - Part 4

Previous Blog : SharePoint External Architecture & Implementation - Part 3
In previous blogs I talked about architecture and authentication for an external facing SharePoint environment. However in an enterprise setup, access control would be required to ensure that data is delivered only on need to know basis. Primary issue for an external facing environment is, that it may not have an active directory based user and group store to pick those users and groups for permission assignment. White SharePoint works seamlessly with an active directory, in absence of it a customized solution is required to attach a user and group store for assigning permissions. Because SharePoint uses claims for its internal working, any custom authorization process must provide user and group credentials through claims, hence the name custom claim provider.
Fire command get-spclaimprovider on SharePoint server and you will see a few inbuilt claim providers.
a custom claim provider is a .wsp solution for SharePoint, to be created in Visual Studio. SharePoint provides a few methods, which need to be implemented to attach a custom user and group store. It could be any database such as a SQL Server, file, SharePoint list etc…
Besides providing claims to SharePoint for assigning permissions, custom claim provider can also augment incoming user claims. What it means that you can provide or learn more information about a user is accessing the environment.
There are numerous resources available online to explain how to build a custom claims provider. Some useful that I found are given below.
http://www.codeproject.com/Articles/506023/Understanding-SharePoint-Custom-Claims-Provider
http://blog.podrezo.com/sharepoint-custom-claims-provider/
http://www.titus.com/blog/2012/03/building-a-custom-claim-provider-to-manage-security-clearances/
for Microsoft BI environment, here’s a very useful document.
http://social.technet.microsoft.com/wiki/contents/articles/15274.using-claims-authentication-across-the-microsoft-bi-stack.aspx
Note:- Custom Claim Provider creates issue with SSRS subscriptions if you are planning to use it for BI. I worked with Microsoft to find a solution to this, but even they are unable to pin point the reasons of this problem. For BI environment, you can come up with an alternate approach of creating sharepoing groups through a .wsp solution.