I am unable to use OPENQUERY to a SQL 2005 OLAP server (RTM).
The actual query is very simple and works fine from management studio
on AS9.
I am a server administrator and logged directly onto server using
management studio against SQL2005 executing openquery against a linked
server to AS9 on same server.
A strange thing is that the profiler shows execution activity on AS9
and no errors. Also no errors appear in event viewer.

Here is the query:
SELECT *
FROM OPENQUERY(OLAP_SERVER, 'select [Measures].[Sales] on 0 from
sales')


Here is the error returned:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSOLAP" for linked server "OLAP_SERVER" reported
an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSOLAP" for
linked server "OLAP_SERVER".


Here is the script to create the linked server:
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'OLAP_SERVER',
@srvproduct=N'MSOLAP', @provider=N'MSOLAP',
@datasrc=N'wrypgpvmw06.apac.pfizer.com', @catalog=N'SalesReporting'
GO
EXEC master.dbo.sp_serveroption @server=N'OLAP_SERVER',
@optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'OLAP_SERVER', @optname=N'data
access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'OLAP_SERVER',
@optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'OLAP_SERVER', @optname=N'rpc
out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'OLAP_SERVER',
@optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'OLAP_SERVER',
@optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'OLAP_SERVER',
@optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'OLAP_SERVER', @optname=N'use
remote collation', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'OLAP_SERVER',
@locallogin = NULL , @useself = N'True'
GO

starts the surface area tool.
Openquery is not authorized by default, you have to change the config to
allow it.


"Renato" <renato_buda (AT) iprimus (DOT) com.au> wrote

The one that would not work for me is OPENQUERY.
The one that needs enabling is OPENROWSET and OPENNDATASOURCE.

I did not touch my server for a few days as I was working on other
things.
In the meantime the server was rebooted for maintenance.
I tried the query tonight and - amazement - it is now working!.

I dont know what to think, except I sure would like a tool that can
get under the hood and let me see what Kerberos is doing.

Jéjé wrote:

Try to set Allow InProcess option in linked server provider options

Vladislav Beliaev

Renato пи�ал(а):

Thanks - I did set that earlier. Perhaps it needed a restart as well
because it seemed to start working after the machine was rebooted.