Security Overview (Data Mining)
The process of configuring an instance of Microsoft SQL Server Analysis Services for data mining occurs at multiple levels.
The instance where you create, process, and query data mining models must be running in multidimensional mode.
You must secure the instance of Analysis Services to make sure that only authorized users have read or read/write permissions to selected dimensions, mining models, and data sources.
This includes securing the Analysis Services computer, the Windows operating system on the Analysis Services computer, and the Analysis Services instance.
You must also secure underlying data sources to prevent unauthorized users from maliciously compromising sensitive business information. If you use drillthrough in any mining models, be sure that appropriate security is applied to columns containing sensitive data.
You must configure appropriate security for users or accounts that need to build or process data mining models.
For more information about overall process and requirements for securing an instance of Analysis Services see Analysis Services Instance Management.
The following section describes security considerations that are specific to data mining.
Special Security Considerations for Data Mining
In general, data mining models are stored within Analysis Services databases using the same security model that is provided for multidimensional solutions. However, there are some key differences to understand in advance:
Building or processing models: For data mining, you need a different set of permissions to build and process models than you need to view or query the models.
Creating predictions: Making predictions against a model is a kind of query and does not require administrative permissions.
Processing of OLAP models: Different permissions are required for processing OLAP mining model than for processing data minigng models based entirely on relational data sources.
This section describes these considerations in detail.
Configuring Access to Analysis Services for Analysts
You must select an appropriate logon account for Analysis Services and specify the permissions for this account. You must make sure that the Analysis Services logon account has only those permissions that are necessary to perform necessary tasks, including appropriate permissions to the underlying data sources.
When you set up and define authorized users for an instance of Analysis Services, you need to determine which users should also have permission to administer specific database objects, which users can view the definition of objects or browse the models, and which users are able to access data sources directly.
Building or Processing Models
To enable an analyst or developer to create and test data mining models, you must give that analyst or developer administrative permissions on the database where the mining models are stored. As a consequence, the data mining analyst or developer can potentially create or delete other objects that are not related to data mining, including data mining objects that were created and are being used by other analysts or developers, or OLAP objects that are not included in the data mining solution.
Accordingly, when you create a solution for data mining, you must balance the needs of the analyst or developer to develop, test and tune models, against the needs of other users, and take measures to protect existing database objects.
If you have multiple analysts developing models, one possible approach is to create a separate instance dedicated to data mining, within which each admin/analyst can create the required Analysis Services databases.
Although the creation of models requires the highest level of permissions, you can control the user's access to data mining models for other operations, such as processing, browsing, or querying, by using role-based security. When you create a role, you set permissions that are specific to data mining objects. Any user who is a member of a role automatically has all permissions associated with that role.
Processing of cube models: The analyst, user, or administrator who processes a data mining model based on a cube must also have processing permissions on the cube.
If the Impersonation option for structure and model processing is set to Default, Analysis Services uses different credentials, depending on how the data source is accessed:
If accessed for processing, the service account credentials will be used to access data.
If accessed for querying, the current user credentials will be used, if possible.
Processing of the model can fail in certain cases, if the Analysis Services startup account doesn’t have full permission on the OLAP cube.
For example, if an administrator change the security policy and changes the configuration file so that the startup account for Analysis Services is no longer a server admin, it would not affect processing of other multidimensional objects, including cubes and dimensions. However, processing for models based on the cube fail with an error like the following:
Internal error: The operation terminated unsuccessfully. OLE DB error: OLE DB or ODBC error: The UseTransaction and MasterTransaction request properties are reserved for database administrators.. Errors in the OLAP storage engine: An error occurred while processing the 'Internet ~1 ~MG' partition of the 'Internet ~1 ~MG' measure group for the 'Customer Clusters ~MC' cube from the Adventure Works DW 2008R2 database. Server: The operation has been cancelled.
To summarize, regardless of the value of the property, DataSourceImpersonationInfo, the credentials of the current user will be used for out-of-line bindings, ROLAP queries, local cubes, and for use of data mining models.
To avoid problems with processing of cube models, use one of the following options:
Ensure that the service account is a database administrator for Analysis Services
Ensure that the property <ServiceAccountIsServerAdmin> Property is set to 1 (True).
This property is exposed only in the msmdsrv.ini file. The default location for the msmdsrv.ini file is C:\Program Files\Microsoft SQL Server\MSAS10_50.SQL2008R2\OLAP\Config).
Take a copy of the file, and do not make additional changes.
Add a role for processing data mining models.
Create a new role in the database.
Grant Full Control to this role.
Add the Service Account as a member of this role.
Querying Models
Analysis Services allows native access to all objects as a web service through XML for Analysis (XMLA). Therefore you can create and send prediction queries to data mining models via a web service.
Managing Access to Data Sources
Additionally, data mining models often reference data sources that contain sensitive information. If the mining structure and mining model has been configured to allow either analysts or end users to drill through from the model to the data in the structure, you must take precautions to mask sensitive information, or to limit the users who have access to the underlying data.
If you use Integration Services packages to clean data, to update mining models, or to make predictions, you must ensure that the Integration Services service has the appropriate permissions on the database where the model is stored, and appropriate permissions on the source data.
Related Resources
Managing Analysis Services using SQL Server Management Studio
Grant User Permissions on an Analysis Services Multidimensional Database