Have you ever encountered the error message “OLE DB error: OLE DB or ODBC error: Login failed for user ‘DOMAIN\COMPUTERNAME$'” while deploying or processing SSAS OLAP cubes? If so, you’re not alone. This error is quite common and can be easily resolved by understanding the concept of SQL Server impersonation in SSAS.

The reason for this error is that the processing engine is configured to use the default impersonation information, which is usually the DOMAIN\ComputerName. However, this default user must also have access to the SQL Server database engine with proper read privileges. If this user doesn’t have the necessary rights, the error will occur as it cannot connect to the source database.

Resolution

To resolve this error, follow these 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 these steps 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.
5. The fully qualified user should be automatically displayed in the box.
6. Click User Mapping on the left-hand panel and check the database on which the access is to be provided.
7. In the Database Role Membership section below, assign the role “db_datareader” for the user.
8. On the Status page, select Permission as Grant and Login as Enabled and click OK.
9. 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 these steps 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.

Now, the cube database can be processed without any errors.

Takeaway

In summary, if you encounter login errors while processing the cube in SSAS, it is important to check the Impersonation Information for your data source. It is recommended to choose “Use specific user name and password” as the impersonation method and use a dedicated Windows domain account. This account should be a regular domain user or part of a restricted domain user group, with no password expiration policy. Additionally, the account should be mapped to a SQL Server database login with db_datareader role membership.

By following these best practices, you can ensure a smooth and error-free processing of your SSAS cubes.