executing OPENQUERY

Hi,

I'm trying to execute an openquery statement from SQL Server 2005 against a linked server AS2005 (both on same machine) from Management Studio.

If I use a one connection with full rights I have no problem. But if I use the specific connection for that application I got this error:

OLE DB provider "MSOLAP.3" for linked server "ASLOCAL2" returned message "The following system error occurred: A specified logon session does not exist. It may already have been terminated. .".

Msg 7303, Level 16, State 1, Line 5

Cannot initialize the data source object of OLE DB provider "MSOLAP.3" for linked server "ASLOCAL2".

"Ad hoc data-mining query" is checked.

MSOLAP Provider has "Allow inprocess" checked.

Code Snippet

SELECT * FROM OPENQUERY(ASLOCAL2,'SELECT non empty {[Measures].[Prices Avg] ,[Measures].[Prices Max] ,[Measures].[Prices Min] } ON COLUMNS,non empty [Tbl DW Dim Type].[Name].&[1] ON ROWS FROM [DW DEV] ')

Any ideas ?

Thnx.

Have you tried running a profiler trace against SSAS while trying to run the OPENQUERY() ?

I am wondering if it is a permissions issue for the account that the application is using for the connection.

|||The only difference between these tow accounts is that one account is Windows account and the other one is Sql server account.|||

Well that is probably your issue. SSAS only supports windows authentication. You would need to have the linked server setup to authenticate using the current login's security context, which works fine for windows accounts, but if you use a sql account, SQL Server will fall back to authenticating against the SSAS server using the account that the SQL Server is running under, which probably does not have permissions to query the cubes.

So your choices are to either only use windows accounts against the linked server or to make sure that the account that SQL Server is running under has access to the appropriate objects in SSAS.

|||

You're right.

I changed the way that the (web)application is connecting to the Sql server (until now I used sql account) and now I am using an windows account.