• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4122
  • Last Modified:

I cannot establish a connection to an internal 2005 SQL server from my workstation running Excel 2003

I have a 2005 SQL server running in my internal network. I am trying to establish a connection to it from Excel 2003 running on my workstation. In Excel I select Cube Analysis menu, Manage Connection, New, in the "Define Connection - Choose server" dialog box i choose "Server" and type the server's name of "patons-arrow", then click next and the error message appears: "OLAP Server error. Cannot connect to the server 'patons-arrow'. The server is either not started or too busy". I'm 99% sure that the error explanation offered is not correct in this case. Any ideas ?
0
wand_polisher
Asked:
wand_polisher
  • 4
  • 3
1 Solution
 
wolfman007Commented:
Does the SQL Server 2005 server that you are trying to connect to have Analysis Services installed and running?

If you do not have Analysis Services installed then you will not be able to connect using your Excel Cube Analysis.
0
 
wand_polisherAuthor Commented:
Yes - Analysis Services is installed and running. I have Excel installed on the SQL server also and can connect locally to the database without a problem. Can it be a permissions issue or firewall setting ?
0
 
wolfman007Commented:
Have you confirmed that the Microsoft OLE DB provider for SQL Server 2005 Analysis Services is installed on your computer?

Also have you followed the instructions on the following website?

How to connect Excel to SQL Server 2005 Analysis Services
http://support.microsoft.com/kb/940167
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
wolfman007Commented:
Have a look at the following website as well, to connect to Analysis Services he needed either the SQL Server Client or OLEDB9 and MSXML6.

Error: Cannot connect to Server 'AnalysisServerName'.The Server is either not started or too busy
http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/0d36ee22-a253-4d38-8908-70017aa865f7
0
 
Neeraj SoniSr. ArchitectCommented:
Is remote connection enabled on server? Though I fought with this scenario in AS2K.

ref: http://webspace0s.spaces.live.com/blog/cns!D8D07DC942EB82D3!141.entry
0
 
wand_polisherAuthor Commented:
OK - progress made. Wolfman007, I followed the link in your comment ID 24268035 and determined that the Microsoft OLE DB for SQL Server Analysis services 9.0 was in fact not installed on my computer. As per instructions on that website I have now installed it along with MSXML 6.0.  Now, in Excel from my computer, I can connect to the cube on the SQL server, however when i try selecting different data, i get another error - "An error was encountered in the transport layer"  and  "Errors in the OLE DB provider. An error occurred while loading the connection dialog box component for prompting". Any ideas?
0
 
wolfman007Commented:
You will need to use a Windows Login to connect to Analysis Services.

SSAS does not support SQL logins. It only supports windows logins and your login would either need to be in the server administrators role (where you would be able to see everything) or your login would need to be added to a role in the database with rights to see specific cube.

from

An error was encountered in the transport layer
http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/b2861abd-0060-4efb-970b-bf455731f5a9


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.

from
http://technet.microsoft.com/en-gb/library/cc917670.aspx
0
 
wand_polisherAuthor Commented:
Although i attempted most of the thoughts and ideas contained within the links provided, i could not resolve the issue. The same errors persist -  "An error was encountered in the transport layer"  and  "Errors in the OLE DB provider. An error occurred while loading the connection dialog box component for prompting". I did learn a lot along the way though.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now