I have a server that has Windows 2000 Advanced Server. I installed SQL Server 2000 and Analysis Server. I then applied service pack 1 for both products. I used a domain admin NT account to run both the SQL engine and the SQL agent. I have also used the same NT account to run the OLAP service.
The problem I get is trying to run a sample query that pulls an OPENROWSET from the analysis server:
SELECT a.* FROM OpenRowset('MSOLAP','DATASOURCE=LocalServer; USER ID=; PASSWORD=;Initial Catalog=FoodMart 2000;',
'SELECT Measures.members ON ROWS,
[Product Category].members ON COLUMNS
FROM [Sales]') as a
The error message states that Access Denied. However when I use another SQL Server's Query Analyzer to run the above query, it runs successfully. (I am hitting the same/original Analysis server listed above.) I have examined the configuration of both SQL Servers and can not see any differences.
Why does one SQL Server work and the other fail?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!