The team I recently moved into had never had a data source created on their report server for an Analysis Services source.

A co-worker helped me got a data source created with Microsoft SLQ Server Analysis Services as the Data Source Type.

For credentials for connection, we correctly flagged “Credentials stored securely in the report server” with the right User name and Password.

The initial try of using the newly created data source failed, however. After fixing two issues, the configuration for the data source finally worked. The final configuration is shown below.

Since I don’t do this kind of task every day, I thought it would be a good idea to write down how we fixed the issues.

1. The user name for the connection should be part of the server role

This is the error message from the report server before we made sure that the user name is part of the server instance role.

In SSMS, right click on the Analysis Services server instance, select Properties. On the Security tab, make sure that the user name is added. I have a little doubt about the user being part of the Server administrators. It seems to be too much privilege to me.

2. Check "Use as Windows credentials when connecting to the data source”

Before we check this option, here is the error we got:

I guess this has to do with the fact that we can only connect to Analysis Services (and also Integration Services) instance via Windows Authentication.

(Note: in the screen shot above, ignore the red warning about invalid password. I changed the User name to be a fake user name for this blog.)