Granting Network Resource Access Rights to the Service Account

When Microsoft SQL Server Analysis Services needs to access network resources, it can access these resources in one of the following ways:

  • In the security context of its logon account.   If Analysis Services accesses these network resources in the security context of its logon account, you must grant this logon account appropriate rights on the remote resource.

  • In the security context of the name and password that are specified in the connection string used to connect to the network resource.   For example, if the data source is a Microsoft SQL Server database and SQL Server is running in mixed mode, Analysis Services can include the appropriate SQL Server logon account and password in the connection string instead of using the Analysis Services logon account. Similarly, if the data source is an Oracle database, Analysis Services can access that database by using a name and password that have appropriate permissions in the Oracle database.

When accessing a network resource by using Analysis Services, the primary resources that you need to access are data sources for Analysis Services objects and other instances of Analysis Services.

When you access data sources, you need to consider the permissions that are required to access those sources, and identify which account has those required permissions. When you have identified the account that you need to access the data sources, you then must decide whether the Analysis Services logon account is adequate or whether you need to connect by using a connection string. If you need to use a connection string, Analysis Services will need to secure the information that is contained in that string.

Setting Permissions for Connecting to Data Sources

The account that is used by Analysis Services to connect to a data source must have at least read permissions on the source data in order for Analysis Services to process a MOLAP or HOLAP partition. If ROLAP is used as the storage mode, the account must also have write access to the source data to process the ROLAP partitions (that is, to store aggregations).

Encrypting Connection Strings

Analysis Services encrypts and stores the connection strings it uses to connect to each of its data sources. If the connection to a data source requires a user name and password, you can have Analysis Services store the name and password with the connection string, or prompt you for the name and password each time a connection to the data source is required. Having Analysis Services prompt you for user information means that this information does not have to be stored and encrypted. However, if you store this information in the connection string, this information does need to be encrypted and secured.

To encrypt and secure the connection string information, Analysis Services uses the Data Protection API. Analysis Services uses a separate encryption key to encrypt connection string information for each Analysis Services database. Analysis Services creates this key when you create a database, and encrypts connection string information based on the Analysis Services logon account. When Analysis Services starts, the encrypted key for each database is read, decrypted, and stored. Analysis Services then uses the appropriate decrypted key to decrypt the data source connection string information when Analysis Services needs to connect to a data source.

Analysis Services can only access remote instances in the security context of its logon account. In order to access linked objects on a remote instance of Analysis Services, the logon account must have permission to read the appropriate objects on the remote instance, such as Read access to certain dimensions.

Community Additions