locked
Connecting ProClarity to non-standard AS port? RRS feed

  • Question

  • In SQL Server 2008, you can tell Analysis Services to use a non-standard TCP/IP port (via the "Port" parameter in Analysis Server Properties).

    How do I tell ProClarity Desktop Professional to connect to this port?  I've tried "<server>,<port>", "<server>:<port>", "<server><space><port>", "<server>.<port>" and none of those work.

    Can it be done?

    Thanks,
    Craig

    Monday, May 11, 2009 4:02 PM

Answers

  • Sounds like you've tried what should have worked, which is server:port#.  Can you connect using Excel's pivot table feature? 
    Microsoft ProClarity | This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, May 11, 2009 8:51 PM
  • Hi Craig,

    ProClarity connecting to AS08Server\instancename or AS08Server:port# should both work.  Using the instance name requires the SQL browser service to be running which by default listens on port 2382.  'server\instance:port' will not work - think of it as using an instancename OR port# but not both.  Whatever method you use to connect to SSAS the clients will use a random port - one to connect to the Browser service and another to connect to the SSAS port (I tested '08 and the client used 4416 and 4418).  Most firewalls have logic built in and will dynamically open the return port.  One thing I noticed is that when changing the port# you'll want to restart the SSAS service from the configuration manager or services snap-in and not from SSMS.  For some reason when I ran a service restart from within SSMS it restarted the wrong service and the port# never changed.  You can confim the port change by re-connecting to the instance and checking the port property's 'Current Value'.  If you're still seeing an error, what is the exact error message you're getting in Pro?

    Also, if you set an instance of SSAS to run on 2383 you do not need to append the port# or instancename to the servername.  You shouldn't have any problem as long as the default instance isn't running on that port or it's default (0).

    hth,
    Sean
    Microsoft ProClarity | This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, May 12, 2009 3:42 PM

All replies

  • Sounds like you've tried what should have worked, which is server:port#.  Can you connect using Excel's pivot table feature? 
    Microsoft ProClarity | This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, May 11, 2009 8:51 PM
  • OK, I can connect to AS on a SQL Server 2000 machine using server:2725 (as well as plain old <server>).  On that system, we used the default ports for AS and we only have the default AS instance.

    I'm now trying to connect to AS on a SQL Server 2008.  We configured AS with an instance name on this one.  When I try to connect to server\instance it works (from both ProClarity and Excel).  For security reasons, though, we may change that port # to be non-standard.

    When testing our current setup, though, I can't connect using server\instance:2382.  Is this the correct port I should use for this in this configuration?

    As a test, we changed the instance to use port 2383.  Since we don't have a default instance, I thought this would be OK (am I wrong on this?).  I couldn't connect via server\instance:2383, either.

    Finally, we have a firewall between our users and the AS database.  I need to know what ports to allow clients to access.  I found this article (http://support.microsoft.com/kb/832017) which looks like it would have what I need.  Unfortunately, even though the last review was March 19, 2009, there's no mention of SQL Server 2008 (or even 2005).  If we can use a non-default instance but it must be other than 2383, then will I only need to allow access to that port?

    Thanks very much,
    Craig
    Tuesday, May 12, 2009 1:02 PM
  • Craig,

     

    I am all about starting slow, I would suggest going to the SSAS machine, create a text file, rename it to anything.UDL. (Cool trick, thanks William C.) This will enable you to use the Data Link interface to test your connection to SSAS.  Start moving back to different machines to add more factors into the mix.  Like Amanda said what you did should have worked, so go to machine to make sure what you are doing is correct then move to different machine with the correct connection string.

     

    After connecting with the Data Link interface you will know that the connection string is correct.  You can open the UDL file with notepad to check it out.

     

    Reeves


    Denver, CO
    Tuesday, May 12, 2009 3:27 PM
  • Craig,

    Forgot to point out, with the above trick you can see if the provider is also installed on the machine.

     

    Reeves  


    Denver, CO
    Tuesday, May 12, 2009 3:30 PM
  • Hi Craig,

    ProClarity connecting to AS08Server\instancename or AS08Server:port# should both work.  Using the instance name requires the SQL browser service to be running which by default listens on port 2382.  'server\instance:port' will not work - think of it as using an instancename OR port# but not both.  Whatever method you use to connect to SSAS the clients will use a random port - one to connect to the Browser service and another to connect to the SSAS port (I tested '08 and the client used 4416 and 4418).  Most firewalls have logic built in and will dynamically open the return port.  One thing I noticed is that when changing the port# you'll want to restart the SSAS service from the configuration manager or services snap-in and not from SSMS.  For some reason when I ran a service restart from within SSMS it restarted the wrong service and the port# never changed.  You can confim the port change by re-connecting to the instance and checking the port property's 'Current Value'.  If you're still seeing an error, what is the exact error message you're getting in Pro?

    Also, if you set an instance of SSAS to run on 2383 you do not need to append the port# or instancename to the servername.  You shouldn't have any problem as long as the default instance isn't running on that port or it's default (0).

    hth,
    Sean
    Microsoft ProClarity | This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, May 12, 2009 3:42 PM
  • Sean,

    Thanks very much.  That cleared up a lot.

    Craig
    Wednesday, May 13, 2009 4:40 PM