Problem

This is one of the errors that I frequently encounter while deploying or processing SSAS OLAP cubes on the client's computers or on a remote server. Recently, one of my team members also faced the same issue and so I thought to write a resolution for the issue.

The error is something like this:

OLE DB error: OLE DB or ODBC error: Login failed for user 'DOMAIN\COMPUTERNAME$'.; 28000.

You can see this in the process progress dialog below.

Why does this error occur in SSAS?

The reason for this error is because the processing engine is configured to use the Default impersonation information while reading the source database, which is usually the DOMAIN\CompuerName. However, it is also necessary that this default user must have access to the SQL Server database engine with proper read privileges. If this user doesn't have rights on the database engine, then this error will pop up as it cannot connect to the source database.

Resolution

In order to resolve this error, we need to perform the following steps:

1. Create a new SQL Login with Windows Authentication mode and Grant Read Access to the database.
2. Use this new user in SSAS to connect to the source database.

Create a new SQL Login with Windows Authentication mode and Grant Read Access to the database

Follow the steps below to create a new Windows Authenticated user in the database.

1. Expand Security on the database instance.
2. Right-click on Login and select New Login.
3. Click the Search button on the New Login dialog box.
4. Enter the name of the windows user in the textbox and click Search. The fully qualified user should be automatically displayed in the box.
5. Click User Mapping on the left-hand panel and check the database on which the access is to be provided.
6. In the Database Role Membership section below, assign the role "db datareader" for the user.
7. On the Status page, select Permission as Grant and Login as Enabled and click OK.
8. To verify this new user, expand Login under Security. The new user should be visible in the list.

Use this new user in SSAS to connect to the source database

Follow the steps below in order to configure the SSAS database to connect to the source using the new user credentials.

1. Expand Databases under the Analysis Services instance.
2. Right-click on the database and select Properties.
3. Click on the Browse (...) box for Data Source Impersonation Info.
4. On the Impersonation Information dialog box, select Use a specific Windows user name and password.
5. Provide the username in the format "DOMAIN\UserName" and the correct password and click OK.
6. Now, the cube database can be processed without any errors.

Takeaway

To summarize the above, if you're receiving login errors while processing the cube, please check the Impersonation Information for your data source. It is a best practice, to choose “Use specific user name and password” as an Impersonation method and use a dedicated Windows domain account.

This dedicated domain account should be configured at a minimum as follows:

• It should be a regular domain user or part of a restricted domain user group.
• There should be no password expiration policy. Otherwise, the password may expire and processing will fail at some point later.
• The Account should be mapped to a SQL Server database login with db_datareader role membership.

References

For Best Practices to process a cube in SQL Server Analysis Services, please refer to this article.

To understand the architecture of SSAS processing, refer to this whitepaper.

Refer to this article for another error in SSAS: Duplicate Attribute Key found while processing.