Connecting to SSAS 2005 from Excel 2007 over http: how to make it ask for username and password?

Question

I'm trying to let my users connect to OLAP cube in SSAS 2005 using Excel 2007 over the Internet.

I've set up dynamic security using fact table in cube. It uses UserName function, so users should authenticate to SSAS using windows accounts. I've set up msmdpump.dll component on IIS, allowing windows authentication, but not anonimous one. I created windows accounts on web/SSAS server for remote users. My users' machines are out of my control, I can not use pass-through windows authentication.

Now, if I create connection in Excel and save username and password in it, everything works. But I want users to download Excel file without embedded credentials from my web site, and than be able to enter their credentials. Best option is if they will enter password when opening Excel file.

Problem is as soon as I clear checkbox "save password" in connection properties, or remove "User ID"/"Password" properties from connection string, Excel gives me authentication error and does not save connection properties. So I cannot create a template document or connection file without embedded credentials.

Moreover, I suspect, Excel will not ask user for login/password anyway.

Do you know a solution to my problem? Maybe some workarounds/alternative ways? I appreciate any help.

Answer 1

Hello, I have similar problem. My intranet users can access SSAS 2005 from excel 2003. I have to enable users from internet to access cubes in my ssas 2005. Any suggestions how to enable it? Right now I save user credentials saved in the excel documents, Is there any I can make users to pass credendtial on the fly?

Thanks, Vinod

Answer 2

it is really tough to get what you want to work, if at all. I don't think it will work "over the internet" like you want. They would have to be VPN'd in. Their machine doesnt have to be on the network, but they can still pass the AD credentials through.

http://blog.stevienova.com/2008/01/14/how-to-connect-to-sql-server-vs-tfs-etc-using-windows-authentication-when-computer-is-not-on-active-directory-domain-xp-and-vista/

There are other options to expose the cube in some other ways (SSRS, Excel Services, 3rd party OLAP through Web) that would allow you to do what you want.

Answer 3

Here's something that might be worth trying - in your connection string, get rid of the username and password and add 'prompt=1;'. This will force Excel to ask for the user's credentials before it tries to authenticate them, instead of just using empty credentials to do the authentication.