Access SSAS Using an External User Database

Question

We have a BI team that has created a wonderful data warehouse that is great for internal staff using it through Excel on the internal network. They use Windows authentication through a domain controller, and everything works fine, including restricting access to AD users and groups.

The problem is that now we want to provide the same access to a wider audience that is not part of our Windows domain. This is further complicated by the fact that information about users who need access to the system is already stored elsewhere (an application with an SQL database).

The goal is to connect (possibly using HTTPS) to the cube (using Excel Analysis Services integration) and authenticate using the username / password that they had in the main application.

The main application has a WCF service interface for user authentication and session management, so all I really need is a way to provide authentication before MSMDpump.dll against this WebService. We can also add role-based mapping so that we can define SSAS roles for users in the application.

I thought I could create a DLL having the same interface as MSMDPUMP.dll and transfer the calls between the client and the main dll, but that seems a bit redundant.

Are there any ready-made tools for this? (and yes, I know that Sharepoint can do something similar, but this is not an option, so please do not offer this). Does anyone know any blogs detailing how to do this?

Any pointers where to start by creating an interface between 2?

The question is similar to How to protect MS SSAS 2005 for HTTP remote access over the Internet? However, I am considering providing an authentication mechanism from another data source, and providing SSAS roles, not to users. We do not want to configure a new user in SSAS for each user that is configured in an external application.

UPDATE. To be clear, external users need to connect to the cube using Excel, and the returned data must be filtered by the role they are in and the security applied in the cube. We can combine the cube to use dynamic dimension security and use CustomData attributes if that helps.

Answer 1

Ultimately, the solution turned out to be a combination of third-party control and the safety of dynamic measurement.

We found that it is not possible to easily apply the MembershipProvider interface to the MSMDPUMP interface without significant effort, so our solution gave them a web interface instead.

The control we used was DevExpress and is their "PivotGrid" control. This is not free, but significantly less than the cost of development resources for the implementation of any other custom solution.

Along with the control, we applied the Dynamic Dimension Security technology to the cube, so each user of the Site will have a dedicated connectionString for the cube with the addition of "CustomData" to the connection string. This allows us to delegate the tasks of splitting data into a Cube and BI group and let web developers focus on displaying controls.

The solution works quite well and does not include heavy-weight applications such as Sharepoint / Excel Services. It can be embedded directly on your website and branded as you need, providing a sales tool as well as a useful functional tool.

Answer 2

Can you create a user with limited access to the db cube (read-only, only for the corresponding cube, etc.) and hard code, which user / password is in the connection string in the db application?