Up to now I've been using OPENQUERY and linked servers to run MDX
queries against my SSAS 2008 cubes. This seems to work fine, apart
from the fact that I now have multiple cube databases, and i'd rather
not have to have a separate linked server for each. I have a general
reconciliation pass which I want to be able to run against all of my
cubes, and maintaining a growing number of linked servers is not
really my idea of fun.

I was hoping to be able to change my query to run MDX on the SSAS
servers from this:

INSERT INTO TABLE (fieldList)
SELECT * FROM OPENQUERY(<SSASLinkedServer>,'<MDX Query>')

into something like this:

INSERT INTO TABLE (fieldList)
SELECT * FROM OpenRowSet('MSOLAP.4','DATASOURCE=<SSASServer>
\<SSASInstance>;Initial Catalog=[CubeDatabase];Integrated
Security=SSPI','<MDX Query>')

The OPENQUERY equivalent of this works fine. I get back exactly the
data I want.

The situation is less complicated than it might be, as <SSASServer> is
the same server that I'm running the TSQL query on; for this reason
I'm not expecting any double-bounce Kerberos-style authentication
issues.

Unfortunately the OpenRowSet query above doesn't work. i get the
following:

OLE DB provider "MSOLAP.4" for linked server "(null)" returned message
"Either the user, MYDOMAIN\MyUser, does not have access to the
[CubeDatabase] database, or the database does not exist.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSOLAP.4"
for linked server "(null)".

Can anyone give me an idea of what I'm doing wrong? I'm guessing that
somehow the authentication isn't getting passed through from TSQL to
SSAS, and it's inexplicably using anonymous login to access the cube
(which fails even if I allow Everyone to access the both <SSASServer>
and read the cube definitions) All the online discussions of this
either have no answer or the user has resorted back to using
OPENQUERY, which I would rather avoid using if I can.

I'm sure I'm not the only one who would love to get this sort of thing
working. Many thanks for any help you can give.

Mike.