none
Remote connection to Analysis Services RRS feed

  • Question

  • Hi,

     

    I'm working with SQL Server 2005.

    I have a problem with the remote connection to the Analysis Services component of a remote Windows Server 2003. I have enabled remote connections from Surface Area Configuration and I have enabled TCP/IP, VIA, Shared memory, etc, protocols.

    I have read the SQL Browser is shared between the Database Engine and the Analysis Services, and for this, according which one is running, the SQL Browser is listening through a port or another (1432 for Database Engine and 2382 for Analysis Services, if I get it right).

    For checking whether port 2382 is listening I do telnet from my computer and it's ok.

    Of course, Analysis Services, SQL Browser are running (really, all the services are running now).

    My problem comes when I try to implement a project with SQL Server Business Intelligence Development Studio. I create a new Analysis Services project, in the project properties I've write the name of the server (NAMESERVER/instance), I've established as a datasource the MS database example (AdventureWorksDW) and until here everything is correct, I generate the project and it's OK. But when I implement the project I get an error like this:

     

    'Can not establish a connection. Make sure the server is running'

     

    And the same it happens when I try: "File --> Open --> Analysis Services database..." I write the server name and I get the same error.

     

    Please I need your help. Do somebody know what might be happening?

     

    Thankss!

     

    PD: Sorry for my bad english. I'm spanish ;-)

    Tuesday, April 8, 2008 10:51 AM

Answers

All replies

  • Do you have analysis services with this name: NAMESERVER/instance

    Or only with this name: NAMESERVER

    Do you see something in the server box? If yes, highlight and open.

    If not, use create new database option.

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Tuesday, April 8, 2008 11:06 AM
  •  

    not sure whether problem is same or not, i also face the same issue some time back..

     

    just disable the VIA Protocol and try again

    it solved my problem.

    Tuesday, April 8, 2008 11:20 AM
    Moderator
  •  

    I've disabled VIA protocol in the server and I get the same problem.

     

    The name of the server is NAMESERVER\instance or IPSERVER\instance, but I've tested in both ways and it fails too.

     

    The firewall server is disabled, for more information. Furthermore, I can connect to Database Engine without problems with the Management Studio, but not at Analysis Services, I don't understand.

     

    Do you have any other suggestions?
    Tuesday, April 8, 2008 11:33 AM
  •  Estefanía wrote:

    I've disabled VIA protocol in the server and I get the same problem.

    The name of the server is NAMESERVER\instance or IPSERVER\instance, but I've tested in both ways and it fails too.

    The firewall server is disabled, for more information. Furthermore, I can connect to Database Engine without problems with the Management Studio, but not at Analysis Services, I don't understand.

    Do you have any other suggestions?

    We have to be precise. There are 5 servers in SQL Server 2005.

    What is the name of the database server?

    What is the name of the analysis server? 

    Can  you give us an image of SQL Server Configuration Manager.

    Can you connect to the analyis server from MS registered servers?

    Can you open up the analysis server in object browser? (right click on analysis server in registered servers)


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012
    Tuesday, April 8, 2008 11:41 AM
  • If i correctly understand you, you are trying to implement a database on a remote Analysis Services server. You have created a project on one box and are trying to implement it on another box.

     

    Say you project box is named PROJECTBOX and your Analysis Services Server is names SSASServerBOX, try to give permissions for the PROJECTBOX to access SSASServerBOX.

     

    In you SSASServerBOX open Analysis services in SSMS

    Right-click on server and click properties

    go to security tab and add DOMAINNAME\PROJECTBOX$ (DomainName\MachineName$)

     

    You can do this by clicking on the Script on top. This will open the xmla for that object. Add DOMAINNAME\PROJECTBOX$. You can do this as mentioned below.

     

    Code Snippet

    <ObjectDefinition>

    <Role.......

    <ID>Administrators</ID>

    <Name>Administrators</Name>

    <Members>
     <Member>
              <Name>DOMAINNAME\PROJECTBOX$</Name>
      </Member>

    </Members>

    </Role>

    </ObjectDefinition>

     

     

     

    Hope this helps

     

    regards

    Gautham

    Tuesday, April 8, 2008 12:06 PM
    Answerer
  •  

    Sorry SQLUSA, I can't paste an image, this editor doesn't let me.
    Tuesday, April 8, 2008 1:58 PM
  • Sorry SQLUSA, I can't paste an image, this editor doesn't let me.

     

    The Analysis Server name is the same of the Database Server.

    When I open the SQL Server Configuration Manager are running two services: Analysis Services and SQL Browser.

    There are enabled only TCP/IP, Named Pipes and Shared Memory. On SQL Native Client Configuration there are all protocols enabled.

    When I connect through Remote Desktop to my server, I open SQL Server Management Studio and I can connect to Analysis Services. I open Object Browser and there is my AS Server. I open the Registered Servers window, click on the cube and there is my server running (I repeat this is working remotely in the server, doing it locally).

     

    I don't know if I've understood you, Do I have to give any type of privilege to my local computer?

     

    I must add that I can't connect to Analysis Services with my local Server Management Studio. It's strange that when I select in Server Type the option of Analysis Services, the Autentication combobox is disabled, and there is fixed the autentication of my local computer, although I don't know if it's important..., then I get this error:

     

    Code Snippet

    'Can't connect to SERVERNAME\instance.

    Additional information:

    Can't establish a connection. Make sure the server is running.

    (Microsoft.AnalysisServices.AdomdClient).

    Can't write transport connection data. Has forced the interruption of an existent connection by remote host. (System).

    ..........'

     

     

     

    Tuesday, April 8, 2008 2:25 PM
  • Great!

    Sounds like you should be working.

    Can you do this:

    1. In BIDS, open analysis services database

    2. Radio button Create New Database

    3. Enter Server name  (you can try (local)\instance

    4. Enter database name

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Edited by Kalman Toth Monday, October 1, 2012 12:57 AM
    Tuesday, April 8, 2008 2:35 PM
  • You're very fast!

     

    I guess that you refer I must create new database in my local computer, so BIDS isn't installed on the remote server.

    I've done everything which you tell me, and I can create a new database on my local PC but not on the remote server...

     

    Many thanks and forgiveness for so many annoyances!!
    Tuesday, April 8, 2008 2:56 PM
  • Yes, I can connect to the server through Remote Desktop to configure anything, but I must develop from mi local computer.

     

    Tuesday, April 8, 2008 4:25 PM
  •  

    Have anybody got any idea?
    Wednesday, April 9, 2008 9:59 AM
  • Can you start over again and give ALL pertinent details?

    It is not clear to me where you are, where is SSAS, how are you connected?

    Describe all sofware being used on  both side.

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Wednesday, April 9, 2008 12:59 PM
  • Could you try to open both TCP ports 2382 and 2383 on your remote server box and see if that succeeds?
    Refer to http://social.msdn.microsoft.com/forums/en-US/sqlgetstarted/thread/5c984c1d-8cd1-4432-9652-6f6e210a9607/

    Tuesday, October 27, 2009 4:51 PM
  • Hi everyone, I'm having the exact same problem that Estefania describes, I've tried everyting and nothing seems to work. I just want to know if anyone has a solution for this?
    Thursday, June 16, 2011 5:15 PM