SSAS 2005 Connection

SQL Server Analysis Services

PHILIP LOW


I need to connect SSAS which reside on another computer. I had a vb.net program which running fine if execute it on remote server. If i execute the program on my local machine it prompt me error when i try establish a connection to remote remote server (SSAS 2005).

Is it SSAS not allow remote connection I had try connect to SSAS remote machine using SSMS (SQL Server Management Studio) and it prompt error as well.

Error Description:

A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient)

Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. (System)

An existing connection was forcibly closed by the remote host (System)

Anything we could do for connect to SSAS on remote machine




Top

Re: SSAS 2005 Connection

Lucas Almeida


Hellow from Brazil,

see that....

http://support.microsoft.com/kb/914277/en-us

You must enable remote connections for each instance of SQL Server 2005 that you want to connect to from a remote computer. To do this, follow these steps:

1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
3. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply.

Note Click OK when you receive the following message:
Changes to Connection Settings will not take effect until you restart the Database Engine service.
4. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Service, click Stop, wait until the MSSQLSERVER service stops, and then click Start to restart the MSSQLSERVER service.

Regards!






Top

Re: SSAS 2005 Connection

PHILIP LOW

Hi Lucas,

Thanks for your valuable information.

But my concern is on SSAS (SQL Server Analysis Services). My program try establish connection to SSAS and it failed connect to remote machine.

I had enabled remote connection for my SSAS and restart the server and it still failed.

BTW,

I connection using AdomdConnection to establish a connection to SSAS on remoted machine. I notice that SQL Server Analysis Services authentication is disabled on SQL server analysis services (Windows authentication is defaulted). We could could try connect to local SSAS from SQL Server management studio.

Anyone could help on this

Regard,

Philip



Top

Re: SSAS 2005 Connection

Darren Gosbell

SSAS only uses windows authentication, there is no such thing as "SQL Server Analysis Services authentication". It does not use SQL server logins, they only relate to the relational engine, not to SSAS. Your windows account needs to be in a database role that has access to the OLAP database. If you cannot connect from SSMS, there is no way your .net application will be able to connect

This article is a good one for troubleshooting connection issues http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/cisql2005ascs.mspx





Top

Re: SSAS 2005 Connection

PHILIP LOW

Hi Darren,

Thanks.

I was thinking use linked server to execute mdx query on SQL server 2005 database engine.

Anyone could help on this.

BTW my Sample script would be

USE master

GO

/* Add new linked server */

EXEC sp_addlinkedserver

@server='LINKED_OLAP', -- local SQL name given to the linked server

@srvproduct='', -- not used

@provider='MSOLAP.3', -- OLE DB provider (the .2 means the SQL2K version)

@datasrc='localhost', -- analysis server name (machine name)

@catalog='Adventure Works' -- default catalog/database

GO

SELECT *

FROM OPENQUERY(LINKED_OLAP,'SELECT { [Measures].[Sales Amount], [Measures].[Tax Amount] } ON 0,{ [Date].[Fiscal].[Fiscal Year].&[2002], [Date].[Fiscal].[Fiscal Year].&[2003] } ON 1 FROM [Adventure Works] WHERE ( [Sales Territory].[Southwest] )')

/* Remove any previous references to the linked server */

EXEC sp_dropserver 'LINKED_OLAP'

I had the error

OLE DB provider "MSOLAP.3" for linked server "LINKED_OLAP" returned message "The following system error occurred: No connection could be made because the target machine actively refused it. .".

Msg 7303, Level 16, State 1, Line 2

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


Top

Re: SSAS 2005 Connection

Darren Gosbell

For a linked server to work you either need to setup your SQL Server to be running under an account that has access to the SSAS cubes, or you need to configure the linked server to run as a specific domain account. I think you need to use the linked server properties screen from SSMS to configure an account.





Top