Hello All, please may I have your assistance with the following issue?

All my MDX queries are being called via a linked server from T-SQL

The AS machine is separate standalone server in the same domain as the MS
SQL Server.

The error I get is as follows
OLE DB provider 'MSOLAP' reported an error. database XXXX does not exist

The database XXXX does certainly exist and to validate this I can open the
link server in the SQL Server Enterprise Manager under Security and can see
all the details

Link server properties are as follows:


The full provider string

Cache Ratio=0.1; Cache Ratio2=0.1; Cache Policy=5; Execution Location=3;
Default Isolation Mode=1

The Data Source is the correct AS server name
The location is blank
The Catalog name is the correct AS Db Name

In the security tab I've tried both the options 'Be made using the login's
current security context' as well as 'Be made using this security context'
where I used a valid domain account which is part of the OLAP
adnministrators group on the AS machine as well as part of the local
administrators group on the SQL Server machine.

Any advise on the above would greatly appreciated

thanks

John

--
Message posted via http://www.sqlmonster.com

I can think of at least 3 possibilities.
1) The actual NT account being used is the SQL service account. It must be a
domain account in common with AS and the RDBMS machines. If the user is not
a sqladmin then the SQL CMD execution account is used. Neither account may
be known to AS as a valid account.
2) Does that domain account have access to the cube? By default, the
everyone role is not created on a cube. Can you see Foodmart through your
linked server?
3) You are trying to run trusted for more than 1 hop. Can you access the
linked server from the RDBMS machine itself?

--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"John Shiangoli via SQLMonster.com" <forum (AT) nospam (DOT) SQLMonster.com> wrote in
message news:7cc6fc63dfc7448590803e80136a640b (AT) SQLMonster (DOT) com...