Software security is one of my favorite topics. Over the years, I have done numerous security reviews with ISVs and Forture-500 companies. My observations are that: a) security is implemented towards the end of a software application/system development; b) there is a lack of security expertise – perhaps, dev is relying on IT and vice versa, that eventually the topic falls thru the cracks. Anyway, I am going to talk about SSAS security options.
1) Windows Integrated Authentication:
Out-of-the-box SSAS supports only Windows Integrated security. Features that are present in MS SQL eventually make their way into SSAS but not with custom security. Windows Authentication requires the presence of a Windows Domain; its Domain Controllers (DC) are the trusted authority (the assumption is that everyone trusts them) and are responsible for handling authentication requests (such as: can you prove to the accessed server that I am who I claim to be with my logon credentials). While this works pretty well in most cases, there are some challenges. The infamous “multi-hop” authentication is one common example. Before Kerberos, there was (and still is) the Windows NT LAN Manager (Local Area Network Manager, aka NTLM). It was using a challenge-response algorithm, in which the user provides a set of credentials and the server checks if they are correct with a table lookup. The disadvantage of this authentication type is that if the user cannot make more than one jump/hop. For example: a user accesses a web site on one server, the web server (IIS) needs to make a call to a SQL database on another server. In this case, we have two server hops (client->IIS->SQL. The NTML does not allow the originating caller identity to be forwarded on the second hop (IIS->SQL) since it does not know the original password (but rather a hash of it); one exception is the use of “Basic Authentication” where the credentials are transmitted as base64 encoded strings in the HTTP header; hence the web server knows them and can forward them to the next tier/server. NTML is still in use today; for example: if you have multiple domains that don’t trust each other and you need to access servers/resources in them, you can create the same account in all of them and as long as the passwords are synchronized, you can access the resources without a problem. It’s a common trick to bypass the non-trusting domain environment but it’s also a security concern…
Back to SSAS, the engine uses “UserName()” function to retrieve the currently-connected Window user identity. The function returns a string with the logon and can be used in configuring the SSAS security (not covered in this article). For example: SSAS can be configured that user “Ivan” has access to “East and Central US”; the two geo members are assigned to “Ivan” so when he logs in, the data is automatically filtered for only these two regions. Another way of describing this is that the MDX is adjusted to filter out all but these two regions. Configuring access to individual user can quickly become a cumbersome, labor-intensive, and error-prone task. Hence SSAS supports the concept of “Roles”, which is basically a “group” with one or more users. Security is configured for the group, and all its members inherit the security rights and restrictions for the given group. Users can also belong to multiple groups and the security is additive – i.e. most rights (vs. Windows with least rights) – a union vs. an intersection of multiple group rights). Configuring the security inside SSAS is usually done thru an extra “User” dimension, which is related to different fact tables/measure groups/. These relationships are then used to filter the current user and the data that will be returned. For example: Dimension “Users” contain a member “Ivan”; “Ivan” has access “East and Central” Geo dimension members. When “Ivan” accesses the cube, the engine will resolve/get his identity thru the “UserName()” function call and then it will find the Dimension “Users” corresponding member (i.e. using “StrToMeber” function) and then it will filter the data for only the two Geo Dimension members.
SSAS also supports “EffectiveUserName” connection string option. This option allows for a multi-hop scenario where the service account has elevated privileges and can impersonate the user. SharePoint and PerformancePoint are good examples of using this approach; in my experience, it’s very difficult to debug and support these types of installations! The “Roles” connection string option is another good trick to provide some flexibility in integrating applications; the SSAS client (i.e. usually the web/mid-tier server) can embed the roles that are needed for the current user, and if the connecting account has elevated rights, it can include this option in the connection string, so when the connection is opened, SSAS will ONLY apply the rights given the provided Roles! Both of these two options are commonly used to integrate custom security web apps with SSAS.
There is much more on the topic but it’s well covered in the whitepapers published on the Web.
2) Anonymous Authentication
This is the simplest authentication method for SSAS. The engine is told that NO authentication and authorization (A&A) should be performed so anyone is allowed to access any data. The SSAS properties has a configuration option ‘Security\RequireClientAuthentication’ that can dis/enable the authentication service level. Alternatively, a role can be configured and everyone/guest is make a member of it; the role is then given full access to the cube (i.e. not administrator rights are implied).
3) Custom/Internet Authentication
This type is commonly requested since the industry trend is to push data to the Internet. The basic algorithm for authenticating a user, is to retrieve the credentials in clear text (note: commonly over Securied Socket Layer – aka SSL used in HTTPS comm channel security), and then for a custom algorithm to do a look-up against a relational database table or a flat file. Example: “select * from tblUsers where UserName = ‘Ivan’ and Password = ‘my password’”. If a record is return then the user is authenticated/valid. It’s a simple implementation and works very reliably. It’s worth mentioning that I’ve seen a lot of enterprise systems where user inputs are not checked and SQL-injections attacks are possible (for example: if the user name is “’Ivan’--“ the extra dashes will comment out the password check in TSQL and the password check will be skipped!). This issue has been drastically eliminated over the years. The password should be also stored as “salted hash” instead clear-text; this will prevent internal employees to access the users passwords in clear text!
Back to SSAS, the engine does NOT support this form of authentication or provided an elegant way to implement it in SSAS. The most common workaround is the use of another connection string option: “CustomData”, which basically allows the client to pass some text data into the SSAS session it’s about to open. An example: Connection.Open(“Data Source=SsasServer; Initial Catalog=AW; CustomData=Ivan”) – when the connection is established, the MDX “with member [User] as CustomData() select [User] on 0 from [MyCube]” will return “Ivan”. The “CustomData()” call returns the information that was passed from outside SSAS and can be used while configuring the SSAS security.
A step further is to use SSAS stored procedures (SP) to extend the functionality of SSAS. A popular common approach is to use such an SP, which in turns calles into a SQL database and passes either the UserName() or CustomData() outputs. The SQL calls retrieves a set of members from a table and returns it back to SSAS, which in turn uses the “StrToSet()” function to resolve the output set in Dimension Members and use them for filtering. This is a very effective way to provide DYNAMIC security in SSAS; a change in the SQL table records will immediately result in different security inside SSAS! There are a couple of caveats to watch out for with this approach. First, the security stops working one in a while; luckily the returned SSAS data is empty so there is no security breach. The setup needs to be reset to get it going. Second, if your are an SSAS administrator, the whole model is bypassed, which makes it difficult to test and troubleshoot. Also, watch out which stored procedure is being called: SSAS or SQL, use proper naming of the procs!
4) External SSAS Authentication
Enterprise Software Solution invented another way of doing SSAS security. I haven’t seen anyone in the industry doing this approach. Basically, the incoming MDX requests are intercepted and modify to include an extra filter, which limits the sub-cube space for the current user and then performs the request. The SSAS server does not know anything about security. The advantages of this approach is that the security is dynamic, there are no disruptions of service mentioned in 3) above, and the solution is easy to support and debug (i.e. it’s not a black box running MS compiled DLLs). Another major advantage is that Caching is OPTIMAL with the server since all the request are coming from the same user and there is no reason for creating multiple caches!
Our product, “BI Portal”, is a solution, which supports all of the above security authentication models. All the components, needed for the various models, are included. The “BI Portal” also takes it a step further with its Single-Sign-On feature for SSRS, SSAS and SQL. The user needs to login only once, and then his/her security identity flows thru SSRS, SSAS, and SQL. Please, see my “SSAS Security” blog under the “BI Portal” category.