How to protect MS SSAS 2005 for remote access over the Internet via HTTP?

Question

We are creating a hosted application that uses MS SQL Server Analysis Services 2005 for some reports, in particular, for viewing OLAP cubes. Because it is intended for use by very large global organizations, security is important.

It seems that Microsoft prefers the client tool for viewing OLAP cubes - this is Excel 2007, and the entire infrastructure is focused on Windows Integrated Authentication. However, we are trying to create an Internet-oriented web application and do not want to create Windows accounts for each user.

It looks like there aren't many good OLAP web tools for AJAXy bricks (fast, drag and drop size, action support, cross browser, etc.). As an alternative, we are currently using the Dundas OLAP Grid , but have also reviewed RadarCube and other more expensive commercial solutions and are still thinking about taking CellSetGrid and developing it further - if you know any other cheap / open solutions, please give to know me!

Therefore, we plan to provide two access modes to the cube data:

• Through our own web application using one of these third-party OLAP web search tools.
• Direct access from Excel via HTTPS through the msmdpump.dll data pump because the web version is too slow / awkward or the user needs more powerful analysis.

To access the web application, the connection to the SSAS data source is from the web server, so we can happily pass the CustomData element in the connection string, which indicates which user is connecting. Since we potentially have too many combinations of rights to create separate SSAS roles, we have implemented dynamic dimensional security that uses the “Cube Users” dimension in combination with the CustomData element from the connection string and restricts Allowed Many different other dimension elements, respectively (via other relationships “Many -to-many "with dimension groups that contain" mapping rights ")

See Mosha on Measurement Security: http://www.sqljunkies.com/WebLog/mosha/archive/2004/12/16/5605.aspx

All this seems to work so far.

For a “direct connection” from Excel, we configured the data pump to access HTTP (see the MS Technet article ), but turned on anonymous access, relying on the connection string again to control access, since we do not have Windows accounts, however, in this case the connection string is user-controlled (we push the .odc file out of the web application, but a curious user can view and modify it), so we cannot rely on the users being good, and keep CustomData =[e-mail] from switching to CustomData=[e-mail]. As it turned out, it also causes the same problem with the roles, since they are also specified in the connection string, if you are not using Windows Integrated Authentication.

Therefore, the question boils down to the following: is there a way to get basic authentication in IIS that works without Windows accounts so that it can be used with an SSAS data pump so that SSAS knows which user is connecting so dynamic measurement security can be used successfully?

(This is my first q on StackOverflow and probably the most difficult question I have ever asked: let me know where I didn’t explain myself very well, and I will try to clarify)

Answer 1

Basic authentication will work with local user accounts (not for a domain) and even support pass-through authentication if local accounts exist on different computers, however, you must force SSL because basic authentication sends passwords in clear text.

You can use non-windows accounts with basic authentication in IIS with an add-on, for example http://www.codeplex.com/CustomBasicAuth , but SSAS will still need to know who this user is, and as far as I know, SSAS only uses authentication Windows

Answer 2

For a (relatively) cheap thin client interface for SSAS, see RSInteract . For bonus points, he will also consume SSRS reports and report models.

Any attempt to use measurement security will require SSAS to know about the user and grant them access rights. I see no way to circumvent supporting user rights.