Linked Server to Analysis Services, SQL 2008, AS 2008, how to do this right ?

_Scotch_
_Scotch_ used Ask the Experts™
on
Hi Experts.

So I have a virtual machine running Windows Server Enterprise 2008 and Microsoft SQL Server 2008. I logon to it with my standard corporate domain/me credentials.

I swear that sometimes this works and sometimes it doesn't :)  

I built a data mining model with AS2008 and at the end of that process you get an analysis services database and a DMX query to that database that you can use directly.  I can run this query in SQL Server Management Studio with a connection to analysis services and it runs fine.

On the same machine, I got into SQL Server Management Studio and created a linked server to MicroSoft OLE DB Provider for Analysis Services 10.0.

In a Transact SQL command, I issue the query, and this is the thing that appears to work "now and then" :)  Today I get logon errors for user NT AUTHORITY\ANONYMOUS LOGIN

Can somebody tell me how to make this work like its supposed to ?     :)
This is a functional DMX query to an analysis services database.

SELECT
  t.[CaseID],
  t.[L2Count],
  t.[L3Count],
  PredictProbability([Tbl Escalation Predictions].[Is Escalated]),
  [Tbl Escalation Predictions].[Is Escalated]
From
  [Tbl Escalation Predictions]
PREDICTION JOIN
  OPENQUERY([BIDB],
    'SELECT
      [CaseID],
      [L2Count],
      [L3Count]
    FROM
      [dbo].[vEscalationCandidates]
    ') AS t
ON
  [Tbl Escalation Predictions].[L2 Count] = t.[L2Count] AND
  [Tbl Escalation Predictions].[L3 Count] = t.[L3Count] AND
  [Tbl Escalation Predictions].[Case ID] = t.[CaseID]


This is the linked server setup:

/****** Object:  LinkedServer [SELF_DMX]    Script Date: 01/29/2010 09:14:03 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'SELF_DMX', @srvproduct=N'WHATEVER', @provider=N'MSOLAP', @datasrc=N'myvirtualmachine', @catalog=N'EP1'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SELF_DMX',@useself=N'False',@locallogin=NULL,@rmtuser=N'MYDOMAIN\Me',@rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @server=N'SELF_DMX', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SELF_DMX', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'SELF_DMX', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SELF_DMX', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SELF_DMX', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SELF_DMX', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SELF_DMX', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SELF_DMX', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'SELF_DMX', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'SELF_DMX', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SELF_DMX', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'SELF_DMX', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'SELF_DMX', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

The SQL query itself:

select * from openquery(self_dmx, 'SELECT
  t.[CaseID],
  t.[L2Count],
  t.[L3Count],
  PredictProbability([Tbl Escalation Predictions].[Is Escalated]),
  [Tbl Escalation Predictions].[Is Escalated]
From
  [Tbl Escalation Predictions]
PREDICTION JOIN
  OPENQUERY([BIDB],
    ''SELECT
      [CaseID],
      [L2Count],
      [L3Count]
    FROM
      [dbo].[vEscalationCandidates]
    '') AS t
ON
  [Tbl Escalation Predictions].[L2 Count] = t.[L2Count] AND
  [Tbl Escalation Predictions].[L3 Count] = t.[L3Count] AND
  [Tbl Escalation Predictions].[Case ID] = t.[CaseID] ')



This is the result:

OLE DB provider "MSOLAP" for linked server "self_dmx" returned message "OLE DB error: OLE DB or ODBC error: Deferred prepare could not be completed.; Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.; 28000.".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT
  t.[CaseID],
  t.[L2Count],
  t.[L3Count],
  PredictProbability([Tbl Escalation Predictions].[Is Escalated]),
  [Tbl Escalation Predictions].[Is Escalated]
From
  [Tbl Escalation Predictions]
PREDICTION JOIN
  OPENQUERY([BIDB],
    'SELECT
      [CaseID],
      [L2Count],
      [L3Count]
    FROM
      [dbo].[vEscalationCandidates]
    ') AS t
ON
  [Tbl Escalation Predictions].[L2 Count] = t.[L2Count] AND
  [Tbl Escalation Predictions].[L3 Count] = t.[L3Count] AND
  [Tbl Escalation Predictions].[Case ID] = t.[CaseID] " for execution against OLE DB provider "MSOLAP" for linked server "self_dmx".

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You can start a trace with Profiler on the SSAS server and catch Login/Logout events. Any error messages will be placed in it.
SSAS supports only Windows authorization, so your virtual box should be in the domain or you have to define an account in virtual box with the same user name and password.

I usually use connection within current security context (@useself=N'True' in  sp_addlinkedsrvlogin).

best regards
Grzegorz
Commented:
Interesting.  I happened to be creating another model and somewhere when defining the AS data connections II got prompted for impersonation information.  That caused me to check _this_ model and there was no impersonation information specified.  I gave it some and things have been running perfectly.

I'm starting to think that there was nothing wrong in SQL - it was the OpenQuery() coming back to SQL inside the DMX query that was failing.  

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial