Resolving Common Connectivity Issues in SQL Server 2005 Analysis Services Connectivity Scenarios
Writer: Carl Rabeler
Technical Reviewer: Craig Utley
Applies to: SQL Server 2005 Service Pack 2
Summary: This article identifies the top 10 connectivity scenarios in which users may experience connectivity problems and explores error messages generated by common client applications. It shows you how to quickly isolate each error, and then provides the information you need to resolve the underlying issue that caused the particular error message.
On This Page
Introduction
Methodology
TCP/IP Connectivity
HTTP Connectivity
Conclusion
Introduction
When an application is unable to connect to a Microsoft® SQL Server™ 2005 Analysis Services instance, the error message that is returned may help you isolate and then resolve the problem. However, error messages do not always provide you with sufficient information to resolve the problem. Also, the same error message can be generated by multiple underlying problems. Furthermore, the error messages generated by different applications for the same problem are different; in this paper you will discover that using a secondary application may help you isolate the source of the connectivity problem. Finally, information in the application log and in trace files can also expedite the problem resolution process.
Note For issues related to administrator connectivity to SQL Server 2005 from Windows® Vista™, see How to: Connect to SQL Server from Windows Vista in SQL Server Books Online.
Note In general, this article assumes that you have applied SQL Server 2005 Service Pack 2 on the Analysis Services instance and on each client computer.
For the purposes of this article, the following client applications were used to demonstrate a range of error messages that the reader might see in their own environment and to illustrate the different types of error messages you can receive from the same underlying problem:
SQL Server Management Studio
UDL file
Microsoft Excel® 2007
Microsoft Excel 2003
Simple Sample ADOMD ClientAccess application. This ADOMD.NET sample application is part of the SQL Server 2005 Samples and Sample Databases (February 2007) .msi file and is available from the Microsoft Download Center.
Note: For specific connectivity issues with Microsoft Proclarity that are not covered in this article, go to Microsoft Help and Support and search for 'ProClarity'.
Methodology
As a general practice, begin with the simple possibilities before moving on to more complex problem areas—simple problems are easy to overlook and take very little time to analyze. Stay calm, be methodical, eliminate variables, and make only one configuration change at a time. Whenever an error message occurs, record the exact error message—it is frequently useful to verify that you can repeat the precise error message. If an error message cannot be repeated consistently, you probably have not eliminated all of the variables in the process, or a change that you have made has not fully taken effect. When in doubt, consider restarting services or rebooting a server until the error message reoccurs consistently.
TCP/IP Connectivity
In this section, we look at six TCP/IP error conditions that result in connectivity errors when a client application that is running within a trusted domain environment attempts to connect directly to a Microsoft SQL Server 2005 Analysis Services instance on the local intranet by using TCP/IP.
Note HTTP connectivity issues are covered in HTTP Connectivity later in this document.
Error Condition 1: Unknown Host or Instance Specified in Connection String
The client application attempts to use TCP/IP to connect to a SQL Server 2005 Analysis Services instance by specifying a computer name or an instance name that does not exist or is not reachable from the client computer.
Errors Observed
The client application receives an error message similar to one in the following table.
Client Application |
Instance Type |
Error Message |
|---|---|---|
UDL file |
Default |
Test connection failed because of an error in initializing provider. The following system error occurred: No such host is known. |
UDL file |
Named |
Test connection failed because of an error in initializing provider. Errors in the OLE DB provider. A valid instance named '<instance_name>' cannot be found on the '<server_name>' server. |
UDL file |
Named |
Test connection failed because of an error in initializing provider. Errors in the OLE DB provider. Could not connect to the redirector. Ensure that the SQL Server Browser service is running and the '<server_name>' server. |
Microsoft Excel 2007 / Microsoft Excel 2003 |
Default |
The following system error occurred: No such host is known. |
Microsoft Excel 2007 / Microsoft Excel 2003 |
Named |
Errors in the OLE DB provider. A valid instance named '<instance_name>' cannot be found on the '<server_name>' server. |
Microsoft Excel 2007 / Microsoft Excel 2003 |
Named |
Errors in the OLE DB provider. Could not connect to the redirector. Ensure that the SQL Server Browser service is running and the '<server_name>' server. |
SQL Server Management Studio |
Default |
Cannot connect to <server_name>. A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient) No such host is known (System) |
SQL Server Management Studio |
Named |
Cannot connect to <server_name>\<instance_name>. The '<instance_name>' was not found on the '<server_name>' server. (Microsoft.AnalysisServices.AdomdClient) |
SQL Server Management Studio |
Named |
Cannot connect to <server_name>\<instance_name>. A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. (Microsoft.AnalysisServices.AdomdClient) No such host is known (System) |
Simple Sample ADOMD ClientAccess |
Default |
Connection to <server_name> server is not ready or connectivity is broken. Full text message received follows: A connection cannot be made. Ensure that the server is running. |
Simple Sample ADOMD ClientAccess |
Named |
Connection to <server_name>\<instance_name> server is not ready or connectivity is broken. Full text message received follows: A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. |
Error Resolution Recommendations
These error messages generally indicate that either the computer or the instance to which the client application is attempting to connect does not exist or cannot be reached from the computer on which the client application is running.
Tip In the case of a named instance, the error message does not always enable you to determine if the problem is related to connectivity to the computer hosting the instance or connectivity to the instance itself. In this case, first verify that the specified computer is reachable before you attempt to resolve the connectivity problem to the named instance.
The following questions will generally help you isolate and then resolve the cause of an unknown host or instance error message.
Is there a typographical or other error in the connection string?
If the client application permits the user to enter some or all of the connection string interactively, verify that the connection string is accurate and is pointing to the correct and valid server and instance name. Simple typographical errors in a connection string are easily overlooked as a possible cause of these error messages. Pointing to the wrong server also happens on occasion and can lead to a waste of troubleshooting time. Verifying the connection string first can sometimes save you a lot of time looking at other issues that are not the problem.
Is the user able to connect to any resources on the network from the computer?
If the user is unable to connect to any network resources from the client computer, these error messages have nothing directly to do with Analysis Services. To begin troubleshooting general network connectivity issues, use the IPCONFIG command-line utility to verify that a valid IP address has been obtained by the computer. You may need to issue an IPCONFIG/RELEASE and then an IPCONFIG/RENEW command from a command prompt. For more information, see IPCONFIG Diagnostic Utility: Parameters and Usage.
Are there name resolution issues on the local computer or on the network?
If the client application cannot connect to Analysis Services by using the server (or server\instance) name, there may be name resolution problems on the network. Try connecting to the Analysis Services instance by using the IP address of the computer that is hosting Analysis Services rather than using the server name itself (xxx.xxx.xxx.xxx or xxx.xxx.xxx.xxx\instance_name).
Tip If you are connecting to an Analysis Services instance on the same computer as the client application, try using the server name or IP address rather than using localhost or (local).
You can also use the Ping command-line utility to isolate the problem. Try to ping the computer that is hosting Analysis Services by using the computer’s hostname, fully qualified domain name, and IP address. You can use the Ping –a parameter to return the fully qualified domain name of a computer. For more information about the parameters of the Ping utility, see Pingon Microsoft Windows XP Product Documentation.
If you can ping the computer only by its IP address, you have isolated the source of the connectivity problem. Resolve the name resolution issue and the connectivity issue will go away.
Tip To begin resolving a name resolution issue, verify that there are no incorrect entries in the hosts or lmhosts files on the local computer. These files are located in the ..\system32\drivers\etc\ folder on the client computer.
Can a connection be made from some computers and not other computers (with the same user account)?
If the client application running on some computers can connect to the Analysis Services instance while the same client application running on other computers cannot connect, determine the differences between the client computers that can connect and those that cannot. These differences can include different operating systems, client components, APIs, Windows user accounts, subnets, firewalls, DNS servers, WINS servers, or domain controllers. Any of these issues can cause a connectivity problem that generates these error messages. After you find the key difference, you can resolve the problem.
Error Condition 2: Connection Attempt is Refused by the Analysis Services Instance
The client application attempts to use TCP/IP to connect to an Analysis Services instance on a computer that exists and is reachable, but the connection attempt is denied for reasons not related to permissions in the Analysis Services instance.
Note This scenario assumes that the SQL Server Browser service is running if the connection attempt is to a named instance. Error messages related to the SQL Server Browser service are discussed later in this article.
Errors Observed
The user receives an error message similar to one in the following table.
Client Application |
Instance Type |
Error Message |
|---|---|---|
UDL file |
Default / Named |
Test connection failed because of an error in initializing provider. The following system error occurred: No connection could be made because the target machine actively refused it. |
Microsoft Excel 2007/ Excel 2003 |
Default / Named |
The following system error occurred: No connection could be made because the target machine actively refused it. |
SQL Server Management Studio |
Default |
Cannot connect to <server_name> A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient) No connection could be made because the target machine actively refused it (System) |
SQL Server Management Studio |
Named |
Cannot connect to <server_name>\<instance_name> A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient) No connection could be made because the target machine actively refused it (System) |
Simple Sample ADOMD ClientAccess |
Default/ Named |
Connection to <server_name> server is not ready or connectivity is broken. Full text message received follows: A connection cannot be made. Ensure that the server is running. |
Simple Sample ADOMD ClientAccess |
Named |
Connection to <server_name>\<instance_name> server is not ready or connectivity is broken. Full text message received follows: A connection cannot be made. Ensure that the server is running. |
Error Resolution Recommendations
These errors generally indicate that the specified Analysis Services instance is not running or is not installed on the specified computer. The following questions will generally help you isolate and then resolve the cause of a connection refused error message.
Is the SQL Server Analysis Services service installed and running?
Verify the state of the SQL Server Analysis Services service for the specified instance and, if needed, start this service. (If the service is in an abnormal state, you may have to restart—such as when processor and memory utilization are low and performance monitor counters are not changing.) You can use Computer Management, Services, SQL Server Configuration Manager, SQL Server Management Studio, or the NET START command prompt utility to accomplish this task.
Note When attempting to connect by using localhost or (local) in the connection string and Analysis Services is not installed on the local computer, you will also receive this error message.
If you cannot start the SQL Server Analysis Services service, review the system and application logs in Event Viewer to determine the reason why this service will not start (or remain started). The following two reasons are common scenarios that may cause the failure of the Analysis Services instance to start.
Log on failure: If a log on failure is detected, verify that the service account password has not expired or changed. If it has, you must change the password for this service by using either SQL Server Configuration Manager (preferred) or Computer Management or Services before you can restart the service. If this solves the problem, consider using a dedicated service account and set its password to never expire.
Note If the SQL Server Analysis Services service has been running for some period of time, the expiration or change will not be detected until the service is restarted. The password change or expiration may have occurred some time in the past.
Access denied: If you receive an access denied error message, the service account may have been changed to a non-administrator account by using Computer Management or Services. If the service account is changed by using Computer Management or Services rather than SQL Server Configuration Manager, access to the encryption key file (CryptKey.bin in the ..\Data folder) and other files and folders is not automatically granted to the new service account. To solve this problem, you can temporarily change the service account to an administrator account (such as Local System) and start the SQL Server Analysis Services service. After this service starts successfully, you can then use the SQL Server Configuration Manager to change the account to the appropriate non-administrator account.
Can you connect to the Analysis Services instance on the local computer?
If the SQL Server Analysis Services service for the specified instance is running and you still cannot connect, use the SQL Server Surface Area Configuration utility to verify that Analysis Services is configured to accept remote connections. Analysis Services can be configured to accept only local connections—connection attempts by remote clients are refused.
Note The Developer and Evaluation editions are configured, by default, to accept only local connections. All other editions that include Analysis Services are configured, by default, to accept remote connections.
Error Condition 3: 9.0 OLE DB Provider Not Installed or Improperly Registered on Client
The client application attempts to use TCP/IP to connect to a SQL Server 2005 Analysis Services instance on a computer that exists and is reachable, but cannot connect to a specific Analysis Services instance on that computer due to problems with the OLE DB provider used for the connection attempt.
Important To connect to a SQL Server 2005 Analysis Services instance using an OLE DB provider, Microsoft Core XML Services (MSXML) 6.0 and the Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB provider must be installed on the client computer. The most recent version of this provider can be downloaded from the Microsoft Download Center as part of the Feature Pack for Microsoft SQL Server 2005, February 2007 download. When the 9.0 OLE DB provider is installed, it registers itself as the default provider. The 9.0 OLE DB provider will fall back to try the 8.0 or 7.0 provider when connecting to an instance of Analysis Services if the 9.0 provider connection fails.
Tip When you have both the 9.0 and 8.0 OLE DB providers installed, you can use the ConnectTo parameter in the connection string to force the use of the 8.0 provider to improve the speed with which a client connects to SQL Server 2000 Analysis Services.
Note ADOMD.NET can also be used to connect to SQL Server 2005 Analysis Services, and does not require that the 9.0 OLE DB provider be installed on the client computer. Both the ADOMD.NET 8.0 and the ADOMD.NET 9.0 providers work against SQL Server 2005 Analysis Services (as well as against SQL Server 2000 Analysis Services). If both are installed, the ADOMD.NET 9.0 provider is used when connecting to SQL Server 2005 Analysis Services. If only the ADOMD.NET 8.0 provider is installed, this provider connects using XMLA.
Errors Observed
The client application receives an error message similar to one in the following table.
Client Application |
Instance Type |
Error Message |
|---|---|---|
UDL file |
Default |
Test connection failed because of an error initializing provider. Cannot connect to the server '<server_name>'. The server is either not started or too busy. |
UDL file |
Named Instance |
Test connection failed because of an error initializing provider. Unable to connect to the Analysis server. The server name '<server_name>\<instance_name>' was not found. Please verify that the name you entered is correct, and then try again. |
Microsoft Excel 2007 |
Default |
Unable to open connection. Cannot connect to server '<server_name>'. OLAP server error: The operation requested failed due to network problems. |
Microsoft Excel 2007 |
Named |
Unable to open connection. Cannot connect to server '<server_name>\<instance_name>'. OLAP server error: The operation requested failed due to network problems. |
Microsoft Excel 2003 |
Default |
Unable to open connection. Cannot connect to server '<server_name>'. The server is either not started or too busy. |
Microsoft Excel 2003 |
Named |
Unable to open connection. Unable to connect to the Analysis Server. The server name '<server_name>\<instance_name>' was not found. Please verify that the name you entered is correct, and then try again. |
Error Resolution Recommendations
These messages are generally raised by the SQL Server 2000 Analysis Services 8.0 OLE DB provider because the 9.0 OLE DB Provider is not installed, is not properly registered, or the client is specifying the incorrect provider in the connection string. This scenario typically occurs with clients that used to connect successfully to a SQL Server 2000 Analysis Services instance and then cannot connect successfully to a SQL Server 2005 Analysis Services instance after an upgrade or a migration of the Analysis Services cube.
The following questions will generally help you isolate and then resolve the cause of such error messages.
Is the 9.0 OLE DB provider installed on the client computer?
To determine if the 9.0 OLE DB provider is installed on the client computer, you can use one or more of the following methods:
In Control Panel, open Add or Remove Programs. Verify that Microsoft SQL Server 2005 Analysis Services 9.0 OLEDB Provider is in the list of currently installed programs.
Create a UDL file (an empty file with a UDL extension—make sure that extensions for known file types are not hidden). Open the UDL file and verify that the Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider is in the list of OLE DB Providers on the Provider tab.
Open the Windows registry (using Regedit.exe or Regedt32.exe) and verify that the MSOLAP.3 registry key exists in the HKEY_CLASSES_ROOT node.
Using Microsoft Excel 2003, verify that the Microsoft OLE DB Provider for Analysis Services 9.0 option appears in the OLAP provider list box.
Using Microsoft Excel 2007, verify that the Microsoft OLE DB Provider for Analysis Services 9.0 option appears in the OLAP provider list box when selecting an OLAP cube data source to retrieve data from Microsoft Query (the installed OLE DB providers are not visible when you select Analysis Services as a data source).
If the 9.0 OLE DB provider is not installed, download and install Microsoft Core XML Services (MSXML) 6.0 (if necessary—you are prompted if this is necessary). Then install the Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider. Both of these are available from the Microsoft Download Center.
Can you connect to Analysis Services from the client computer by using a UDL file?
To determine if the 9.0 OLE DB provider is properly registered and that the problem is specific to a certain application, use the 9.0 OLE DB provider to connect to a SQL Server 2005 Analysis Services instance by using a UDL file. To accomplish this:
Create an empty file with an extension of UDL. (Make sure that extensions for known file types are not hidden.)
Open this UDL file and then select Microsoft OLE DB Provider for Analysis Services 9.0 as the OLE DB provider on the Provider tab.
in the Data Source text box, specify a valid server and instance name and then click Test (or attempt to view the available catalogs).
If this test succeeds (or a list of databases is visible), the problem is most likely the connection string used by the client application or the registration of the 9.0 OLE DB provider as the default OLE DB provider.
Is the 9.0 OLE DB provider properly registered?
To determine if the 9.0 OLE DB provider is properly registered on a client computer, open the Windows registry (using regedit.exe or regedt32.exe) and verify that the value for the HKEY_CLASSES_ROOT\MSOLAP\CurVer registry key is MSOLAP.3. If the MSOLAP.3 registry key exists, but it is not reflected in the CurVer key, the 9.0 OLE DB provider is not registered as the default OLE DB provider. You can either modify the CurVer key directly or re-register the 9.0 provider to update this key. To re-register the 9.0 OLE DB provider, you can use the following command:
Regsvr32 "%ProgramFiles%\common files\system\Ole Db\msolap.dll "
Note Registering the Microsoft OLE DB Provider for Analysis Services 8.0 after the OLE DB 9.0 provider is installed is a frequent cause of this condition. There are a number of programs that install the 8.0 OLE DB provider automatically if it is not detected during installation.
What provider is specified in the connection string sent by the client?
If the 9.0 OLE DB provider is installed and properly registered, you may also see these error messages if the connection string does not specify "Provider=MSOLAP.3". For example, older applications may have "Provider=MSOLAP.2" hard coded into the connection string. To solve this problem, change the connection string in the client application to "Provider=MSOLAP.3". If the application still fails, it probably indicates that the 9.0 OLE DB provider is not registered as the default provider.
Note If you are using certain applications, such an older version of Office Web Components (OWC), that require that you to specify "Provider=MSOLAP" rather than "Provider=MSOLAP.3" in the connection string, you will receive a permission denied or object does not exist error (mentioning a data mining model) when attempting to access an Analysis Services 2005 instance if the 9.0 OLE DB provider is not installed or is not registered as the default 9.0 OLE DB provider.
Tip: To change the OLE DB provider used by ProClarity Desktop Professional, on the File menu, click Options, and then on the OLAP Provider tab, click Change Provider.
Error Condition 4: Insufficient User Permissions in Analysis Services Instance
The client application attempts to use TCP/IP to connect to a SQL Server 2005 Analysis Services instance on a computer that exists, is reachable, on which SQL Server 2005 Analysis Services is installed and running, but the client application is unable to access any databases within the Analysis Services instance after connecting, due to insufficient permissions within the specified instance.
Important Users can access database objects if they are either an administrator of the Analysis Services instance or belong to a role that has permission to objects within a particular database. A user has server-wide administrator permissions only if he or she is a member of the server role within Analysis Services.
A user can view the definitions of database objects in a client application, such as in Object Explorer in SQL Server Management Studio, only if the user has Read Definition permissions to objects within the database.
Errors Observed
The user experiences the following behavior when connecting with a client application.
Client Application |
Error Message |
|---|---|
UDL file |
No error message is generated, but the client application is unable to access the selected database on the Analysis Services 2005 instance. If the user has no permissions to access any database on the Analysis Services 2005 instance, no databases are visible. |
Microsoft Excel 2007 |
The Data Connection Wizard cannot obtain a list of databases from the specified data source. |
Microsoft Excel 2003 |
No error message is generated, but the client application is unable to access the selected database on the Analysis Services 2005 instance. If the user account has no permissions to access any database on the Analysis Services 2005 instance, no databases are visible in the Multidimensional Connection 9.0 dialog box. |
SQL Server Management Studio |
No error message is received when the user connects, but no databases are visible in the Databases node in Object Explorer or no databases are visible when opening an MDX query pane. |
Simple Sample ADOMD ClientAccess |
No error message is received when the client application connects, but the selected database on the Analysis Services 2005 instance is not visible. If the user account has no permissions to access any database on the Analysis Services 2005 instance, no databases are visible. |
The Audit Login event in the SQL Server profiler trace shows the connected user in the NTUserName column in conjunction with a successful login event.
Error Resolution Recommendation
This observed behavior occurs when connecting to an Analysis Services instance and the connecting user has no permissions within any database on the Analysis Services instance. Verify that the connectivity problem is related to insufficient permissions by connecting to the Analysis Services instance with an administrator account or with some other user account that you know has permissions to objects within the Analysis Services instance. If you can connect and view database objects using another account, connect to the Analysis Services instance with an administrator account by using SQL Server Management Studio and then verify that the user has the necessary permissions within at least one database in the Analysis Services instance.
Note If the user can connect to a database and view database objects by using a client application such as a UDL file or Microsoft Excel, but cannot view objects in SQL Server Management Studio’s Object Explorer window, the user has Read permissions but not Read Definition permissions.
Tip When attempting to use a client application to connect from a computer in a non-trusted domain or on a stand-alone computer to Analysis Services, the NTUserName column in the SQL Server Profiler trace displays Anonymous Logon for the Audit Event Login event in SQL Server Profiler. The client will receive the following error: An error was encountered in the transport layer.
However, if the user name and password match a valid local user account on the local computer that is hosting the Analysis Services instance, the user can connect successfully because the user will be authenticated on the Analysis Services computer by using the local user account with the matching credentials. If you have a manageable number of users who connect from computers in non-trusted domains or from stand-alone computers, you might consider using this technique to allow them to connect to an Analysis Services instance directly without authenticating at the domain level. Simply add these users to one or more roles within Analysis Services with the desired permissions. However, this technique is not a security best practice and should be avoided whenever possible.
Error Condition 5: SQL Server Browser Service Not Running on Analysis Services Computer
The client application attempts to use TCP/IP to connect to a SQL Server 2005 Analysis Services named instance on a computer that exists, is reachable, on which the specified instance of SQL Server 2005 Analysis Services is installed and running, but the client application is unable to connect to the named instance due to a SQL Server Browser service error.
Important The SQL Server Browser service enables client applications to connect to a named instance of Analysis Services by specifying the name of the instance rather than the TCP port number on which that instance is listening. By default, each named instance of Analysis Services is configured to use a dynamically assigned TCP port. This port can change each time a named instance of Analysis Services starts. The SQL Server Browser service enables clients to connect to a named instance without knowing the port number and not caring that the port number may have changed since the last time they connected.
The SQL Server Browser service records the port number on which each named instance is running on the computer in the Msmdredir.ini file in the ..\90\Shared\ASConfig folder (it records this the first time a user attempts to connect to the named instance, not when the named instance starts) and uses this information to direct client requests to connect to the named instance to the appropriate TCP port. To work properly, the SQL Server Browser service must run under a security account that has local administrator rights, such as the local system account. If a named instance is installed, the SQL Server Browser service is, by default, configured to start automatically.
Tip You can configure a named instance to run under a specified port by changing the Port property for the Analysis Services instance to a specified value by using SQL Server Management Studio (this is an advanced property) or by editing the Msmdsrv.ini file directly (in the ../OLAP/Config folder). A value of zero (the default value) means that Analysis Services dynamically assigns the port at startup.
Errors Observed
The user receives an error message similar to one in the following table.
Client Application |
Error Message |
|---|---|
UDL file |
Test connection failed because of an error in initializing provider. Errors in the OLE DB Provider. Could not connect to the redirector. Ensure that the SQLBrowser service is running on the '<server_name>' server. |
Microsoft Excel 2007 / Microsoft Excel 2003 |
Errors in the OLE DB Provider. Could not connect to the redirector. Ensure that the SQLBrowser service is running on the <server_name> server. |
SQL Server Management Studio |
Cannot connect to <server_name>\<instance_name> 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) |
Simple Sample ADOMD ClientAccess |
Connection to <server_name>\<instance_name> server is not ready or connectivity is broken. Full text message received follows: A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. |
Error Resolution Recommendations
These errors are generally raised if the SQL Server Browser service is not running. The following questions will generally help you isolate and then resolve the cause of SQL Server Browser service error messages.
Is the SQL Server Browser service running?
Verify that the SQL Server Browser service is running and is configured to start automatically. To accomplish this, use either SQL Server Configuration Manager or IIS Computer Management or Services. If it is not running, start it and, if necessary, configure the SQL Server Browser service to start automatically.
Is the SQL Server Browser service running under an administrator account?
If the SQL Server Browser service is running and you are receiving these error messages, verify that the SQL Server Browser service is running under an account with administrator rights on the local computer—such as the local system account. To accomplish this, use either SQL Server Configuration Manager or Computer Management or Services. If it is not, change this service to run under an appropriate account and then restart this service before attempting to reconnect.
Note You should not run the SQL Server Browser service under the Local Service or Network Services accounts.
Can the client application connect by specifying the port name directly?
If the SQL Server Browser service is running, running under an administrator account, and you are still receiving these error messages, you can bypass the SQL Server Browser service and connect directly to a named instance (or a default instance running on a nonstandard port) by specifying the port number in the connection string. Use the following format - <Server_Name>:<Port>, for example, Serverx:1500.
Note When connecting to a named instance by specifying a port number, neither SQL Server Management Studio nor Business Intelligence Development Studio can make a secondary connection to the database to perform certain tasks. These include running the Usage-Based Optimization Wizard from either SQL Server Management Studio or Business Intelligence Development Studio and browsing a cube from Business Intelligence Development Studio when connecting in direct-connect mode.
Error Condition 6: Ports Not Open on Firewall on Analysis Services Computer
The client application attempts to use TCP/IP to connect to a SQL Server 2005 Analysis Services instance on a computer that exists, is reachable, on which SQL Server 2005 Analysis Services is installed and running, but the client application is unable to connect.
Important Unless you specify a different TCP port, a default instance of SQL Server 2005 Analysis Services listens on TCP port 2383. The SQL Server Browser service listens on TCP port 2382 and forwards all connection requests for a named instance to the TCP port on which a specified named instance is listening. The TCP port on which the named instance is listening must also be open. To ensure that you open the appropriate port for a named instance, you can configure a named instance to listen on a specified port rather than allowing the TCP port to be assigned automatically at startup (a value of zero means that the TCP port is assigned automatically). To do this, modify the Port property for the named instance by using SQL Server Management Studio or by editing the Msmdsrv.ini file directly (in the ../OLAP/Config folder for the instance).
Note If you are connecting to the Analysis Services instance via HTTP and Internet Information Services, you will generally need to open TCP port 80 (or TCP port 443 for HTTPS).
Tip You may also need to use Network Monitor to debug firewall issues. For more information on using Network Monitor, see How to use Network Monitor to capture network traffic.
Errors Observed
The user receives an error message similar to one in the following table.
Client Application |
Instance Type |
Error Message |
|---|---|---|
UDL file |
Default |
Test connection failed because of an error in initializing provider. The following system error occurred: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. . |
UDL file |
Named |
Test connection failed because of an error in initializing provider. Errors in the OLE DB Provider. Could not connect to the redirector. Ensure that the SQLBrowser service is running on the '<server_name>' server. |
Microsoft Excel 2007 / Microsoft Excel 2003 |
Default |
The following system error occurred: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. |
Microsoft Excel 2007 / Microsoft Excel 2003 |
Named |
Errors in the OLE DB Provider. Could not connect to the redirector. Ensure that the SQLBrowser service is running on the '<server_name>' server. |
SQL Server Management Studio |
Default |
Cannot connect to <server_name. A connection cannot be made to redirector. Ensure that 'SQL Browser ' service is running. (Microsoft.AnalysisServices.AdomdClient) A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond (System) |
SQL Server Management Studio |
Named |
Cannot connect to <server_name>/<instance_name>. A connection cannot be made to redirector. Ensure that 'SQL Browser ' service is running. (Microsoft.AnalysisServices.AdomdClient) A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond (System) |
Simple Sample ADOMD ClientAccess |
Default |
Connection to <server_name> server is not ready or connectivity is broken. Full text message received follows: A connection cannot be made. Ensure that the server is running. |
Simple Sample ADOMD ClientAccess |
Named |
Connection to <server_name>\<instance_name> server is not ready or connectivity is broken. Full text message received follows: A connection cannot be made. Ensure that the server is running. |
Error Resolution Recommendation
This error is generally received when the client application is attempting to connect to an Analysis Services instance through a firewall and the necessary ports for Analysis Services connectivity are not open on the firewall. The following questions will generally help you isolate and then resolve the cause of firewall error messages.
Are the appropriate ports open on the Windows firewall?
To verify that the appropriate ports are open on the Windows firewall, open Windows Firewall in Control Panel or open the properties of an individual connection in Network Connections and then switch to the Advanced tab and click Settings. On the Exceptions tab, open the necessary port.
Are the appropriate ports open on the non-Windows firewall?
Many anti-virus software applications install firewalls on computers; this can cause similar issues. To verify that the appropriate ports are open for a particular software application, see the documentation for the anti-virus software.
If there is a hardware firewall between your client application and the Analysis Services instance, you must open the appropriate ports on the firewall or connect by using HTTP connectivity (and open the appropriate TCP port for HTTP).
HTTP Connectivity
In this section, we look at a variety of error messages generated by client applications and explore the reasons why a client application may be unable to connect to a Microsoft SQL Server 2005 Analysis Services instance by using HTTP. The scenarios in this section assume that the IIS service is running on a separate computer from the computer that is hosting the Analysis Services instance and that the appropriate ports are open on any firewalls between the client application and the Analysis Services instance. For firewall issues, see Error Condition 6: Ports Not Open on a Firewall.
In a Microsoft SQL Server 2005 Analysis Services instance, HTTP connectivity must be manually configured. For setup information, see Configuring HTTP Access to SQL Server 2005 Analysis Services on Microsoft Windows Server 2003 and Configuring HTTP Access to SQL Server 2005 Analysis Services on Microsoft Windows XP. To connect by using HTTP, the connection string must be in the form of HTTP://<server_name>/<virtual_directory_name>/msmdpump.dll. To connect to a non-standard TCP port for HTTP, simply add a colon after the server name (and before the virtual directory name) followed by the port number.
Important When troubleshooting an HTTP connectivity problem, verify that a TCP/IP client application (such as a UDL file) can connect to the Analysis Services instance from the IIS computer with the credentials specified for the IIS virtual directory. If TCP/IP connectivity fails with these credentials, HTTP connectivity will also fail. If TCP/IP connectivity fails, follow the troubleshooting steps in the previous section. If TCP/IP connectivity succeeds, but HTTP connectivity is failing, use the following steps to resolve the most common HTTP connectivity issues.
Tip: For general security guidance on IIS, see Security Guidance for IIS.
Basic Troubleshooting Steps
Basic troubleshooting steps for resolving HTTP connectivity problems include the following:
Verify that the IIS service is running by using the Internet Information Services (IIS) Manager, Computer Management or Services tools.
Open Internet Information Services Manager, navigate to the virtual directory used for HTTP connectivity to Analysis Services, and then check the following:
Verify that there is no space in the virtual directory name.
Verify that execute permissions are set to Scripts only.
Click Configuration. In the Application Extensions list, verify that the full path to the msmdpump.dll executable appears in the Executable Path list and that .dll appears in the Extension list.
Click the Directory Security tab and verify that the Authentication and Access Control settings are set appropriately.
Note: The directory security configuration for the default Web site enables both anonymous access and Integrated Windows authentication. This security configuration is inherited by new virtual directories unless you modify these settings. With this security configuration, all connections through IIS to the specified Analysis Services instance are anonymous, and connect to the specified Analysis Services instance by using the IUSR_<iis_machine_name> account. This behavior occurs regardless of the fact that other authentication methods (such as Integrated Windows, Basic, or Digest) are enabled.
If anonymous access is enabled, verify that there is an Analysis Services security role defined in the specified Analysis Services instance that allows the IUSR_<iis_machine_name> account access to one or more Analysis Services databases.
Note: When you are connecting using anonymous access, no user account is recorded in SQL Profiler for the Audit Login event. To view the IUSR_<iis_machine_name> user account, you must view another event—such as the Discover Begin event.
If IIS is running on Windows 2003 or later, verify that there is a Web Service Extension that references both the msmdpump.dll and xmlrwbin.dll files.
Important Whenever you change the IIS configuration settings for the OLAP virtual directory, make sure to restart the IIS service before retesting or you may receive a variety of interesting error messages. Also, when testing basic authentication, make sure that you do not specify Windows NT® Integrated security in your client application or you will also receive a variety of interesting error messages.
Note If users are receiving 401 or 403 errors when connecting via the Internet, verify that the IP address of the computer hosting IIS is exposed to the Internet and the appropriate ports are open for both HTTP and HTTPS access.
Verify that the value for the <ServerName> property in the Msmdpump.ini file in the OLAP folder (typically in the ..\initpub\wwwroot folder) specifies the appropriate Analysis Services instance.
Error Condition 1: 9.0 OLE DB Provider Not Installed or Improperly Registered on IIS Computer
The client application attempts to connect by using HTTP through IIS to a SQL Server 2005 Analysis Services instance. The IIS service is running, the OLAP virtual directory is properly configured, and the specified Analysis Services instance is running. The directory security configuration is not relevant for this error condition.
Important The IIS computer must have the 9.0 OLE DB provider installed and properly registered for a client application to connect to an Analysis Services instance, regardless of whether the client application is attempting to connect to the Analysis Services instance by using OLE DB or ADOMD.NET. The IIS service does not use the ADOMD.NET provider when connecting to the Analysis Services instance.
Errors Observed
The client application receives an error message similar to one in the following table.
Client Application |
Error Message |
|---|---|
UDL file |
Test connection failed because of an error initializing provider. The HTTP server returned the following error: Service Unavailable. |
UDL file |
Test connection failed because of an error in initializing provider. Either a connection cannot be made to the '<server_name>', or Analysis Services is not running on the computer specified. |
Microsoft Excel 2007 / Microsoft Excel 2003 |
Either a connection cannot be made to the <server_name>, or Analysis Services is not running on the computer specified. |
Microsoft Excel 2007 / Microsoft Excel 2003 |
The HTTP server returned the following error: Service Unavailable. |
SQL Server Management Studio |
Cannot connect to http://<server_name>/olap/msmdpump.dll. The connection either timed out or was lost. (Microsoft.AnalysisServices.AdomdClient) The remote server returned an error: (503) Server Unavailable. (System) |
SQL Server Management Studio |
Cannot connect to http://<server_name>/olap/msmdpump.dll. The connection either timed out or was lost. (Microsoft.AnalysisServices.AdomdClient). The underlying connection was closed: An unexpected error occurred on a receive. (System) Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. (System) An existing connection was forcibly closed by the remote host (System) |
Simple Sample ADOMD ClientAccess |
Connection to http://<server_name>/olap/msmdpump.dll server is not ready or connectivity is broken. Full text message received follows: The connection either timed out or was lost. |
Error Resolution Recommendations
These errors generally indicate that the Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider is not installed on the IIS computer. To verify that the 9.0 OLE DB provider is installed on the IIS computer, follow the troubleshooting steps in Error Condition 3: 9.0 OLE DB Provider Not Installed or Improperly Registered.
Error Condition 2: IIS Anonymous Connection Not Permitted
The client application attempts to connect by using HTTP through IIS to a SQL Server 2005 Analysis Services instance. The IIS service is running, the OLAP virtual directory is properly configured, and the specified Analysis Services instance is running. Directory security for the OLAP virtual directory is configured using default settings (namely anonymous access and Integrated Windows authentication).
Errors Observed
The user experiences the following behavior when connecting with a client application.
Client Application |
Error Message |
|---|---|
UDL file |
No error message is generated, but the client application is unable to access the desired database on the Analysis Services 2005 instance. If the user has no permissions to access any database on the Analysis Services 2005 instance, no databases are visible. |
Microsoft Excel 2007 |
The Data Connection Wizard cannot obtain a list of databases from the specified data source. |
Microsoft Excel 2003 |
No error message is generated, but no databases are visible in the Multidimensional Connection 9.0 dialog box. |
SQL Server Management Studio |
No error message is received when the user connects, but no databases are visible in the Databases node in Object Explorer and no databases are visible when opening an MDX query pane. |
Simple Sample ADOMD ClientAccess |
No error message is received when the client application connects, but no databases are visible. |
No entries appear for the Audit Login event in a SQL Server Profiler trace, but the anonymous user account for the IIS service (typically the IUSR_<iis_machine_name>user) appears in the NTUserName column for the Discover Begin and Discover End events.
Error Resolution Recommendations
This observed behavior when attempting to connect to an Analysis Services instance by using HTTP through IIS generally occurs because the anonymous user account for the IIS service the IUSR_<iis_machine_name>user) does not belong to an Analysis Services security role that has permissions to read any database objects.
To verify that the IIS anonymous user account is actually establishing a connection to the Analysis Services instance, create a trace by using SQL Server Profiler and view the Audit Login and Discover Begin events; the connected user account (IUSR_<iis_machine_name>user account) will appear in the NTUserName column for the Discover Begin event.
You will generally resolve this problem in one of the following ways:
Create a security role in the Analysis Services instance that grants the anonymous user account for the IIS service permission to access the specific database objects. In this scenario, all users connecting to the Analysis Services instance through the IIS service will have the same permissions.
Note You can change the anonymous user account to any domain account. A full discussion of using anonymous connectivity to IIS in conjunction with Analysis Services connection string properties, such as CustomData, Role, and EffectiveUserName are beyond the scope of this paper.
Important Permitting anonymous connectivity has significant security risks and should not be undertaken lightly. However, it can occasionally be appropriate and can be useful for testing purposes.
Disable anonymous access in the IIS service for the OLAP virtual directory and select another authentication method. IIS can be configured to support a number of authentication methods. To disable anonymous access and specify an alternate authentication method, open the properties for the OLAP virtual directory. Select the Directory Security tab, and then click Edit in the Authentication and access control box.
Note A full discussion of alternate authentication methods using IIS is beyond the scope of this paper. For more information, see Managing a Secure IIS 6.0 Solution (IIS 6.0).
Error Condition 3: Integrated Windows Authentication Credentials Cannot Be Forwarded
The client application attempts to connect by using HTTP through IIS to a SQL Server 2005 Analysis Services instance. The IIS service is running, directory security for the OLAP virtual directory is configured for Integrated Windows authentication only, and the specified Analysis Services instance is running. The user who is attempting to connect has sufficient permissions to connect and perform the task attempted.
Note In this scenario, if the IIS service and the Analysis Services instance are hosted on the same computer, the connection attempt will be successful.
Errors Observed
The client application receives an error message similar to one in the following table.
Client Application |
Error Message |
|---|---|
UDL file |
Test connection failed because of an error in initializing provider. The following system error occurred: . |
Microsoft Excel 2007 / Microsoft Excel 2003 |
The following system error occurred: . |
SQL Server Management Studio |
Cannot connect to http://<server_name>/olap/msmdpump.dll. Unsupported data format: (Microsoft.AnalysisServices.AdomdClient) |
Simple Sample ADOMD ClientAccess |
Connection to http://<server_name>/olap/msmdpump.dll server is not ready or connectivity is broken. Full text message received follows: Unsupported data format: |
ANONYMOUS LOGON or NT AUTHORITY\ANONYMOUS LOGON appears as the NTUserName in a SQL Profiler Trace for the Audit Login and Discover Begin events.
Note If your client application is a SQL Server 2005 Reporting Services report in this scenario, the error message will typically be “Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON.”
Error Resolution Recommendations
These errors are raised because the IIS service cannot forward the logon credentials of the user who is trying to connect to an Analysis Services instance through IIS. These errors are the result of the classic double hop scenario in which a user logs on and is authenticated on one computer and uses a client application to attempt to connect to a service on a third (or fourth) computer through a service running on an intermediary computer—in this scenario, the IIS service. For security reasons, the forwarding of a user’s security credentials (in the form of a Kerberos ticket or NTLM access token) is not permitted unless Kerberos is configured.
You will generally resolve this problem in one of the following ways:
Configure the IIS service to use IIS anonymous authentication. For more information on IIS anonymous authentication, see Error Condition 2: IIS Anonymous Connection Not Permitted.
Configure Kerberos to enable the client credentials to be forwarded by the IIS service to the Analysis Services instance. The steps necessary for configuring Analysis Services for authentication using Kerberos and enabling delegation are documented in KB Article 917409, How to configure SQL Server 2005 Analysis Services to use Kerberos authentication.
Configure the IIS service to use basic authentication and enable Secure Sockets Layer (SSL) to ensure that authentication information does not travel across the network in clear text. This enables users to submit a user name and password to the IIS service, which uses these security credentials to authenticate the user and present the user’s token and connect to the Analysis Services instance, impersonating the user who connected to IIS. These credentials can either be embedded in the connection string used by the application or the user can be prompted for these credentials.
Cache the credentials necessary to connect to the Analysis Services instance in the middle tier. For example, if you are using SQL Server 2005 Reporting Services, you can configure the report data source to store the credentials. For more information, see Specifying Credential and Connection Information.
Configure the Analysis Services instance to permit anonymous logons (generally only for testing purposes). You can accomplish this by using one the following methods:
Add the Windows Everyone group to an Analysis Services security role within the Analysis Services instance. Grant this role the appropriate permissions. Change the value for the Analysis Services server property Security \ RequireClientAuthentication to False in the Analysis Services instance. Then change the value for the local security policy Network access: Let Everyone permissions apply to anonymous users to Enabled on the computer.
Add the ANONYMOUS LOGON user account (from either the local computer or the Windows domain) to an Analysis Services security role with the appropriate permissions.
Important Permitting anonymous connectivity has significant security risks and should not be undertaken lightly. However, it can occasionally be appropriate and can be useful for testing purposes.
Note If you configure Analysis Services to permit anonymous connectivity, you can also use the SSPI=Anonymous connection string property.
Error Condition 4: IIS Improperly Configured Due to DLL Mismatch
The client application attempts to connect by using HTTP through IIS to an instance of SQL Server 2005 Analysis Services where there is a mixture of 32-bit and 64-bit environments. The IIS service is running, directory security for the OLAP virtual directory is properly configured for the appropriate type of authentication for your environment, and the specified Analysis Services instance is running.
Important The version of the msmdpump.dll that you use with IIS must match the version of ASP.NET with which you have configured Microsoft Internet Information Services (IIS), regardless of the version of Microsoft SQL Server 2005 Analysis Services you are running. For example, if you are running a 64-bit version of Analysis Services and a 32-bit version of IIS, you must use the 32-bit version of msmdpump.dll.
If you are running a 64-bit version of Microsoft Windows, you can switch between the 32-bit version of Microsoft ASP.NET 1.1 and both the 32-bit version and the 64-bit version of ASP.NET 2.0. For more information, see How to switch between the 32-bit versions of ASP.NET 1.1 and the 64-bit version of ASP.NET 2.0 on a 64-bit version of Windows.
Errors Observed
The client application receives an error message similar to one in the following table.
Client Application |
Error Message |
|---|---|
UDL file |
Test connection failed because of an error initializing provider. The HTTP server returned the following error: Service Unavailable. |
Microsoft Excel 2007 |
The HTTP server returned the following error: Service Unavailable. |
Microsoft Excel 2003 |
The HTTP server returned the following error: Service Unavailable. |
SQL Server Management Studio |
Cannot connect to http://MyMachine/olap/msmdpump.dll. The connection either timed out or was lost. (Microsoft.Analysis Services.AdomdClient). The remote server returned an error: (503) Server Unavailable. (System) |
Simple Sample ADOMD ClientAccess |
Connection to http://<server_name>/olap/msmdpump.dll server is not ready or connectivity is broken. Full text message received follows: The connection either timed out or was lost. |
The following general IIS error is observed on the IIS computer when connecting to https://localhost: Service Unavailable
Error Resolution Recommendations
These errors are generally raised when the version of ASP.NET that is installed and running on the IIS server does not match the version of the msmdpump.dll that you have configured. The primary symptom of such a mismatch is a Service Unavailable message from the IIS service when attempting to connect to any Web page on that server, including the default page (typically https://localhost). In this scenario, errors will also appear in the application log with the 2268 and 2274 Event IDs.
To resolve this error condition, match the version of ASP.NET that you have enabled with the version of msmdpump.dll that that you have configured for the virtual directory. To verify the version of msmdpump.dll that you are using for the virtual directory, view the value for the Platform property of the msmdpump.dll by using Windows Explorer.
Conclusion
While the SQL Server 2005 Analysis Services connectivity issues discussed in this article can be avoided by a thorough understanding of networking, security, providers, and so on, most of us will still encounter the errors discussed in this article—either because we are faced with a scenario with which we are unfamiliar or frequently because we have just been brought into a new environment to solve a problem that we did not create. In any event, knowing how to interpret the error messages generated by a variety of applications and how to quickly produce the most useful error message can substantially reduce the amount of time spent resolving Microsoft SQL Server 2005 Analysis Services connectivity problems.