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

connect to AS2000 cube error

Here is my setup: I have Reporting Service and SQL Server 2000 in one box
(Box A), and IIS and Analysis Service in another box (Box B), which has
couple of cubes based on the database in Box A, using SQL Authentication and
Allow save password. Create AS2000 data source at Reporting Project, then
create a report, I can preview the report fine in VS.2003. But I got the
following error after deploying the report to the reportserver and browse the
report:

Cannot create a connection to data source 'Cube'. (rsErrorOpeningConnection)
Get Online Help
Database 'Cube' does not exist.
Do I miss anything?

Thanks

Reporting services and Analysis Services only support Windows authentication.

Another problem with your setup is that even if you use windows authentication you cannot validate users between to servers without using Kerberos and account delegation.

Its possible to use standalone servers and maintain the same windows accounts on the separate machines manually.

Regards

Thomas Ivarsson

sqlsql

Connect to Analysis Services with specific username

Hello,

We are trying to develop an ASP .Net web application that connects to an SQL Server 2005 analysis services and executes MDX statements. We want the connection to be made using a specific user name that we specify in the code rather than having the web application inherit the logged on domain user account.

We tried to specifiy the user name in the connection string as follows, but it is still taking the logged in user account rather than the one specified.

Microsoft.AnalysisServices.Server s = new Microsoft.AnalysisServices.Server();

s.Connect("Provider=MSOLAP.3;Password=123;Persist Security Info=True;User ID=domain\username;Data Source=<server ip>;Initial Catalog=<analysis DB>");

Is this possible?

Note that if we are using the OLEDB 9.0 provider for Analysis Services to connect an excel sheet to the analysis database we can easily specify the user name and password, so is there a similar way to do the same from a .Net application?

Any feedback is appreciated, thanks,

Grace

If you want to use a Windows Indentity, then you can′t. For other connection options refer to the connection properties which are shown e.g. here:

http://www.ssas-info.com/ssas_articles/ssas_articles/analysis_services_2000_and_2005_connection_string_properties.html

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens,

The link helped a lot. We were able to connect using specific Roles and that is what we were looking for exactly but didn't know it exists.

Grace

Connect to Analysis Services 2005 from Excel 2007

I have recently discovered the ability to connect to an Analysis Service 2005 OLAP connection from Excel 2007. It looks and functions great. I do have one thing that am trying to do, however, that I am hoping it has. Does anyone know of a way that from the Pivot field panel, you can see the description metadata that is defined on the measures and dimensions in the Analysis Services cubes? It would be very useful if this come up in a tooltip or something. Otherwise, the business users will need a supplemental document while they are navigating the cubes.

Thanks in advance for your response.

I'd be interested in knowing how to do this as well.|||Sorry Guys, I don't think there is any way of accessing this data using the out-of-the-box functionality in Excel 2007. I even tried using the CUBEMEMBERPROPERTY() function in Excel, but it did not seem to be able to access the intrinsic properties like DESCRIPTION.|||Yikes! I thought Excel 2007 supported all SSAS2005 features? I can't express how useful and how much of a time-saver this feature would be. The "workaround" is that I'd have to create a big ugly document that our business users will have to print out and refer to. Even a simple tooltip that showed the member description in the dimension/measure browser would be immensely useful.

Is this something that we'd need to file w/ MS Connect?|||Thank you for your responses. I suppose I will have to put together a user-friendly on-line reference for people to use. Hopefully a future version will have this feature.|||

Unfortunately I don't think there is a section on Connect for Excel, otherwise this would definitely be the place to lodge such a query. I will see if I can find out if there is an avenue through which such a request can be lodged.

It is not an SSAS issue as the property is present, Excel just does not expose it. It might be possible to extend Excel to display the descriptions, but I have not had a chance to look into this. It would be possible to generate documentation from the meta-data in SSAS BI Documenter (www.bidocumenter.com) does this, but the documentation it produces might be a bit overwhelming for a business user.

sqlsql

Connect to Analysis Manager via VB2005

Does anybody know how to use VB2005 or VB.NET to connect to analysis manager to extract the CUBE information?

Can I have a sample program? I'm very stuck.

Thanks in advance :)

Check out AMO for the cubes metadata
or ADOMD.net to query the cube

There are some examples on those pages, and there are more in the sql server samples.

C

Connect to Analysis Manager over HTTPS and non domain account

Please, I need your help... I`m desperate...

I`m trying to connect to the Analysis Services (AS 2000 Enterprise Edition SP4 on Windows Server 2003 EE, respository is in Access DB) throught Analysis Manager over HTTPS(HTTP) connection. I dont want to use domain account.

I need to specify USER_ID and PASSWORD to the Analysis Manager, but in registration of new server is only IP address or HTTP adress (for http://server) - but it doesnt work - it says Unauthorized. But I`ve tried to use Anonymous account and it doesn`t work (client wan`t able to make connection)... I would like to use Basic Authentification... Thise account is member of OLAP_Administrators... but the problem is where should I put USER_ID and PASSWORD?

It works perfect in Excel 2003... MSOLAP.asp work fine in IE...

Thanks very much for your advise

RicmondIs it due to "BUG: Analysis Manager Does Not Support HTTP or HTTPS onnections"

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q311310

Ricmond

Configuring HTTP Access to SQL Server 2005 Analysis Services on Microsoft Windows 2000

Microsoft has kindly posted articles on how to configure 2005 SSAS for
HTTP (formerly called XMLA) for Windows XP and Windows 2003 here:
http://www.microsoft.com/technet/pro.../httpasws.mspx
http://www.microsoft.com/technet/pro.../httpssas.mspx
However, I have not been able to find documentation to support or
refute support of this feature on Windows 2000. Is it possible to use
the native XMLA/SOAP support of SQL2005 when hosted on a Windows 2000
server?
Thanks!
Sorry, but I think you have to have XP or 2003. the native HTTP/SOAP support
in SQL Server does not work on 2000. I am not sure if SSAS for HTTP does
though. You need to check in an AS specific newsgroup.
Best regards
Michael
<russel.krause@.upsidesoft.com> wrote in message
news:1143500586.771866.101310@.j33g2000cwa.googlegr oups.com...
> Microsoft has kindly posted articles on how to configure 2005 SSAS for
> HTTP (formerly called XMLA) for Windows XP and Windows 2003 here:
> http://www.microsoft.com/technet/pro.../httpasws.mspx
> http://www.microsoft.com/technet/pro.../httpssas.mspx
> However, I have not been able to find documentation to support or
> refute support of this feature on Windows 2000. Is it possible to use
> the native XMLA/SOAP support of SQL2005 when hosted on a Windows 2000
> server?
> Thanks!
>