Avatar of MysticX
MysticXFlag for Australia asked on

Problem with Win 2003 Server / SQL Analysis Server not accepting connections?

I changed our Domain Controller over the weekend and since I have done this, no one is able to refresh various sales reports we have which run via OLAP cubes out of SQL Analysis Server 2000.

The sales reports are run in Excel and connect to a SQL database on another server in our environment (on the same domain as the domain controller, but nothing on this machine changed).  The actual excel files sit on the new domain controller (but permissions were copied so are the same as before).

When I try to refresh the sales reports, which obviously tries to connect to the sql 2000 server i get the following error within MS Excel 2003:

"The following system error occurred: No connection could be made because the target machine actively refused it."
Then I get:
"Errors in the OLE DB Provider. An error occured while loading the connection dialog box component for prompting."

The strange thing is, the sales report can be refreshed if you are a user in the Domain admins group or Local Admins group.  For everyone else, the connection is blocked and you can no longer refresh the sales reports.  Obviously I dont want to give every user admin access to fix this problem, but I am at a loss as to why this is suddenly happening?

Any help is greatly appreciated.
DatabasesWindows Server 2003Microsoft SQL Server

Avatar of undefined
Last Comment
MysticX

8/22/2022 - Mon
ASKER
MysticX

Just a quick followup, I forgot to mention that the data cubes which the sales reports connect to are stored in MS SQL Analysis Server 2000.
Reg Bes

Hi MysticX,

What are the permissions for   c:/program files/microsoft analysis services/bin folder , (the share is
called msolaprepository$) on the  server? try giving the users who need access to the cubes permission to this folder
ASKER
MysticX

Hi Regbes,

I could not find that share, but i went to the c:/program files/microsoft analysis services/bin folder and gave Full access to a test user and rolled this security down to all subfolders, but this made no difference.  The same error messages still come up im afraid.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Reg Bes

If you try to connect via a non admin user using the analysis manager do you get the same result?

If not it may be caused by not having Pivot Table Services( PTS) loaded on the machine you are connecting from, though this does not seem likely to me as you connect as admin.

You can also try and re set up the connection the the AS cube in excel by using Data -> piviot table -> external data source.

HTH

R.
ASKER CERTIFIED SOLUTION
MysticX

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question