SSAS OLAP Cube Ignores Credentials in Connection String

0
votes

I'm having quite a interesting issue with my SSAS server today. I have two (almost) identical data cubes. Each named Desserts and Test, both these cubes have one role, and one user assigned in that role:

enter image description here

enter image description here

I'm accessing both cubes in excel via the same "msmdpump.dll" file located in IIS on the same server. The connection string for both cubes looks like the following:

enter image description here

Desserts:

Provider=MSOLAP.8;Persist Security Info=True;User ID=xxxDessertsAdmin;Initial Catalog=xxx_Desserts_GL;Data Source=https://Desserts.xxx.com/olap/msmdpump.dll;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Update Isolation Level=2

Test:

Provider=MSOLAP.8;Persist Security Info=True;User ID=xxxTestAdmin;Initial Catalog=xxx_Test_GL;Data Source=https://Test.xxx.com/olap/msmdpump.dll;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Update Isolation Level=2

Both these cubes are build correctly and contain the same data. I can verify this by using the cube browser in SSAS.

So to review, excluding the different name I have the: same cube, same data, same roles, same users, same "msmdpump.dll", same App Pool, same connection string in Excel. As far as I tell nearly identical cubes. However one of them does not work.

When I try to access the "Desserts" cube in excel in shows I do have permission to access the cube. I can reproduce this issue directly by granting permission for the "IUSR" user account to the desserts cube, setting up the connection string, then revoking that access (Removing "IUSR" from "Desserts Role"):

enter image description here

It appears to me that "msmdpump.dll" ignores the username passed through the connection string, and instead is trying to access the cube using the "IUSR" account. I do not understand why this is happening. The app pool for the " msmdpump.dll" is not running under that user name. I have not configured anything on my server to run under that context.

Why is (for only the desserts cube) "msmdpump.dll" ignoring the username in the connection string and instead trying to access the cube using the "IUSR" account?

1
excelssaspivot-tableolapcube

1 Answers

0
votes

Yes same everything except the website, each cube was going through a separate website, the configuration differed between the two. Specifically it was the "Authentication" configuration within IIS that caused the issue:

enter image description here

"Annoymous" authentication needs to be "Disabled" if you wish to have the credentials passed through to the OLAP cube. Otherwise it seems to just default to the "IUSR" account.