SSAS errors: Logon failure / Cube process failure

Often I encounter this error myself when trying to process an SSAS cube at a client’s site or get asked by customers and friends how to solve this error. Although it may seem really obvious,  not everyone may understand “what” is rejecting their credentials or “what” are they authenticating against.

Simple. The data source.

So, what credentials are being passed through to your data source?

At this point you may answer: “my Windows credentials” or “credentials of the current user.” This is not always the case.

Impersonation
Your Windows credentials are used to create the connection strings to your data source at design-time. During processing and run-time, Analysis Services needs an account to be able to reach the data source. Keep in mind that you will not always process Analysis Services cubes manually within BIDS or SSMS. Once the Analysis Services cube is initially deployed, in most environments you will have scheduled SSIS packages that will re-process the cubes every night. In order for Analysis Services to be able to read the data from your data source, it needs some form of credentials to pass through. This is also known as impersonation.

In Analysis Services 2008-R2, there are several impersonation options:

• Use a specific Windows username and password
• Use the service account
• Use the credentials of the current user
• Inherit (or default)

To learn more about each of these options you can read MSDN Books Online at: http://msdn.microsoft.com/en-us/library/ms187597.aspx

In a nutshell, the impersonation account specified needs to have read access to your data source. For example, if your data source resides in SQL Server, the account specified as your impersonation account needs to  be mapped to your database with db_datareader role membership. It is best practice to specify a dedicated account that has limited read-only access to the database. Avoid using an account with elevated privileges such as sysadmin or db_owner.

The recommended impersonation setting is to specify a Windows username and password. If your server is joined to a domain, you will have to specify DOMAIN\Username in the User name textbox and the associated password in the Password textbox as seen below:

If you specify “Use the service account”,  it will use the account used to start the Analysis Services instance to authenticate against your data source. In this case, the service account will need read access to the data source. Keep in mind that the system service accounts Network Service, Local System and Local Service will not have access to your data source if the data source is on a different server. Typically, you will use a Windows domain account that has Log on as Service privilege in Active Directory Group Policy as your service account to start Analysis Services.

Inherit (SQL Server 2008/2008-R2, Default in SQL Server 2005) uses the impersonation mode  and credentials set in the Data Source Impersonation Info database property. To view or modify this database property, open SSMS and connect to the Analysis Services instance, expand the databases folder and right click on the database you are interested and select properties. You will now see the database properties window and the impersonation mode selected. To change the impersonation mode, click on the elipsis inside of the Data Source Impersonation Info textbox highlighted below:

Per MSDN Books Online, by default the Data Source Impersonation Info database property is set to Use the service account. This means that if the Inherit impersonation option is selected in your BIDS solution, it will use whatever is specified in the Data Source Impersonation Info database property. If this database property was set to Default as shown on the image above, then it will use the Default impersonation mode in which it will use the impersonation method that is most appropriate for the context in which impersonation is used. For more details read http://technet.microsoft.com/en-us/library/ms126693.aspx.

In summary, if you are getting a logon failure when processing an SSAS cube, check the impersonation settings of your data source. As a best practice, choose “Use specific username and password” as Impersonation option and use a dedicated Windows domain account.

The dedicated domain account should be configured at a minimmum as follows:
– Regular domain user or part of a restricted domain user group
– No password expiration policy should be applied. If not, password may expire and processing will fail at some point.
– Account should be mapped to a SQL Server database login with db_datareader role membership.

If the dedicated domain account will be used as the service account, make sure it is part of the “Logon as a service” AD Group Policy.