OLAP Cube failing due to lack of permissions.

K

Kevin

I’ve been using the Implementing and Administering Microsoft Office Project
Server 2007 guide as well as MSFT’s configuring Analysis Services 2005 to
work with Project Server 2007 and for the life of me I cannot get past this
permissions issue.

I have turned on verbose logging and used the SQL profiler (I’m not a DBA so
this was a first for me) and see nothing more detailed than the message
project provides below. I have logged into the SQL 2005 box as the SSP user
and connected to the Analysis Services and tested the connection for both the
DSO\RepositoryConnectionString and the DSO\RemoteRepositoryConnectionString
and both indicated success. Any guidance to track down this issue would be
greatly appreciated.

Failed Build Message:
Your CBSRequest job failed. Its current state is FailedNotBlocking. It was
0% complete. It entered the queue at 10/25/2008 22:15:43.

To get more information about the job failure, please go to Project Web
Access. Select Personal Settings from the left menu. Then select My Queued
Jobs.

The errors returned from the queue are as follows:
Error ID: 17003
Error ID: 26000

Detailed error below - send it to the administrator for more detailed
troubleshooting.

<?xml version="1.0" encoding="utf-16"?>
<errinfo>
<general>
<class name="CBS message processor failed">
<error id="17003" name="CBSASConnectionFailure"
uid="ba0cfc1b-a905-417c-9d97-0c64e411e7b1" QueueMessageBody="Setting
UID=00007829-4392-48b3-b533-5a5a4797e3c9 ASServerName=DHRM-DEV-SQL
ASDBName=PRJ07_Analysis_Repository ASExtraNetAddress= RangeChoice=0 PastNum=1
PastUnit=0 NextNum=1 NextUnit=0 FromDate=10/25/2008 22:15:42
ToDate=10/25/2008 22:15:42 HighPriority=True" Error="Analysis Services
session failed with the following error: Failed to connect to the Analysis
Services server DHRM-DEV-SQL. Error: Your permissions on the server computer
do not allow you to administer this Analysis server.

Error: " />
</class>
<class name="Queue">
<error id="26000" name="GeneralQueueJobFailed"
uid="9ec4518d-bb62-4610-8662-b07fedda3469"
JobUID="de04bcbf-d38c-49cc-9cf9-3e1b4de1e597" ComputerName="DHRM-VMDEV-PRJ0"
GroupType="CBSRequest" MessageType="CBSQueueMessage" MessageId="2" Stage="" />
</class>
</general>
</errinfo>

System Info:
DEV-SQL server running SQL Server 2005 SP2
• The SQL Server Analysis Services user is SQLServiceAgent. This user has
User Mappings for the reporting database = db_datareader

DEV-PRJ07 server running Project 2007 + MOSS 2007 + Server 2008
• Of the many users I’ve setup to get Project 2007 running, SQLServerAgent
is not one of them. How do I learn which user is being used to build the
cubes and what other authority I need to give?
• The Database user for this configuration is not SQLServiceAgent.
 
R

Rudolf

Hi Kevin,

when building the cube project server writes a file to one of the root
directories of the application server. Best chance is that this will
be the hard disk where the project serversoftware is installed.

