We have SQL Server 2000 and Analysis Services installed on the same
Windows 2000 server. In Enterprise Manager we have set up a linked
server to the Analysis Server. We execute the following OpenQuery
statement from a SQL job:

select * from OpenQuery(SALES,'select {[Date.All
Dates].[Year].members} ON ROWS, { [Measures].[Sales] } ON COLUMNS from
[SalesCube]')

We receive the following error:

"Executed as user: DOMAIN\SQLEXEC. OLE DB provider 'MSOLAP' reported
an error. Access denied. [SQLSTATE 42000] (Error 7399) OLE DB error
trace [OLE/DB Provider 'MSOLAP' IUnknown::QueryInterface returned
0x80070005: Access denied.]. [SQLSTATE 01000] (Error 7300). The step
failed."

The MSSQLSERVER and MSSQLServerOLAPService services use the same
domain login account (DOMAIN\SERVER). The SQL Server Agent is using a
different domain account (DOMAIN\SQLEXEC). Both domain accounts
(DOMAIN\SERVER and DOMAIN\SQLEXEC) are members of the OLAP
Administrators group on the server.

We have run this same OpenQuery statement from Query Analyzer and any
users with a SQL Login (sa, etc.) can run the OpenQuery statement
successfully. NT authenticated users receive the same "Access denied"
error as the SQL job.

Does anyone know why NT authenticated users (including the domain
account used by the SQL Server Agent) cannot access the Analysis
Server from the SQL Server?

You should be able to do this.
Possibly there is a problem in the way you've configured the linked server.
See if you can follow the linked server example in Appendix F of the AS
Operations Guide.
http://www.microsoft.com/technet/pro...e/anservog.asp
Hope that helps.
--
Dave Wickert [MS]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI Practices Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"BI-DBA" <ronhippen (AT) hotmail (DOT) com> wrote

I am still getting the "Access denied" error even though I used
sp_addlinkedserver with MSOLAP.2 instead of "Microsoft OLE DB Provider
for Olap Services 8.0" from within Enterprise Manager.

I also changed the Data source on the linked server from the actual
server name to "localhost" and still got the error.

Any other ideas as to where the security breakdown is occurring?