Connect to Remote Analysis Server

Hi,

How do I connect to a remote Analysis Server using SQL management studio on my local machine.

Thanks in advance..

Hi,

At the remote computer you want to connect, your nt domain username should be at least in the MSOLAPUser group. And if you want to have server-wide permissions your username should be added to the list displayed in the security tab of the olap server properties screen.

Eralper

http://www.kodyaz.com

|||Hi eralper,

Thanks for the reply. But how can my local SQL management studio detect Analysis Services of Network Servers? As of now, it can only detect Database Engines of other network server..

Thanks again...
|||

What is the name of the physical machine you want to connect to?

Also when it was installed was the instance given a name?

If the instance was not given a name then you can just put the machine name in the server edit control, if it has a name then it should be servername\instancename in the control.

|||

Maybe the Analysis Server is not configured to allow remote connections?

1. On the remote machine, start the SQL Server Surface Area Configuration tool and verify that remote connections are enabled.

2. On the remote machine, verify that the SQL Browser windows service is running.

3. If you have the Windows Firewall enabled on the local machine or remote machine, you will need to make sure you open TCP ports so Analysis Server can accept incoming connections. The port numbers Analysis Server listens to are following:
2383 – port default instance of Analysis Server listens to
2382 – port SQL Browser service listens to for Analysis Services requests.

Note: Named instance of Analysis Services will dynamically pick ports to listen on your machine. The easiest way to discover the TCP port the named instance is listening is to look at the configuration file for SQL Server Browser file. Open file located on the %Program files%\Microsoft SQL Server\90\Shared\ASConfig. Open msmdredir.ini file and look at the < Instances> section in it.
For example you might see following in it;
<Instances>
<Instance>
<Name>Inst1</Name>
<Port>56178</Port>
</Instance>
</Instances>

This means your machinename\Inst1 is listening on the port 56178.

In many situations you would like to ensure your named instance is not picking port by itself but listens on pre-defined port. For that, you can modify the Port – Analysis Server property. Make sure you restart Analysis Server and SQL Server Browser service after changing this property.

Hope this helps,

-- Robert

|||

Hi,

If you open the SQL Server Management Studio, open the Registered Servers screen and select Analysis Services.

If you try to add a new olap server, a list of available servers are listed in the dropdownlist. Or you can use the browse button. But I guess these are not coming from a network search process for available servers.

Sorry, I don't have an answer for that.

But I can suggest a tool which scans for sql servers within an IP range, SQLPing2 by C. Andrews from sqlsecurity.com

Unfortunately this tool does not search for the MS AS instances.

Eralper

http://www.kodyaz.com