Recently while doing a Project Server 2010 installation I ran into an issue when I went build the OLAP Cube after configuring the environment in accordance with the Microsoft recommended configuration. This particular client had their SQL configured so Analysis Services was on a separate server from the database server. Every time we went to build the cube the cube structure would be created successfully, but in the last step of the build it would fail with the message,
“Login failed for user ‘DOMAINAnalysisServerName$’”
In short, Analysis Services could not get to the ProjectServer_Reporting database to populate the cube it had just built. This was also verified by looking at the SQL Log file,
“Date: 6/15/2012 12:31:02 PM, Log: SQL Server (Current – 6/15/2012 12:31:00 PM), Source: Logon, Message: Login failed for user ‘DOMAINAnalysisServerName$’. [CLIENT: 126.96.36.199]”
What the problem finally turned out to be is that SQL Server Analysis Services was running under the Local Sys Account on the Analysis Services Server.
Once I opened the service on the Analysis Services Server and changed the “Log on as” from “Local System Account” to a service domain account that had DB_DATAREADER permission to the ProjectServer_Reporting DB and restarted the service the cube build worked fine.