Home > SSRS Expert > SSRS & SSAS #62 – Create a data source on Report Server for an Analysis Services database

SSRS & SSAS #62 – Create a data source on Report Server for an Analysis Services database

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. image

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.

image

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.

image

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

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

image

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.)

Categories: SSRS Expert
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: