User Access Security Architecture
Microsoft SQL Server Analysis Services relies on Microsoft Windows to authenticate users. By default, only authenticated users who have rights within Analysis Services can establish a connection to Analysis Services. After a user connects to Analysis Services, the permissions that user has within Analysis Services are determined by the rights that are assigned to the Analysis Services roles to which that user belongs, either directly or through membership in a Windows role.
Analysis Services contains a single fixed server role, which grants its members permission to perform any task within the entire instance.
Users who are not members of the fixed server role can be made members of one or more database roles. Each database role has a customized set of permissions to let users access data and perform tasks within a particular database.
A database role can be granted administrator permissions, process object permissions, view object metadata permissions, and permissions to view or modify data at multiple levels within each Analysis Services database.
Members of a database role that has administrator permissions can view or update all data within the database. Members of other database roles can only view or update the data objects to which they have specifically been granted such permissions.
Permissions within an Analysis Services database are granted initially at the database level. If a database role has permissions at the database level, the role must be granted specific permissions for each object within the database. Objects to which a role can be granted permissions include database and cube dimensions, individual dimension members, cubes, individual cells within a cube, mining structures, mining models, data sources, and stored procedures.
In addition to these security architecture components, Analysis Services encrypts all client/server communication to reduce the risk that unauthorized users can gain access to unauthorized information. Finally, features of Analysis Services that may compromise security if they are configured inappropriately or used in an inappropriate environment are disabled by default. For example, you can let users connect to Analysis Services without authentication or you can accept authentication that is submitted in clear text. However, because doing this may compromise security if performed incorrectly, starting these types of features requires that you modify default settings.
Windows Authentication
Access to Analysis Services is based on Microsoft Windows Authentication. This authentication model requires that all users be authenticated by the Windows operating system before they can access data that is stored in Analysis Services and before they can administer Analysis Services objects. Having the operating system perform authentication enables Analysis Services to take advantage of the security features of Windows, including secure validation and encryption of passwords, auditing, password expiration, minimum password length, and account lockout after multiple invalid logon requests.
Note
If the instance of Analysis Services is configured to permit anonymous access, Windows does not authenticate the user.
Windows Authentication and Analysis Services work together in the following manner:
When a user logs on to the Windows network, a Windows domain controller validates the user's user name and password, thereby establishing the user's network authentication credentials.
Later, when the user tries to connect to Analysis Services, Analysis Services validates the user's network authentication credentials with a Windows domain controller.
Authorization
After authenticating a user, Analysis Services next determines whether the user has permissions to view data, update data, view metadata, or perform administrative tasks. If the user, or a group of which the user is a member, has some type of permissions within the instance of Analysis Services, Analysis Services lets the user connect. By default, Analysis Services does not let a user connect if that user does not have some type of permissions within the instance of Analysis Services.
However, authorization does not stop after a user successfully connects to Analysis Services. Authorization continues as the user works within Analysis Services, such as when the user executes server stored procedures, Data Mining Extensions (DMX) statements, Multidimensional Expressions (MDX) queries, or Analysis Management Objects (AMO) commands. Every time that Analysis Services must perform an action or access an object, it verifies the authority of the user to access the objects or perform the command. If the user does not have the appropriate permissions, Analysis Services returns a permissions error.
Server and Database Roles
Analysis Services has two types of roles: the server role and database roles. Following is a brief description of the difference between these two roles:
There is only one server role, and members of this role have complete administrator rights within the instance of Analysis Services.
Note
Members of the Administrators local group on the local computer are automatically members of the server role in an instance of Analysis Services.
There can be multiple database roles. Members of the server role create these database roles within each database, grant administrative or user permissions to these database roles such as read or read/write permission to cubes, dimensions, cells, mining structures, mining models, and data source objects), and then add Windows users and groups to these database roles.
Important
Role permissions are additive. Permissions that a Windows user or group has through one database role are added to the permissions that the same Windows user or group has through other database roles. If one role denies a user or group permission to perform certain tasks or view certain data, but another role grants this permission to that user or group, the user or group will have permission to perform the task or view the data.
Administrator Rights
Although members of the server role automatically have complete administrator rights, members of a database role do not. A database role can be granted Full Control, otherwise known as Administrator, rights or a more limited set of administrator rights from the following list:
Process the database
Read database metadata
Process one or more dimensions
Read the definition of one of more dimensions
Process one or more cubes
Read the definition of one or more cubes
Process one or more mining structures
Process one or more mining models
Read the definition of one or more mining structures
Read the definition of one or more mining models
Read the definition of one or more data sources
Only members of the server role and members of a database role that have Full Control can read Analysis Services data without additional permissions. Other users can only read Analysis Services data if their database role expressly grants read or read/write permissions to the data objects in Analysis Services, such as dimensions, cubes, cells, and mining models).
Dimension-Level Security
A database role can specify whether its members have permission to view or update dimension members in specified database dimensions. Moreover, within each dimension to which a database role has been granted rights, the role can be granted permission to view or update specific dimension members only instead of all dimension members. If a database role is not granted permissions to view or update a particular dimension and some or all the dimension's members, members of the database role have no permission to view the dimension or any of its members.
Note
Dimension permissions that are granted to a database role apply to the cube dimensions based on the database dimension, unless different permissions are explicitly granted within the cube that uses the database dimension.
For More Information: Grant permissions on a dimension (Analysis Services) and Grant custom access to dimension data (Analysis Services).
Cube-Level Security
A database role can specify whether its members have read or read/write permission to one or more cubes in a database. If a database role is not granted permissions to read or read/write at least one cube, members of the database role have no permission to view any cubes in the database, despite any rights those members may have through the role to view dimension members.
For More Information: Grant cube or model permissions (Analysis Services).
Cell-Level Security
A database role can specify whether its members have read, read contingent, or read/write permissions on some or all cells within a cube. If a database role is not granted permissions on cells within a cube, members of the database role have no permission to view any cube data. If a database role is denied permission to view certain dimensions based on dimension security, cell-level security cannot expand the rights of the database role members to include cell members from that dimension. On the other hand, if a database role is granted permission to view members of a dimension, cell-level security can be used to limit the cell members from the dimension that the database role members can view.
For More Information: Grant custom access to cell data (Analysis Services).
Mining Structure, Mining Model, and Data Source Security
A database role can specify whether its members have read or read/write permissions to mining structures and mining models. A database role can also be granted permission to drill through to the source data, and permission to browse on one or more mining models. Finally, a database role can be granted read/write permissions on data source objects. This permission lets the role reference that data source in an OPENQUERY clause and use the connection string that is defined in the data source object.
For More Information: Grant permissions on data mining structures and models (Analysis Services) and Grant permissions on a data source object (Analysis Services).
Stored Procedure Security
Stored procedures in Analysis Services are external routines, written in a Microsoft .NET programming language, that extend the capabilities of Analysis Services. Stored procedures let the developer take advantage of cross-language integration, exception handling, versioning support, deployment support, and debugging support.
Any user can call a stored procedure. Depending on how the stored procedure was configured, the procedure can run either in the context of the user calling the procedure or in the context of an anonymous user. Because an anonymous user has no security context, use this capability together with configuring the instance of Analysis Services to permit anonymous access.
After the user calls a stored procedure but before Analysis Services runs the stored procedure, Analysis Services evaluates the actions within the stored procedure. Analysis Services evaluates the actions in a stored procedure based on the intersection of the permissions granted to the user and the permission set used to run the procedure. If the stored procedure contains any action that cannot be performed by the database role for the user, that action will not be performed.
Following are the permission sets that are used to run stored procedures:
**Safe **With the Safe permission set, a stored procedure cannot access the protected resources in the Microsoft .NET Framework. This permission set only allows for computations. This is the safest permission set; information does not leak outside Analysis Services, permissions cannot be elevated, and the risk of data tampering attacks is minimized.
**External Access **With the External Access permission set, a stored procedure can access external resources by using managed code. Setting a stored procedure to this permission set will not cause programming errors that could lead to server instability. However, this permission set may result in information leaking outside the server, and the possibility of an elevation in permission and data tampering attacks.
**Unrestricted **With the Unrestricted permission set, a stored procedure can access external resources by using any code. With this permission set, there are no security or reliability guarantees for stored procedures.
Encryption
By default, Analysis Services, requires that all communication between clients and the instance of Analysis Services be encrypted.
Off By Default
An instance of Analysis Services is designed to be secure by default. Therefore, features that might compromise security are disabled by default. The following features are disabled by default, and must specifically be enabled if you want to use them:
HTTP Connectivity
Trace
Stored Procedures
Remote Partitions
Linked Objects (To)
Linked Objects (From)
Data Mining Aggregator
Client Protection Level
Required Client Authentication
Active Directory Integration
Ad Hoc OpenRowset Queries
8.0 Client Connectivity
Crash Reports