Export (0) Print
Expand All
0 out of 3 rated this helpful - Rate this topic

Configure Service Accounts (Analysis Services)

Account provisioning is documented in Configure Windows Service Accounts and Permissions, a topic that provides comprehensive service account information for all SQL Server services, including Analysis Services. Refer to it to learn about valid account types, assigned Windows privileges, file system permissions, registry permissions, and more.

In this topic, you can learn more about how the Analysis Services service account is used, in particular which workloads impose additional permission requirements on the service account, and how Analysis Services service account provisioning diverges from that of other SQL Server features. This topic contains the following sections:

If your domain is configured to use Kerberos, consider registering a Service Principal Name (SPN) for the Analysis Services instance and service account. By performing this task, you enable pass-through authentication in double-hop scenarios. For more information, see Configure Analysis Services for Kerberos constrained delegation.

Analysis Services executes some tasks in the security context of the service account that is used to start Analysis Services, and executes other tasks in the security context of the user who is requesting the task.

The following table describes additional permissions required to support tasks executing as the service account.

Server Operation

Additional Configuration

Remote access to external relational data sources

Processing refers to data retrieval from an external data source (usually a relational database), which is subsequently loaded into an Analysis Services database. One of the credential options for retrieving external data is to use the service account. This credential option works only if you create a database login for the service account and grant read permissions on the source database. See Set Impersonation Options (SSAS - Multidimensional) for more information about how the service account option is used for this task. Similarly, if ROLAP is used as the storage mode, the same impersonation options are available. In this case, the account must also have write access to the source data to process the ROLAP partitions (that is, to store aggregations).

DirectQuery

DirectQuery is a tabular feature used to query external datasets that are either too large to fit inside the tabular model or have other characteristics that make DirectQuery a better fit than the default in-memory storage option. One of the connection options available in DirectQuery mode is to use the service account. Once again, this option works only when the service account has a database login and read permissions on the target data source. See Set Impersonation Options (SSAS - Multidimensional) for more information about how the service account option is used for this task. Alternatively, the credentials of the current user can be used to retrieve data. In most cases this option entails a double-hop connection, so be sure to configure the service account for Kerberos constrained delegation so that the service account can delegate identities to a downstream server. For more information, see Configure Analysis Services for Kerberos constrained delegation.

Remote access to other SSAS instances

Remote partitions and referencing linked objects on other remote Analysis Services instances are both system capabilities requiring permissions on a remote computer or device. When a person creates and populates remote partitions, or sets up a linked object, that operation runs in the security context of the current user. If you subsequently automate these operations, Analysis Services will access remote instances in the security context of its service 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. Similarly, using remote partitions requires that the service account have administrative rights on the remote instance. Such permissions are granted on the remote Analysis Services instance, using roles that associate permitted operations with a specific object. See Create and Manage a Remote Partition (Analysis Services) for more information about remote partitions. See Grant Server Administrator Permissions (Analysis Services) for instructions about administrative rights.

Writeback

When enabled in client applications, writeback is a feature of multidimensional models that allows the creation of new data values during data analysis. If writeback is enabled within any dimension or cube, the Analysis Services service account must have write permissions to the writeback table in the source SQL Server relational database. If this table does not already exist and needs to be created, the Analysis Services service account must also have create table permissions within the designated SQL Server database.

Write to a query log table in a SQL Server relational database

You can enable query logging to collect usage data in a database table for subsequent analysis. The Analysis Services service 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. For more information, see Improve SQL Server Analysis Services Performance with the Usage Based Optimization Wizard (Blog) and Query Logging in Analysis Services (Blog).

SQL Server Setup assigns the Log on as a service (SeServiceLogonRight) privilege to the designated account. This is the only Windows privilege required by the Analysis Services service account.

As a verification step, you can run GPEDIT.msc | Local Computer Policy | Computer Configuration | Windows Settings | Security Settings | Local Policies | User Rights Assignments to check the permissions granted to accounts on the local system.

Note Note

Previous versions of Setup inadvertently added the Analysis Services service account to the Performance Log Users group. Although this defect has been fixed, existing installations might have this unnecessary group membership. Because the Analysis Services service account does not require membership in the Performance Log Users group, you can remove it from the group.

Note Note

See Configure Windows Service Accounts and Permissions for a list of permissions associated with each program folder.

See Configure HTTP Access to Analysis Services on Internet Information Services (IIS) 7.0 for file permission information related to IIS configuration and Analysis Services.

All permissions required for server operations, including file system permissions needed for loading and unloading databases from a designated data folder, are assigned by SQL Server Setup during installation, or by a SQL Server tool (such as SQL Server Configuration Manager or SQL Server Management Studio) when you update account or database folder locations. Maintaining the validity of permission assignments is one of the more important reasons for always using SQL Server tools to modify server configuration.

There are minor differences in how SQL Server Setup assigns file system permissions to Analysis Services, as compared to other services. Knowing how Analysis Services obtains file system permissions can be helpful when troubleshooting system configuration problems that arise during account updates, installation or upgrade, or migration.

The Analysis Services instance gets permissions from a local security group created by SQL Server Setup, either SQLServerMSASUser$MSSQLSERVER for the default instance, or SQLServerMSASUser$<servername>$<instancename> for a named instance. Setup provisions this security group with the permissions required to perform server operations. If you check the security permissions on the \MSAS12.MSSQLSERVER\OLAP\BIN directory, you will see that the security group (not the service account or its per-service SID) is the permission holder on that directory.

The security group contains one member only: the per-service Security Identifier (SID) of the Analysis Services instance startup account. Setup places the SID associated with the account into the local security group. The use of a security group, with SID membership, is a small but noticeable difference in how Analysis Services determines permission ownership.

Note Note

Every Windows account has an associated SID, but services can also have SIDs. The SID is created as a unique, permanent fixture of the account or service, which allows the more widely-visible name to be changed arbitrarily without affecting permission ownership. Because the SID is immutable, it is well-suited for use in ACLs. ACLs created during service installation can be used indefinitely, regardless of changes to the account name. As an added security measure, ACLs that specify permissions via a SID ensure that program executables and data folders are accessed only by a single instance of a service, even if other services run under the same account.

A per-service SID is created during setup for a specific service instance and does not change when the account name changes. For example, suppose you installed two instances of Analysis Services, a default instance and named instance, both running under the same Windows user account. Each service instance will have a unique per-service SID that is also distinct from the SID of the user account.

You can use the Service Control command line tool (sc.exe) to view each SID. The following examples show the SC syntax that returns the SID of a Windows user account named 'contoso\ssas-svc', a default instance, and a named instance called 'Tabular':

  • SC showsid contoso\ssas-svc

  • SC showsid MSSqlServerOlapService

  • SC showsid MSOlap$Tabular

If you ran these commands for valid accounts and service instances on your computer, you would notice unique SIDs for each one, even though both services run under the 'contoso\ssas-svc' account. Should you subsequently change the service account that runs either service, you would notice that the per-service SID stays the same.

Note Note

Never remove or modify a SID. To restore a per-service SID that was inadvertently deleted, see http://support.microsoft.com/kb/2620201.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.