Cant connect to analysis services via excel

5
votes

I have an analysis services cube in SQL server 2005 which I'm connecting to via an excel front end.

When I connect via one user its fine, but when I log on to the same machine as another user I get an error in my excel spreadhseet - "user...does not have access to the [Cube name] database"

Obviously the first user has the correct permissions, but how do I set up analysis services to allow other users to join the party?

4
sql-serverolapbusiness-intelligencessas

4 Answers

3
votes

Login to the machine with an account that is an administrator (Domain\CubeAdmin) on the cube. Connect to the cube in BIDS (run devenv.exe and open Analysis Services Database).

Under Roles, create a reader role and in the Membership tab, add the user account (Domain\NewUser).

All this will only work if the SSAS Server Administrator gives the Domain\NewUser access to the server.

1
votes

The Windows user accounts that you are trying to access SQL Analysis Services with need to be added to the Roles in the Cube that would allow the permissions you want.

If you are connecting over HTTP using msmdpump.dll through IIS you need to turn on Authentication for that site and allow the Windows user account to access the site.

If the IIS site using msmdpump is on another machine and you aren't using a domain then the accounts would need to exist on both servers with the same password.

0
votes

I know this is old but for other's reference, I had to repair the MS Office install to resolve a connectivity issue with SSAS. The user was added to the role, but the error "Cannot connect to server" was displayed when connecting.

0
votes

Raj has already answered the initial question... You need users to be set up with at least read access to your SSAS instance.

However, the error "Cannot connect to server" does not necessarily mean it's an authentication issue, it actually doesn't mean much. I've seen this error on Excel 2007 on various occasions, where the underlying error could be anything, this is just a generic error from Excel.

Several aspects that caused problems on my end were (things to check):

  • User has access to the web site (if not using anonymous auth)

  • ADOMD and OLEDB for Analysis Services are installed locally (correct version)

  • User propagated to SSAS has read access to instance (are you using ApplicationPoolIdentity?)

  • Handler mapping (script mapping for *.dll) is set up

For a complete guide of how to set up HTTP access for SSAS check: Microsoft - Configure HTTP Access to SSAS via IIS

Cheers