locked
Can't get linked server working between SSMS and SSAS on two different servers RRS feed

  • Question

  • I've read a bunch of posts already, and can't seem to figure this out.

    I’m having trouble accessing SSAS on server A, from server B.  This works fine when I do it on the same machine.  I’ve created a linked server on server B:

    EXEC sp_addlinkedserver

    @server=N'SQL_AS',

    @provider=N'MSOLAP',

    @srvproduct=N'',

    @datasrc=N'serverA',

    @catalog=N'Test'

    I’ve set the linked server to run under the specific security context of a domain account that has access in a role (again, one that works when I do this on the same server).  However anytime I try to edit the linked server, I receive the error below:

    The test connection to the linked server failed.

    An exception occurred while executing a Transact-SQL statement or batch.

    (Microsoft.SQLServer.ConnectionInfo)

    Cannot initialize the data source object of OLE DB provider “MSOLAP” for linked server “SQL_AS”. OLE DB provider “MSOLAP” for linked server “SQL_AS” returned the message “The following system error occurred: No connection could be made because the target machine actively refused it.”.

    (Microsoft SQL Server, Error: 7303)

    Here is what I'm running on server A and B:

    Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64)   Sep 21 2011 22:45:45   Copyright (c) 1988-2008 Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)

    Some posts recommend the link below.  I’ve followed the advice under error condition 2, and set all Facet properties to “True” just to see if I could get it working. It didn’t change the error message at all. http://technet.microsoft.com/en-us/library/cc917670.aspx

    The SQL Browser service is running on both machines.

    I’ve also turned off both firewalls, which also didn’t change the error message.

    I don't see any errors in event viewer relating to this on either server.

    I can create a linked server to the DB engine.

    Any ideas?



    • Edited by Eta Argus Wednesday, May 16, 2012 8:17 PM More Info
    Wednesday, May 16, 2012 8:10 PM

All replies

  • More info:

    I am able to connect to the database engine on that server, via ip address.  When I try to connect to AS via ip:port, i get the error below.

    Cannot connect to <ip>:<port>

    Additional Information:

    A connection cannot be made.  Ensure that the server is running.

    (Microsoft.AnalysisServices.AdomdClient)

    No connection could be made because the target machine actively refused it <ip>:<port>

    (System)

    Thursday, May 17, 2012 4:07 PM
  • Are you certain that the server with the linked server set up and the destination AS server are running the exact same bits??  I have discovered recently that if there is a difference in the version of the bits installed on either the server using the linked server and the AS destination, then AS will refuse the connection.  If you have access to different servers running different versions, script out your linked server and try to connect from different versions.
    • Proposed as answer by Lola Wang Friday, May 18, 2012 6:30 AM
    • Unproposed as answer by Eta Argus Friday, May 18, 2012 2:05 PM
    Thursday, May 17, 2012 11:19 PM
  • I appreciate the effort Chris, but I've confirmed they're both 64 bit by running:

    SELECT @@VERSION

    I did try from another machine, but this was an R2 instance:

    Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)   Jun 17 2011 00:54:03   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor) 

    This server was originally giving me the same error, but I tried leaving the firewall on and opening the ports for the Browser Service, starting the browser service, recreating the linked server, disabling and then re-enabling "allow inprocess" under the MSOLAP provider, and it worked.  I followed those steps on the original, and I still get the same error.  Not sure what I missed.

    I did try setting up the linked server from a 32 bit instance of SQL 2008.  The error for that is:

    An exception occurred while executing a Transact-SQL statement or batch

    (Microsoft.SqlServer.ConnectionInfo)

    A severe error occurred on the current command.  The results, if any, should be discarded.

    A severe error occurred on the current command.  The results, if any, should be discarded.

    (Microsoft SQL Server)

    I'm guessing that is a difference in bits issue?

    Friday, May 18, 2012 2:05 PM
  • One more thing, I can connect via SSMS to the instance of SA on Server A by using name or ip and port from other machines, even the 32 bit server, except for the original server (server b) that I wanted to connect with.  I used to be able to connect from there, the linked server was what wasn't working.  The error when I try to connect through SSMS from Server B is:

    A connection cannot be made. Ensure that the server is running.

    (Microsoft.AnalysisServices.AdomdClient)

    No connection could be made because the target machine actively refused it <ip>:<port>

    (System)

    Friday, May 18, 2012 2:28 PM
  • I'm not talking about the bitness of the installation, but rather the version number of SQL Server (2008 SP2 vs 2008R2 vs 2012, etc).  The issue that we had, when our DW and AS environments were upgraded to 2008R2, was with running an open query via a linked server to SSAS.  The connection failed / was constantly refused because the SQL Server versions were not the same.  I was thinking that your issue was the same that we ran into.

    Sadly, I don't have any other ideas.

    Friday, May 18, 2012 6:44 PM
  • Oh okay, gotcha.  Thanks anyway!
    Friday, May 18, 2012 8:55 PM