I have OLAP Cube on SQL Server 2008 R2.
I can connect to it from Excel locally (via localhost), but I can't connect from Excel remotely. I get the following error: "No connection could be made because the target machine actively refused it".
I enter server IP address and user credentials, which has reader roles in Cube.
What was the error's reason?
Thank you!
The user of machine from which you are connecting through excel remotely , does that use has rights on remote SSAS server ? If not then you can add this user . Go to SSMS , Right click on analysis services server desired instance , click on properties , then click on security and check the administrators . You can add this user as administrator .
PTSLITE needs to be installed for the pivot tables in excel.
Ptslite.exe and Ptsfull.exe, are provided on the SQL Server 2000 CD-ROM, in the folder \Msolap\Install\Pts. Ptslite.exe installs the PivotTable Service files only; Ptsfull.exe installs the PivotTable Service files and Microsoft Data Access Components (MDAC).
... the target machine actively refused it
Means there is a firewall on the target machine and blocked the port you wanted to connect or the OLAP service is not working. Since you said "it works locally" this could not be the reason. Another possibility is service is configured to serve only localhost interface.
