SQL BI

My Experience - Troubleshooting SQL BI Stack

SQL Server Analysis Services Port (SQL 2005 / 2008)

SQL Server Analysis Services Port (SQL 2005 / 2008)

  • Comments 9

 

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

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

 

Managing Multiple Instances of Analysis Services

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

Comments
  • 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?

  • 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

  • Very useful posting.

    Although getting struck up at one point. I have created a named instance INSTANCE1 in SQL2005 for DB&SSAS. Due to security restrictions SQL Server Browser service cannot be enabled. SSAS is of major focus here. I have specified a static port for SSAS  named instance in msmdsrv.ini. I can connect to SSAS INSTANCE1 from SSMS using (local):<staticport>\INSTANCE1. Additionally i can deploy an SSAS solution from BIDS to the target database server (local):<staticport>\INSTANCE1. But I cant process the cube from BIDS. Error points to SQL Browser service not running. Is there any way processing can also succeed fm BIDS by doing config changes? If I proces the cube from SSIS package will I face the same error?

  • Processing from BIDS which is connected using Port Number is not doable because it's looking for the instance name and doesn't understand the port.

    Second question: If I process the cube from SSIS package will I face the same error?

    Yes that is doable.

    Here are the Steps:

    In a SSIS Project add Processing Task.

    Edit Connection for Analysis Services and provide server name as Server_HOSTNAME:Port or IP_ADDRSSS:Port.

    Select Objects which you want to process.

    And execute that task.

    Regards

    Karan Gulati

  • 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

  • Hi,

    The easiest way to get the port your SSAS instances are using is to look at the file %ProgramFiles%\Microsoft SQL Server\90\Shared\ASConfig\msmdredir.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.

  • 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

  • Brilliant posting - helped me massively

    Thanks

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

Page 1 of 1 (9 items)
Leave a Comment
  • Please add 5 and 3 and type the answer here:
  • Post