Troubleshooting Analysis Services 2005 connectivity problems
After answering quite a few questions on the Microsoft forum and getting some common questions regarding connectively to analysis services 2005 it is time to have a common place for look for information about how to troubleshoot connectivity:
There are several causes for connectivity problems.
Just to remind. There are two protocols you can use to connect to Analysis Server: TCP/IP and HTTP.
Here are pointers to whitepapers on how to configure HTTP connectivity:
http://www.microsoft.com/technet/prodtechnol/sql/2005/httpssas.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/httpasws.mspx
When troubleshooting HTTP connectivity problems, first make to connect to Analysis Server using TCP/IP from IIS machine using same credentials you use for IIS virtual directory.
Client components problems.
When using client application to connect to Analysis Services 2005 you are usually relying on one of the two object models: Analysis Services 2005 OLE DB provider, or ADOMD.NET. There is another object model that is used for management of Analysis Server objects – Analysis Management Objects (AMO)
First make sure you have correct version of the client object model installed. The latest version of the client object model could be found at ( at the time of writing this article) http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en
Install latest version of the client component.
Some information about dependencies for Client components:
You don’t need to guess the dependencies for the client component you are installing. The setup for component will give you a message in case some dependency is missing:
AS 2005 OLEDB is dependent on the MSXML6.
ADOMD.NET and AMO depends on the .NET Framework 2.0 to be installed.
Firewall
If you have Firewall enabled on your machine, you will need to make sure you open TCP ports so Analysis Server can accept incoming connections.
The port numbers Analysis Server listens to are following;
2383 – port default instance of Analysis Server listens to
2382 – port SQL Browser service listens to for Analysis Services requests.
Named instance of Analysis Services will dynamically pick ports to listen on your machine. The easiest way for you to discover TCP port named instance listens is to look at the configuration file for SQL Server Browser file. Open file located on the %Program files%\Microsoft SQL Server\90\Shared\ASConfig. Open msmdredir.ini file and look at the < Instances> section in it.
For example you might see following in it;
<Instances>
<Instance>
<Name>Inst1</Name>
<Port>56178</Port>
</Instance>
</Instances>
This means your machinename\Inst1 is listening on the port 56178.
In many situations you would like to ensure your named instance is not picking port by itself but listens on pre-defined port.
For that, you can modify the Port – Analysis Server property. Make sure you restart Analysis Server and SQL Server Browser service after changing this property.
Windows Firewall
Depending on the operating system you are running windows firewall admin interface is found in different places. On Windows XP you were to go into Control Panel and click on the “Windows Firewall”.
On Windows Server 2003 you go into “Network Connections” , click on individual connection, go into Advanced Tab and there you would find Widows Firewall settings.
Non-Windows Firewalls
Many antivirus software applications nowadays will install firewalls on your machine. Please refer to particular software manufacturer documentation about opening ports for Analysis Server to use.
Security problems
Most of the problems here related to the fact that user establishing connection to Analysis Server doesn’t have sufficient privileges.
There are two stages of security checks user connection is passing;
Authentication
Authorization
Authentication means that Analysis Server can establish user identity.
Most of the problems with Authentication are because Analysis Sever cannot establish who the user connecting. For instance Analysis Server running on the machine A, user is trying to connect from machine B.
If user connecting with Domain\user credentials, machine A can recognize the user. That in the case if machine A is registed with Doman or trust relationships are established between domains.
If user connecting with local machine B credentials, machine A will to be able to recognize the user.
By default Analysis Server will not allow anonymous connections (connections coming under unrecognizable user credentials). Temporarily for troubleshooting purposes you can enable anonymous users to connect using ether SAC (Surface Area Configuration for Features) or modifying Security\RequireClientAuthentication server property.
Authorization is process where Analysis Server establishes access permissions for authenticated user.
Make sure user connecting is member of one or more security roles. Otherwise after connecting you will not see any of databases.
Other problems:
Several obvious things to try:
Make sure Analysis Server service is running;
Default name appears under name SQL Server Analysis Services (MSSQLSERVER) in Service Control Manager.
Named instances will be named as SQL Server Analysis Services (InstName).
For named instances make sure SQL Server Browser service. If SQL Server Browser service is not running or you suspect it not functioning correctly you can try and bypass it by using following format for the server name when connecting: Servername:Port. For instance Edwardm2:56178.
Make sure the account password for Analysis Server service has not expired.
Just restart Analysis Server service to verify.
Make sure Analysis Server will allow connections from other machine. Although it is not common, and by default this situation will not happen to you. But this is possible: SQL Server Surface Area Configuration (SAC) tool allows you to force Analysis Server to listen to local connections only. Please make sure to open SAC, click on “Surface Area Configuration for Services and Connections”, expand node for Analysis Services and make sure Remote connections has “Local and Remote Connections” radio button selected.
Tools for troubleshooting connectivity.
MDXSampe app.
Surprisingly the simplest client tool for troubleshooting connectivity problems I’ve found is MDXSample application shipped with SQL Server 2000.
Compile MDXSample. Copy the executable over to your AS2005 machine. You will need to copy couple more binaries along with it comctl32.ocx and msflxgrd.ocx. You will find them in windows\system32 folder on you AS2000 machine.
MDXSample is using AS2005 OLEDB.
Run MDXSample and try to see if you are able to connect to AS2005.
SQL Server Profiler.
Use Profiler to monitor Analysis Server. You should be able to Audit Login event in case user is authenticated with Analysis server. You will see username in the NTUserName column.
Session Initialize event will tell you about user able to create session. Sessions are associated with particular database, if you see session created, that means user was granted access to specific database. You will see name of the database in the DatabaseName column.
Network Tools.
You should be able to use variety of tools to see if you can access specific machine ( ping). Or to see if particular port is open ( port scanner ).
Common errors;
Some of commonly encountered error messages:
#1
No such host is known…
This error indicates you are trying to connect to non existing machine on the network, or machine is not available.
#2
No connection could be made because the target machine actively refused it.
You will receive this error message if client application was able to find server machine, but couldn’t connect to specific port. Try stopping your Analysis Server and you will receive this error next time you try to connect.
If you are trying to connect to the named instance and you are receiving this error, it means that client application was able to retrieve port information from SQLBrowser and made an attempt to connect using this port, but it couldn’t.
#3
An error occurred while named instance information was being retrieved from the SQLBrowser service on the ‘MACHINENAME’ server.
You will receive this error when trying to connect to named Analysis Server instance and client application cannot establish connection to SQLBrowser service listening on the port 2382.
#4
A valid instance named ‘MyInst’ cannot be found on the ‘MACHINENAME’ server.
This error message tells you: You were able to connect to SQLBrowser but it couldn’t find any instance name matching yours. Just to test it, use some arbitrary non-existent instance name: “localhost\1111” as your server name and you will receive this error.
Test your instance name.
Edward Melomed.
Program Manager, Analysis Services.