[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2009-04-28
9
Medium Priority
?
4,057 Views
Last Modified: 2016-02-13
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
Comment
Question by:wand_polisher
  • 4
  • 3
8 Comments
 
LVL 14

Expert Comment

by:wolfman007
ID: 24258359
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
 

Author Comment

by:wand_polisher
ID: 24266167
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
 
LVL 14

Expert Comment

by:wolfman007
ID: 24268035
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Expert Comment

by:wolfman007
ID: 24268074
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
 
LVL 6

Expert Comment

by:Neeraj Soni
ID: 24278054
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
 

Author Comment

by:wand_polisher
ID: 24292937
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
 
LVL 14

Accepted Solution

by:
wolfman007 earned 1500 total points
ID: 24316452
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
 

Author Comment

by:wand_polisher
ID: 24609273
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question