June 16, 2008 at 11:17 pm
I seem to be missing something which is probably obvious.. but I'm not seeing it:
I've created a data warehouse cube in the SQL server 2005 Business Intelligence Dev Studio. I can look at the cube as a pivot table in the Browse pane of BIDS...
But when I try to connect to the cube from Excel 2007, the data connection wizard doesn't show the cube on the Select Database and Table page..( it does show the Adventure Works DW cube which is on the same server).. As far as I can tell, I've got the permission set correctly in the database.
Is there some final step I need to take so that the cube will be recognized by Excel?
June 18, 2008 at 7:25 am
Have you deployed and processed your cube to your server?
you can check by login in to SQL server managament studio,
and make a connection to your analysis server. You
should be able to browse your cube at that place.
If you can view it, then you should be able to connect with
excel 2007.
June 18, 2008 at 1:27 pm
yep.. that seems to be the problem. There must be something I don't understand about the deployment process. Within BIDS I, when I select to process and deploy, it all seems to go ok, and I can browse the cube within BIDS. But when I go to the Managment Studio and connect to Analysis services, the cube does not show.
When I try to deploy it using the Deployment Wizard, I get the error:
"Errors in the metadata manager. Either the database with the ID of xxx does not exist in the server with the ID of YYY, or the user does not have permissions to access the object."
I've tried using several different type of credentials, all of which allow me to log into the sql server succesfully, and all of which have sysadmin or dbo roles..
What am I missing???
June 18, 2008 at 2:21 pm
Have you set the permissions in BIDS? If so, did you do a test deploy? It doesn't seem like a required step, but I got hung up in a similar situation as you. I was able to resolve it by clicking on the test security link in the upper right corner in the permissions section.
I know that's slightly vague, and if you have trouble finding it, let me know and I'll go back and look to get you an exact location.
June 18, 2008 at 3:00 pm
I guess I'm going to need further directions.. I can't find where in BIDS to set the permissions.
(I've already added the login domain/username to the server role in SSAS, but I assume you're talking about something else..)
I appreciate your help in this.. at the moment all this security stuff seems needlessly frustrating, and calls out for a simple white paper on how to set it all up..
June 18, 2008 at 3:27 pm
In the solutions explorer, go to the Roles folder. Create a role with the appropriate permissions and users. In the cell data tab of that role, click "Test cube security", which appears in the upper right corner.
Let me know if that helps.
June 18, 2008 at 3:50 pm
1. Was able to create a role and give it full control, and add myself to that role. But all the other tabs are grayed out, (including the CellData tab and its "Test cube security" option)
2. Turns out that someplace in my fiddling around with it previously, I apparently did succeed in deploying the cube, but it only shows up in SSAS when I launch the managment studio as an administrator (in Vista).. not as a normal user. And it still doesn't appear as one of the available cubes when I try to connect to it from Excel.
3. Also, now when I try to redeploy or process the cube out of BIDS, it fails with the message "Login failed for NT AUTHORITY\NETWORK SERVICE"..
I haven't figured out yet why its gone to using that as the login credential..
June 18, 2008 at 3:56 pm
Try changing full control to read definition and see if the tabs become available.
June 18, 2008 at 4:42 pm
ok.. that made the tabs appar (I won't even ask at the moment why that should have made the difference..)
But when I try to test the security, it fails "No connecton could be made because the target machine actively refused it"..
When I try to reprocess the cube (within BIDS), it also fails, and tells me it's trying to login as NT AUTORITY\NETWORK SERVICE..
I don't understand why its trying to log in under those credentials. Where do I change this??
What I finally had to do (after the above) was to:
1. Sign in to SSAS as an administrator
2. Grant the user that I created above full rights
3. Sign into SSAS again as that user
4. Reprocess the cube in SSAS as the current user
( I've done a lot of fiddling, but I think these were the essential steps)
Now the cube is accessible in Excel as a pivot table.
Seems like an awful lot of security related work just to deploy this cube!
I really appreciate your help on this.. Thanks..
Is there any writeup available of this whole deployment/ security process?
June 18, 2008 at 5:26 pm
I'm glad it finally worked for you.
I found security much easier in Analysis Service 2K, although I still think AS2005 is overall better. Unfortunately there is no write up on this that I'm aware of. I ran into many of the same issues you did and nearly pulled my hair out trying for a couple of days to figure it all out.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply