Configure Service Accounts (Analysis Services)
Analysis Services executes as a Windows service, running under a Windows account. The account can be a domain user, managed service account, a built-in system account (like NetworkService), or a local account.
The service account is always specified for the first time during setup. When configuring a service account during installation, follow the guidelines in this topic: Setting Up Windows Service Accounts.
To change the service account later, always use SQL Server Configuration Manager. Using SQL Server Configuration Manager ensures that file and registry permissions are updated with the proper service account information. If you use other tools, like the Services console application, you’ll bypass this step.
This topic includes the following sections:
Account recommendations for specific deployments
Account permissions
Understanding the Execution Context of Analysis Services Operations
Granting Network Resource Access Rights to the Service Account
Granting Additional Rights for Specific Tasks
Account recommendations
This section lists all possible choices and explains the conditions under which you might choose a particular account.
<SINGLE SERVER, TCP IP, CLIENT SERVER ACCESS>
<SINGLE SERVER, Local access only – custom app using local cubes>
<FAILOVER CLUSTER>
<VM ROLE IN WINDOWS AZURE>
<POWERPIVOT—see other topic>
Domain user account
Unless you have a compelling reason to do otherwise, you should always use a Windows domain user account to run the Analysis Services service. A domain account that you create is less likely to be used for other purposes.The account should be used exclusively for Analysis Services so that you can audit logins.
A standalone Analysis Services instance. This is the most common installation scenario.
An instance of Analysis Services that runs as part of a PowerPivot for SharePoint installation. If you are installing PowerPivot for SharePoint, SQL Server Setup enforces the use of a domain account for the Analysis Services instance included in the PowerPivot for SharePoint deployment.
Analysis Services runs as part of a failover cluster. In a cluster, all server instances must run under the same account. Domain user accounts are the only accounts that can be used by services that run on different physical servers.
Managed service account
(Windows 7 and later, Windows 2008 R2 and later, domain functional level at Windows 2008 R2 or higher) - A managed service account is a domain account configured by a domain administrator, assigned to a service instance running on a specific computer. Password updates and SPN registration are performed by the domain controller. Eliminating password and SPN overhead is a primary reason for using a managed service account. However, if your domain includes older servers or workstations, your domain will be running at a lower functional level and a managed service account will not be an option. In this case, use a domain user account instead.After a managed service account is created in Active Directory, an Analysis Services administrator can specify it as a service account for Analysis Services. Because the account is used solely by a single service, it satisfies service isolation criteria for defense in depth. It also functions as an SPN, eliminating the need to register an SPN for the service instance.
A managed service account uses the following name format: <INSERT>
If you uninstall a service or migrate an installation to new hardware, the managed service account will need to recreated.
Virtual account
(Windows 7 and later, Windows 2008 R2 and later, domain functional level Windows 2008 R2 or higher) - Virtual accounts in Windows Server 2008 R2 and Windows 7 are managed local accounts that can use a computer's credentials to access network resources. Similar to a managed service account, you don’t have to specify a password.A virtual account uses the following format: NT Service\MSOLAPService on the local computer, or <ComputerName>$MSOLAPService if you are specifying the account on a different machine. For example, if you are granting SQL Server database permissions to the Analysis Services service account, you would specify the account as <ComputerName>$MSOLAPService on the remote SQL Server instance.
SPN registration is required. If the domain functional level is not
Virtual account (cluster)
<TBD>Network Service
<TBD>Local System
<TBD>Although it’s a common practice on older deployments to run Analysis Services as LocalSystem, you should always choose another option, such as a managed service account, a domain user account, or NetworkService. LocalSystem provides a superset of permissions that are unnecessary for Analysis Services operations, and introduces unnecessary security risks.
Local Service
<TBD>
Account permissions
The Analysis Services instance requires registry and file system permissions that grant read, write, and execute operations on program, data, and configuration files.
The Analysis Services instance gets its permissions from a local security group, SQLServerMSASUser$<servername>$<instancename> that is created by SQL Server Setup. SQL Server Setup provisions the security group with the permissions required to perform server operations. For example, if you view the security permissions on the \MSAS11.MSSQLSERVER\OLAP\BIN directory, you will see that the security group (not the service account) is the permission holder on that directory.
The group contains one member only: the Security Identifier (SID) for an Analysis Services instance. SQL Server Setup creates the SID for a specific Analysis Services instance and then adds it to the local security group.
<DOES THE ACCOUNT IMPERSONATE>
<DOES THE ACCOUNT DELEGATE>
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 SPN registration for an Analysis Services instance.
Understanding the Execution Context of Analysis Services Operations
Analysis Services executes some tasks in the security context of the service 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.
Operations performed in the security context of the service account
<LIST OPERATIONS HERE>
Operations performed in the security context of another user
<LIST OPERATIONS HERE>
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.
[!NOT]
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.
Granting Network Resource Access Rights to the Service Account
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.
Granting Additional Rights for Specific Tasks
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.
[!NOT]
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.
[!NOT]
Security on the writeback table is the responsibility of the SQL Server database administrator.