December 11, 2009 at 1:06 pm
Good Afternoon,
I am trying to give a user access to view a SSAS cube via Excel, but when he tries to connect to the server, he receives an error similar to this: "The data connection wizard cannot obtain a list of databases from the specified data source."
We are using Excel 2007 and SQL Server 2008. I can connect to the cube without issue, but I've not had much experience giving users access to cubes.
I'm having him select (in Excel) data > From other data sources > From Analysis Services. At this point, there is a pop up screen where he is supposed to type in the server name. After he types the name in with the Windows Auth radio button selected, he clicks Next and receives the above error.
I have set his Windows Auth ID up on the server in question and given him public and sysadmin server roles, but he is still unable to connect.
Can anyone please point me in the right direction?
Thanks,
Matt
December 11, 2009 at 1:50 pm
Try connecting through a UDL file from the client's computer and please be sure to select "Microsoft OLE DB provider for Analysis services 9.0" on the provider tab. Let us know how it goes.
December 11, 2009 at 1:52 pm
You've given him public and sysadmin roles 'where'? In SQL Server? SQL and SSAS security are seperate models. If so, you need to explicitly provide access (ie put him within a role) in Analysis Services. The simplest (and somewhat worst due to security) choice is to add him to the Admin of the analysis services instance (ie right click the SSAS server in SSMS , go to proerties and security, add the user). A better choice is to go to the SSAS DB in question (expand the object browser in SSMS) and add a role for the DB and then add the user to the Role.
HTH,
Steve.
December 11, 2009 at 1:58 pm
Thanks for the info guys. I'll follow your advice and let you know if it works. I don't think the user works until next week so it may not be until then.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply