Showing posts with label ssas. Show all posts
Showing posts with label ssas. Show all posts

Saturday, February 25, 2012

Cannot Connect to SSAS using Reporting Services 2005

I am creating a report in Reporting Services 2005 that is using a data cube that has been created in SSAS 2000. I cannot connect to the Cube. If I preview the cube in Reporting Services, I can see the data, however, after deploying the report, I cannot view the data on the webpage. The following error is displayed :

  • An error has occurred during report processing.

  • Cannot create a connection to data source 'Conn_FR'.

  • A connection cannot be made. Ensure that the server is running.

  • No connection could be made because the target machine actively refused it

    I have also tried to connect to the analysis server using SSMS and the connection failed stating

  • No connection could be made because the target machine actively refused it

  • I read another post that said that connection from SSMS to SSAS 2000 is not supported, is this the same case for Reporting Services 2005 to SSAS 2000?

  • Since you can see the cube in the RS designer I assume that you have a problem with the security credentials in Reporting services 2005. You must supply a valid windows account in Report Manager and map that account to a role in AS2000 or AS2005.

    Regards

    Thomas

    |||

    Thanks for the reply,

    I have been set up as administrator on the server that analysis services is installed on. But still no access.

    I am still concerned that the connection between SQL 2005 and Analysis Services 2000 is not supported.

    Thanks

    |||You have to make sure that all the accounts line up with the right credentials. What account is SSRS running under? Does that account have permissions on the cube?|||

    Is the webserver and reporting services on a separate server than AS2000? If so you you will also have to consider the use of Kerberos for security and account validation.

    Check what you have been recommended to do, previously, and check Kerberos authentication after that.

    Regards

    /Thomas

    |||

    First, the SSRS and SSAS are on different servers. The user that is being used is a domain account with admin rights on both servers.

    I am not sure if this helps, but below is the error details that are beind displayed:

    ===================================

    Cannot connect to DBServer2.

    ===================================

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

    Program Location:

    at Microsoft.AnalysisServices.AdomdClient.XmlaClient.GetTcpClient(ConnectionInfo connectionInfo)

    at Microsoft.AnalysisServices.AdomdClient.XmlaClient.OpenTcpConnection(ConnectionInfo connectionInfo)

    at Microsoft.AnalysisServices.AdomdClient.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession)

    at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.AdomdConnection.IXmlaClientProviderEx.ConnectXmla()

    at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP)

    at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open()

    at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)

    at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

    ===================================

    No connection could be made because the target machine actively refused it (System)

    Program Location:

    at System.Net.Sockets.TcpClient..ctor(String hostname, Int32 port)

    at Microsoft.AnalysisServices.AdomdClient.XmlaClient.GetTcpClientByAddress(String hostName, Int32 port)

    at Microsoft.AnalysisServices.AdomdClient.XmlaClient.GetTcpClient(ConnectionInfo connectionInfo)

    Thanks for the feedback thus far...

    |||

    Check out the connection troubleshooting guide: http://www.sqljunkies.com/WebLog/edwardm/

    Edward.
    --
    This posting is provided "AS IS" with no warranties, and confers no rights.

  • Sunday, February 12, 2012

    Cannot connect to AS2005 remotely using SSMS or BIDS

    I really need help to understand why I can't connect to SSAS from a remote machine running SSMS or BIDS. Following is my setup:

    SQLServer 2005 on a development server
    Server Name: SQLWIN-1
    Named Instance: SQLWIN1

    If

    I am running management studio locally from the server machine I can

    connect to SSAS fine. However, when running from a remote machine the

    connection fails with the following errors:


    TITLE: Connect to Server

    Cannot connect to SQLWIN-1\SQLWIN1.

    ADDITIONAL INFORMATION:
    A

    connection cannot be made to redirector. Ensure that 'SQL Browser'

    service is running. (Microsoft.AnalysisServices.AdomdClient)

    No connection could be made because the target machine actively refused it (System)

    What I have already verified & tried:
    SQL Browser service is running under NT Authority/Network Service account.
    SSAS properties has local and remote connections radio button checked.
    I even unabled anonymous login for SSAS on SQLWIN1

    If I change the SQL Browser to run under the local system account, i get a slightly different error as follows:

    TITLE: Connect to Server

    Cannot connect to SQLWIN-1\SQLWIN1.

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

    Unable

    to read data from the transport connection: An established connection

    was aborted by the software in your host machine. (System)

    An established connection was aborted by the software in your host machine (System)

    When connecting from another machine I am not able to change the Windows Authentication username and I know the default username doesn't exist on the server.

    I am kinda stuck and don't know what else I can try.

    Any help is appreciated. Thanks

    Hello. Check this. Under programs, SQL Server 2005 and configuration tools you will find SQL Server Surface Area Configuration.

    Start this and check if remote connections are allowed for Analysis Services.

    If this setting is correct you need to check if a firewall is activated on the server with SSAS 2005 or if the services packs are not the same on the server and your remote client.

    HTH

    Thomas Ivarsson

    |||

    http://www.sqljunkies.com/WebLog/edwardm/archive/2006/05/26/21447.aspx

    Also try running SQL Browser under domain account.

    Edward Melomed.
    --
    This posting is provided "AS IS" with no warranties, and confers no rights.

    |||Thomas,

    I have run Surface Area configuration and have remote connections enabled for SSAS.

    Regarding Service packs, I installed SQL Server 2005 SP1 on the server which is running Windows 2003 Server. My client machine is running XP professional. Are there any client patches that I need to install for SSMS?

    Also, the server does not use domain authentication. We just have this one server which is windows and it has local user authentication.

    Also, I notice that when connecting to SSAS from management studio, you don't have the option to change the authentication type or the username & password. They are greyed out. By default, I see a username in the dialog box that I know does not exist or is not going to be valid on the server. Is there a way to change this?

    Thanks,
    Arpan Shah|||

    I do not think that you can change the autentication for SSAS 2005 in management studio. SSAS only support windows athentication. SQL Server both support SQL Server autentication and Windows autentication.

    You will need to run SP1 on your client as well if you have BI-Dev Studio on that machine.

    HTH

    Thomas Ivarsson

    |||Edward,

    What account are you referring to as domain account? Ther server is not associated with any domain. It just has local administrator accounts.

    The service is running under the NTAUTHORITY/Network Service account. I have tried running it under the Local System account as well. The service listens for DB connections and connects them properly. However, SSAS connections don't work remotely.

    When i do a netstat on the server, I can see the ports associated with the Analysis server process id. I have a named instance of Analysis server, so its using a dynamic port. When I try to telnet to the server and analysis server dynamic port, it establishes a connection. So Network connectivity or firewall is not an issue.

    However, when i try to use the server:port for SSAS in the SSMS connection dialog window it doesnt work. By default, the windows user account selected in the dialog box does not exist on the server. There is no way to change this so I have enabled anonymous connections as well. Still no luck..

    Any help or insight is appreciated...

    Thanks,
    Arpan|||

    make sure DB server has the same username/password in Windows that you used to logon to the remote machine.