Configure Service Accounts (Analysis Services)
The logon account under which Analysis Services runs is always specified for the first time during setup, and then managed later using SQL Server Configuration Manager or the Services console application. When configuring a service account during installation, follow the guidelines in this topic: Configure Windows Service Accounts and Permissions.
This topic includes the following sections:
Understanding the Execution Context of Analysis Services Operations
Granting Network Resource Access Rights to the Service Account
Analysis Services executes some tasks in the security context of the Windows user account that is used to start Analysis Services, in other words the logon account, and executes other tasks in the security context of the user who is requesting the task.
For tasks that Analysis Services performs in the context of its logon account, the logon account must have sufficient permissions and access rights to perform the tasks. At the same time, the logon account for Analysis Services should not be granted more rights than are required for the tasks to be performed, because excessive rights could pose potential security issues.
If your domain is configured to use Kerberos, additional logon account configuration is required. Specifically, you must register a Service Principal Name (SPN) for the Analysis Services service on the server. For more information, see How to configure SQL Server 2008 Analysis Services and SQL Server 2005 Analysis Services to use Kerberos authentication.
Each instance of Analysis Services runs as Windows service, Msmdsrv.exe, in the security context of a defined logon account.
-
The service name of default instance of Analysis Services is MSSQLServerOLAPService.
-
The service name of each named instance of Analysis Services is MSOLAP$InstanceName.
Each instance of Analysis Services is completely independent, with its own independent executables, security model, databases, cubes, and mining models.
Note
|
|---|
|
If multiple instances of Analysis Services are installed, Setup also installs a redirector service, which is integrated with the SQL Server Browser service. The redirector service is responsible for directing clients to the appropriate named instance of Analysis Services. The SQL Server Browser service always runs in the security context of the Local Service account, a limited user account used by Windows for services that do not access resources outside the local computer. |
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.
Accessing Data Sources
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.
Accessing Remote Analysis Services Instances
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.
If you need to use the logon account of Analysis Services either to access a backup file or to work with the query log table in a specific SQL Server database, there are additional rights that you must grant to the logon account.
Granting the Rights Required to Write to or Read from a Backup File
The Analysis Services logon account requires the following permissions to access a backup file:
-
To perform a database backup, the Analysis Services logon account must have file level permissions to create the backup file in the specified location.
-
To perform a database restoration, the Analysis Services logon account must have read permissions to the backup file.
Additionally, use the NTFS file system to help secure the storage location for the backup file and to help make sure that unauthorized users do not have access to the backup file.
Granting the Rights Required to Write to and Create the Query Log Table
The Analysis Services logon account must have write permissions to the query log table in the designated SQL Server database. If this table does not already exist and needs to be created, the Analysis Services logon account must also have create table permissions within the designated SQL Server database.
|
Note
|
|---|
|
Security on the query log table is the responsibility of the SQL Server database administrator. |
Granting the Rights Required to Write to the Writeback Table
If writeback is enabled within any dimension or cube, the Analysis Services logon account must have write permissions to the writeback table in the designated SQL Server database. If this table does not already exist and needs to be created, the Analysis Services logon account must also have create table permissions within the designated SQL Server database.
|
Note
|
|---|
|
Security on the writeback table is the responsibility of the SQL Server database administrator. |
