I'm trying to get a linked server set up from a SQL2k box to an AS2K
box. I can create the linked server just fine, but when I try to
execute a query using OPENQUERY(), I get the following:

[OLE/DB provider returned message: Database 'FoodMart 2000' does not
exist.]
OLE DB error trace [OLE/DB Provider 'MSOLAP' IDBInitialize::Initialize
returned 0x80004005: ].
Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSOLAP' reported an error.

I suspect this has to do with security but I'm not sure how to set up
the linked server since AS does not have a "standard login" concept. I
chose the option for using the login's current security context.

Do I need to do something to the AS 2000 box as well?

Please make sure that you install SP3 or latest for Analysis server and SQL
Server.

Regards,

BizWorld

"Randy Knight" <knight.randy (AT) gmail (DOT) com> wrote

and make sure that the useraccount that SQL runs as (and/or the account
sql agent runs as) has enough perms to connect to AS

I'm certain they're both SP3 ... but I'll check on the service accounts.

When you use linked servers, the SQL box makes a connection to the AS2k
box and passes through the query. Unless you have set up Kerberos, one
server cannot pass a clients credentials to another server. As such the
"user" that connects to AS will be the user account that the SQL service
is running under. You will need to go into AS and assign this user
account to a role which has access to the database/cubes you are
interested in.

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

you can delegate authority without kerberos using 'SetSpn' i believe or
simply 'trust account for delegation'

for the service/account that SQL Server will run under

I could be wrong, but I was pretty sure that the steps you are
describing are the ones you need to take to set up Kerberos. I think
Win2k3 uses kerberos for delegating trusts

Mosha has the exact steps for enabling Kerberos on his website at
http://www.mosha.com/msolap/articles...delegation.htm

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

The SQL Service account is a Domain Admin so it shouldn't matter, but I
explicity added it to a database role anyway.

Same Error as before:


Error 7399: OLE DB Provider 'MSOLAP' reported an error.

OLE DB error trace [OLE/DB Provider 'MSOLAP' IDBInitialize::Initialize
returned 0x80004005: ].