Securing the Data Sources Used by Analysis Services
If unauthorized users obtain access to the data sources from which Microsoft SQL Server Analysis Services loads its data, those unauthorized users can access the same information that is stored within the instance of Analysis Services. You should limit access to these data sources. To browse cubes and dimensions, Analysis Services users do not need permissions on these data sources. However, Analysis Services users do need permissions to connect to underlying data sources to perform certain tasks, such as using Data Mining Expressions (DMX) to perform certain data mining tasks.
Securing Connections to Underlying Data Sources
Analysis Services connects to the underlying data sources to:
Process data into Analysis Services.
Resolve queries when ROLAP or HOLAP is used.
Record writeback entries.
Execute Multidimensional Expressions (MDX) drillthrough queries.
Analysis Services performs each of these tasks through a DataSource object. The security account that the DataSource object uses to access these data sources is either the user's own security credentials, or the user name and password that are specified in the connection string that is stored in the DataSource object.
Warning
If Analysis Services connects to any one of its data sources by using the Analysis Services logon account, members of a database role that have Full Control permissions have access to that data source, regardless of whether that data source is used within that database.
The permissions that are required by the security account that is used by a DataSource object depend on the task that Analysis Services is to perform:
To connect to the data source, the security account that is used by the DataSource object must have at least read permission on the appropriate table in the data source. If ROLAP storage is used, the security account must also have permission to write aggregation data back to the data source.
If writeback is enabled, the security account that is used must also have permission to write to the writeback table.
Analysis Services encrypts and stores the connection string information that is used to connect to each data source. If the connection string specifies a specific security account instead of a trusted connection, you can choose to store the connection string with or without the password. If the password is not stored with the connection string, Analysis Services will prompt the user to provide an appropriate security account and password whenever Analysis Services tries to connect to that data source (such as during processing or when modifying a data source view).
During development, you may choose to store the connection string security account and password in the Analysis Services project. When you build the Analysis Services project, Business Intelligence Development Studio removes the security accounts and passwords from all data source connection strings, unless you choose to persist them in the output files. If you store the security account and password in the output files for an Analysis Services project, this connection string information is encrypted. If you do not persist this connection string information, and a trusted connection is not specified in the connection string, Analysis Services will prompt you to provide an appropriate security account and password during deployment processing.
Securing Connections Used by Data Mining Queries
For data mining queries that use the OPENQUERY clause in a DMX statement to connect to the underlying data source, Analysis Services connects through a DataSource object. The DataSource object either impersonates the client or uses the name and password that are specified in the connection string. By default, users have no permission on a DataSource object, and cannot query the underlying data source by using the OPENQUERY statement. To use the OPENQUERY clause in a DMX statement to query the underlying data source, users must be granted read/write permissions on the DataSource object. If users have read/write permissions on a DataSource object, and a specific account is specified in the connection string, it is a best practice to have the most restrictive permissions possible on the tables in the underlying data source for the specified account, namely read-only permissions to the specific tables that are accessed. For more information about DMX, see Data Mining Extensions (DMX) Data Definition Statements and Data Mining Extensions (DMX) Data Manipulation Statements.
By default, users cannot use the OPENROWSET clause in a DMX statement, and submit ad hoc queries against an underlying data source by using an ad-hoc connection string. You change the default setting for the DataMining \ AllowAdHocOpenRowsetQueries server configuration property to let users use the OPENROWSET clause. To access this property, right-click the instance of Analysis Services, click Properties, and then locate this property on the Security page. If you do this, you will not be able to limit the data sources to which data mining model users can query. For more information, see Granting Access to Mining Structures and Mining Models and Granting Access to Data Sources.