Granting Access to Data Sources
Typically, most users of Microsoft SQL Server Analysis Services do not require access to the data sources that underlie an Analysis Services project. Users ordinarily just query the data within Analysis Services.
However, in the context of data mining, such as performing predictions based on a mining model, a user has to join the learned data of a mining model with user-provided data. To connect to the data source that contains the user-provided data, the user uses a Data Mining Extensions (DMX) query that contains either the OPENQUERY (DMX) and OPENROWSET (DMX) clause.
Important
For security reasons, the submission of DMX queries by using an open connection string in the OPENROWSET clause is disabled. For more information, see Securing the Data Sources Used by Analysis Services.
To execute a DMX query that connects to a data source, the user must have access to the data source object within the Analysis Services database. By default, only members of the Analysis Services server role or members of the Administrator role have access to data source objects. This means that a user cannot access a data source object unless a member of the Analysis Services server role or the Administrator role gives the database role to which the user belongs access to the data source.
Setting Read Permissions to a Data Source
A database role can be granted either no access permissions on a data source object or read permissions.
Important
Permission on a data source object should be restricted. The actual permissions that users have in the underlying data source should be limited.
To grant a user read permissions to a database role, the user must be a member of the Analysis Services server role or a member of an Analysis Services database role that has Full Control (Administrator) permissions.
To give a database role read permission to a data source
In SQL Server Management Studio, connect to the instance of Analysis Services, expand Roles for the appropriate database in Object explorer, and then click a database role (or create a new database role).
Click Data Source Access in the Select a Page pane, locate the data source object in the Data Source list, and then select the Read in the Access list for the data source.
Working With the Connection String Used by a Data Source Object
The data source object contains the connection string that is used to connect to the underlying data source. This connection string can specify one of the following:
Specify a user name and password
If the connection string that a data source object uses specifies a user name and password, you may want to create multiple data source objects, each with different user accounts. Creating multiple data source objects lets users access certain data source objects and prevents those users from accessing other data source objects. These other data source objects can be used by Analysis Services itself for processing objects, such as cubes and mining models.
Specify Windows Authentication
If the connection string that a data source object uses specifies Windows Authentication, Analysis Services must be able to impersonate the client. If the data source is on the same computer, Analysis Services will be able to impersonate the client. If the data source is on a remote computer, the two computers must be trusted for impersonation by using Microsoft Kerberos authentication, or the query will typically fail. If the client does not allow for impersonation (through the Impersonation Level property in OLE DB and other client components), Analysis Services will try to make an anonymous connection to the underlying data source (most data sources do not accept anonymous connections).