Re: OPENQUERY - Access denied

Try to set Allow InProcess option in linked server provider options

Vladislav Beliaev

Renato писал(а):

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
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:
starts the surface area tool.
Openquery is not authorized by default, you have to change the config to
allow it.

"Renato" <renato_buda@xxxxxxxxxxxxxx> wrote in message
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:
FROM OPENQUERY(OLAP_SERVER, 'select [Measures].[Sales] on 0 from

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]
EXEC master.dbo.sp_addlinkedserver @server = N'OLAP_SERVER',
@srvproduct=N'MSOLAP', @provider=N'MSOLAP',
@datasrc=N'', @catalog=N'SalesReporting'
EXEC master.dbo.sp_serveroption @server=N'OLAP_SERVER',
@optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'OLAP_SERVER', @optname=N'data
access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'OLAP_SERVER',
@optname=N'rpc', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'OLAP_SERVER', @optname=N'rpc
out', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'OLAP_SERVER',
@optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'OLAP_SERVER',
@optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'OLAP_SERVER',
@optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'OLAP_SERVER', @optname=N'use
remote collation', @optvalue=N'true'
USE [master]
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'OLAP_SERVER',
@locallogin = NULL , @useself = N'True'


Relevant Pages

  • Re: exec("zip ...) works very infrequently.
    ... I'm running a PHP script on my ISP's server to zip some files. ... you might add a second parameter to the 'exec' function. ... echo print_r; ...
  • Re: String manipulation - taking one long csv string and putting into separate record fields
    ... From what I can work out, it looks as if the script is taking ... "%2147746132" from the computer VPN-01 when attempting to activate the ... computer VPN-01 when attempting to activate the server: ... EXEC xp_startmail ...
  • Re: Replicating database schema changes in "daisy chain" situation
    ... create database central ... exec sp_replicationdboption 'central','merge publish','true' ... SQL Server B> is installed in another country and replicates key master data from server A.> MSDE Instance C replicates with Server B to receive this master data as well> as merge data that is shared only between B and C. ...
  • Re: Script to delete backup files which are 7 days old
    ... you might want to know that the sp_smtp_sendmail and xp_smtp_sendmail have been used for this purpose by many many people on SQL Server 2000. ... DECLARE @DeleteFiles NVARCHAR ... SELECT 'exec xp_cmdshell ''DEL "' ...
  • Re: Verwaiste Benutzer
    ... > jedoch fehlt die Zugriffsberechtigung auf den Server. ... > Muss ich jetzt tatsächlich allen Benutzern neuen Zugriff ... EXEC sp_configure 'show advanced options', ... WHERE name NOT IN (SELECT name FROM master.dbo.sysxlogins) ...