:::: MENU ::::

Security Considerations for SQL Server Analysis Services

  • May 13 / 2009
  • 0
Analysis Services SSAS, dbDigger, Security and Permissions

Security Considerations for SQL Server Analysis Services

Administrator Security

When Analysis Services is installed, the setup program creates the OLAP Administrators local group on the Analysis Services computer and adds the user account of the person installing Analysis Services to this group. All members of the local Administrators group are automatically members of the OLAP Administrators group, regardless of whether they are explicitly added to the OLAP Administrators group. The OLAP Administrators group is granted the following rights on the Analysis Services computer:

  • Full control permission to the Server Connection
  • Write permissions through the MsOLAPRepository$ hidden share (the ..Microsoft Analysis ServicesBin folder). The MsOLAPRepository$ hidden share is created during setup. Analysis Services uses the hidden share when reads from or writes to the repository when it is stored in an Access database (this is the default location and store for the repository).
  • Full control rights to the Bin and Data folders under the ..Microsoft Analysis Services directory. This includes full control rights to the repository files, Msmdrep.mdb and Msmdrep.ldb. With clustering, if the Data folder is on a different computer than the computer on which Analysis Services is running, you must ensure that the members of the OLAP Administrators group on the Analysis Services computer have full control rights to this Data folder. This includes the account under which Analysis Services is running. Generally this is accomplished through the use of a domain group. There is only one level of administrative access to Analysis Services. A member of the OLAP Administrators group has complete administrative access to Analysis Services objects, full read access to all cubes and dimensions, and full write access to all write-enabled cubes and dimensions (regardless of any contrary role definitions). A domain or local user that is not a member of the OLAP Administrators group can perform no administrative tasks and has read or write access to the extent permitted based on dimension-level or cell-level security.

End-User Security

End-user security in Analysis Services is based on Windows user accounts and groups. Before you begin configuring end-user security in Analysis Services, you must first create the user accounts and groups within Active Directory. A frequently asked question is whether Analysis Services supports other kinds of authentication. The answer is Yes and No. Yes, it can support other types using HTTP access and IIS (IIS 6.0 includes some new authentication options). However, all these authentication types must ultimately map to a Windows user account in the general sense: including domain accounts, local accounts, the guest account (if enabled), or the built-in NT AUTHORITYANONYMOUS LOGON account. Therefore, no, Analysis Services does not support SQL standard security or any similar technology where the authentication is not based on Windows user accounts.

Security Roles

After you have created the appropriate Windows user and group accounts, you create security roles within Analysis Services that contain Windows user and group accounts, and define the access each role has to Analysis Services data. You can use database roles, cube roles, and mining model roles.

  • A database role can be assigned to multiple cubes or mining models in a database. Database roles provide default permissions for cube or mining model roles. By default, a database role specifies only read access and does not limit the dimension members or cube cells visible to end users. You can, how ever, specify read/write access and limit dimension members that are visible and updatable.
  • A cube role applies to a single cube. Defaults in a cube role are derived from the database role of the same name, but some of these defaults can be overridden in the cube role. In addition to the database role features of specifying read/write access and limiting dimension members that are visible and updatable, a cube role also enables you to specify cell-level security. Cell-level security has less memory overhead than dimension security.
  • A mining role applies to a single mining model. Defaults in a mining role are derived from the database role of the same name, but some of these defaults can be overridden in the mining role. A domain user or group can be a member of multiple roles within Analysis Services. In this case, the effective rights of the user are the combined access characteristics specified in these roles.

Dimension-, Cell-, or Application-Level Security

When you use dimension-level security to limit the dimension members that are visible or updateable, Analysis Services must create a replica dimension in memory when a user connects which reflects the dimension members that user is permitted to see.

A user is not in any role: no access is permitted to the dimension at all.

This is actually an interesting case. If a user is allowed access to a cube (based on the user’s membership in the roles), the user can see the cube as a valid cube, capable of being queried. However, when dimension security is applied, the allowed set is empty in one or more dimensions. This places Analysis Services in a difficult position because Analysis Services cannot tell the user where access is being denied (because that is a security violation in and of itself). As a result, Analysis Services forcibly disconnects the session with the user – and the user receives the purposely ambiguous error message “The connection to the server is lost.”

Consult us to explore the Databases. Contact us