To be able to write this file (I think it's called msolap.upd) the
ssp-user needs write rights to this root directory.

Hope this helps,
Regards,
Rudolf
 
K

Kevin

Yes, thank you for the response.

PRJSSP has been added to SQLServer2005MSOLAPUser$DHRM-DEV-SQL$MSSQLSER.

SQLServer2005MSOLAPUser$DHRM-DEV-SQL$MSSQLSER has a security login with a
user map for PRJ07_Analysis_Repository with db_owner enabled.

The Analysis Services user, SQLServiceAccount, has a security login with a
user map for PRJ07_ProjectServer_Reporting with db_datareader enabled.

The PRJSSP user has been added to the Analysis Server,s Security NT Group.

What other setup can I check for you?
 
K

Kevin

Rudolf -
I granted the PRJSSP user local admin rights to the server where Project
Server and MOSS are installed. I created a test text file on the root drive
to confirm that the user had permission to create files. I cannot find any
sign of a msolap.udp file.
 
K

Kevin

Paul, I wish I could share the victory dance with you but your one line
sentance has me just as clueless as I was before. How do you know I'm
missing the Administrators role and how do I add it?
 
P

Paul Conroy

open Analysis Services in SQL management studio.

Open the properties of the AS instance, and on the security tab, check that
the SSP account is specified with the administrators role. (or belongs to a
group which is).
 
K

Kevin

The SSP account is listed in the AS Properties security tab already. I'm
getting hung up on the "administrator role" language. Are you saying that I
need to add the SSP user to the local admin group on the SQL2005 Server? Or
are you saying that any local admin needs to also be added to the AS
Properties security tab? Or is there a setting to enable the SSP user
administrative rights within the securities tab that I'm overlooking? Sorry
to be dense and I appreciate the patience.
 
P

Paul Conroy

Extract from this article
http://technet.microsoft.com/en-nz/library/cc197552(en-us).aspx

Configure the SSP account to have administrative permissions in SQL Server
2005 Analysis Services
You must also add the SSP account as an administrator in SQL Server 2005
Analysis Services in order to have permissions to create databases. In SQL
Server 2000 Analysis Services, this automatically occurs when a user is added
to the OLAP administrators group. In SQL Server 2005 Analysis Services, the
user must be added manually as a member to the server role administrative
group.

Members of the server role within an instance of Microsoft SQL Server 2005
Analysis Services have unrestricted access to all Analysis Services objects
and data in that instance. A member of the Analysis Services server role can
add Microsoft Windows users and groups to the Analysis Services server role.
A user must be a member of the Analysis Services server role to perform any
server-wide task, such as creating a database, modifying server properties,
or launching a trace (other than for processing events).

Add the SSP account as a server role member in SQL Server 2005 Analysis
Services
Open SQL Server Management Studio. In the Connect to Server window, connect
to the instance of SQL Server 2005 Analysis Services that you are using.

In Microsoft SQL Server Management Studio, in Object Explorer, right-click
your SQL Server 2005 Analysis Services instance name, and then click
Properties.

On the Analysis Services Properties page, in the Select a page pane, click
Security. NT Users and Groups that are members of the server role will appear
in a list.

Click Add. In the Select Users or Groups page, go to the Enter the object
names to select field and enter the name of the SSP account that you are
adding to the server role. Click Check Name to verify that the account exists.

Click OK.
 
K

Kevin

If the below is how you add a user to the admin server role than that was
already done. The SSP user is already listed on the security tab page. Is
there some kind of test I can do to sign in as the SSP user on the SQL box
and run something to confirm?

How to add the SSP user to the server admin role:
Add the SSP account as a server role member in SQL Server 2005 Analysis
Services
Open SQL Server Management Studio. In the Connect to Server window, connect
to the instance of SQL Server 2005 Analysis Services that you are using.

In Microsoft SQL Server Management Studio, in Object Explorer, right-click
your SQL Server 2005 Analysis Services instance name, and then click
Properties.

On the Analysis Services Properties page, in the Select a page pane, click
Security. NT Users and Groups that are members of the server role will appear
in a list.

Click Add. In the Select Users or Groups page, go to the Enter the object
names to select field and enter the name of the SSP account that you are
adding to the server role. Click Check Name to verify that the account
exists.
 
G

Gary L. Chefetz

K

Kevin

For Project Server, just two. The machines are in a data room with 20-30
others.

System Info:
DEV-SQL server running SQL Server 2005 SP2
• The SQL Server Analysis Services user is SQLServiceAgent. This user has
User Mappings for the reporting database = db_datareader

DEV-PRJ07 server running Project 2007 + MOSS 2007 + Server 2008
• Of the many users I’ve setup to get Project 2007 running, SQLServerAgent
is not one of them. How do I learn which user is being used to build the
cubes and what other authority I need to give?
• The Database user for this configuration is not SQLServiceAgent.
 
B

brianduper

Kevin:

Add the SSP account to the Local *Administrators* group on the server
running SQL Analysis Server. Use the same steps described starting on page
180 of Implementing and Administering book.

--
----------
Gary L. Chefetz, MVP
MSProjectExperts
Project Server Consulting:http://www.msprojectexperts.com
Project Server Training:http://www.projectservertraining.com
Project Server FAQS:http://www.projectserverexperts.com
Project Server Help Blog:http://www.projectserverhelp.com











...

read more »- Hide quoted text -

- Show quoted text -
Click to expand...


Kevin - Did you resolve your issue, if so, would like to know what you
learned. I am pretty sure I have the identical issue (after following
the MS documentation to a T.)

Thanks - Brian
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cube Build Failure 7
OLAP Cube Build Issue 3
OLAP Cube Build Failure 9
OLAP Cube build failure – What is the meaning of the queue job err 9
OLAP queue job error: 'Your queue job CBSRequest failed' 2
PS2007 Cube Failing 2
Cube failed to build due to 'Assignment Timephased' 1
Olap Cube Build Error...can't seem to get the cube to build 5

Top