Partilhar via

SQL Server Analysis Services Port (SQL 2005 / 2008)

  • Artigo

Default Port:2383

You can change default Port for AS in msmdsrv.ini file of the Instance.

Port Used by SQL Server Browser Services for SSAS is 2382

How to determine on which port AS is running?

-Open Task Manager Get the PID for msmdsrv.exe

-Open command Prompt type netstat /ao >>c:\output.txt

-Look for the PID in output file and corresponding TCP IP:Port information for same PID

-To confirm whether you got right Port number, Open Management Studio and connect to AS using IP Address:Port Number (192.168.1.1:5585)

How to change Port for AS Services (2005 & 2008)

-In Clustered environment, AS can listen only on Port Number 2383.

For standalone Default or Named Instance you can change port number in msmdsrv.ini file (<Port>0</Port>)

Caveat:

SQL Server Bowser is enumerating all SSAS instances and will provide connection information for them.

Imp: Not any default instance information is handled by the SQL Server Browser which means, if you’re using a default instance with an alternate port, you will need to provide it to the client application; SQL Browser will not be able to forward it at connection time.

SQL Server Browser will only provide the connection information for visible SSAS named instance.

How AS Port is Determined in Standalone or Clustered Environment?

What if I have multiple nic card on a box, how exactly SSAS will Listen?

-SSAS as a Standalone Instance

Default instance

SSAS will start listening on all IP addresses of the box using the port specified in the msmdsrv.ini file or the default port (2383) if “0” specified.

Named instance

SSAS will start listening on all IP addresses of the box using the port specified in the msmdsrv.ini file or an available port provided by the system if “0” specified.

-SSAS as a Clustered Instance (Default or Named Instance)

SSAS will start listening on all IP addresses of the cluster group using the default port (2383). Any alternate port configuration is ignored.

Imp: In clustered environment AS can listen only on Port Number 2383, manual setting in msmdsrv.ini file will be ignored.

On same box you can’t run standalone and clustered instance:

Related Links:

How to: Configure Windows Firewall for Analysis Services Access

https://msdn.microsoft.com/en-us/library/ms174937.aspx

Managing Multiple Instances of Analysis Services

https://msdn.microsoft.com/en-us/library/ms174906.aspx

Comments

  • Anonymous
    November 04, 2009
    Great post, appreciate it.  Regarding:  For standalone Default or Named Instance you can change port number in msmdsrv.ini file (<Port>0</Port>). How do you know which port to pick?  Can you just pick anything that is not being used, such as 1067?

  • Anonymous
    November 09, 2009
    Yes, in Standalone Environment you can use any un-used port. Note,For defulat Instance if you are chaning the port then you need to specify the server name with port number in the connection string of application because by desing browser service will not resolve any port change for default instnace. For finding used port you can give - netstat -ao >>c:op.txt find 1067, if its not used then you are free to use it. Pls. let me know if you need furhter clarifications. Karan

  • Anonymous
    February 01, 2010
    The comment has been removed

  • Anonymous
    February 02, 2010
    The comment has been removed

  • Anonymous
    March 14, 2010
    Excellent... More usefull then overall docs :-) I was suc..ing from a couple of hours before I found your post and my problem are solved in 30 seconds. Thank you very, very, very much

  • Anonymous
    October 03, 2010
    Hi, The easiest way to get the port your SSAS instances are using is to look at the file %ProgramFiles%Microsoft SQL Server90SharedASConfigmsmdredir.ini. Check the tag "<Instances>". I use the following convention to assing a port to SQL Server and Analysis Services instances:

  • SS2005 intance 1: 50000 + <default SQL instance> + <instance #>.

  • SS2005 intance 2: 50000 + <default SQL instance> + <instance #>.

  • AS2005 intance 1: 50000 + <default AS instance> + <instance #>.

  • AS2005 intance 2: 50000 + <default AS instance> + <instance #>.

  • Etc..

  • SS2008 intance 1: 60000 + <default SQL instance> + <instance #>.

  • SS2008 intance 2: 60000 + <default SQL instance> + <instance #>.

  • AS2008 intance 1: 60000 + <default AS instance> + <instance #>.

  • AS2008 intance 2: 60000 + <default AS instance> + <instance #>.

  • Etc.. Regards, Tiago.

  • Anonymous
    October 24, 2011
    hi Karan - I have a problem in makign SQL SSAS to listen in all the IPs of cluster group. i have posted my issue here. social.microsoft.com/.../a64f7b60-5400-4b6b-8338-acf53a8c5946 Any thoughts pls. -Ravikumar

  • Anonymous
    July 04, 2012
    Brilliant posting - helped me massively Thanks

  • Anonymous
    July 19, 2012
    Thanks for writing this up. It helped me solve a problem I had connecting to a new installation of SSAS!