http access using msmdpump and basic authentication

Question

Hi all,

As we plan to set up a hosted service using SSAS, we've set-up http access to SSAS unsing the msmdpump. It works great, when we allow anonymous access. Hoewever, anonymous access is unacceptable for a lot of obvious reasons. The problem is that everything stops working when we enable basic authentication in IIS.

I feel we're missing something here. It looks like IIS doesn't hand over the user credentials the way msmdpump expects them. The result is an access denied error to the user and an XML Parsing error in the SSAS flight recorder log.

We're running SSAS 2008 on Windows Server 2008 without an AD setup. IIS is running on the same box. Obviously, we're using IIS 7. The client is Excel 2007 without any client tooling installed, since our offering won't allow for the distribution of client connectivity tooling. All users are outside our domain. However, we're creating local windows accounts for all users to accomodate access. AD will be implemented as need arises, for instance if we need to scale to multiple boxes.

I'm having no luck at all finding anything on the web that addesses this issue. Does anyone have an idea how to solve this mistery?

Thanks very much in advance,

Coen

Answer 1

Under Authenticated Acess options, did you select only basic authentication? or did you also select Integrated Windows Authentication? Try by selecting both options.

---

Thanks, Ashok -http://dugaputiashok.blogspot.com/

Answer 2

Unfortunately, your suggestion had nog effect.

Thanks anyway,

Coen

Answer 3

What is the windows group of the windows user that you are trying to ? When you install SQL Server 2008 Analysis Services, a local group is created on the server that allows users to access it. This group is named SQLServerMSASUser$<SERVERNAME>$MSSQLSERVER. Please check if your user is member of this group?

---

Thanks,
Ashok -
http://dugaputiashok.blogspot.com/

Answer 4

Tried it, but didn't work either. I think I may have left out some vital info in my initial posting. The user I'm working with is already a member of the server admin role in SSAS. If I use excel to go directly against SSAS, without using msmdpump, everything works just fine, although I do get challenged from the SSAS Client Tooling wich is installed on this particular machine.

I still think SSAS somehow isn't aware of the user credentials when I go in through msmdpump.

Thanks again,

Coen

Answer 5

 

Hi Coen,

Could you try the below steps:

1)    Did you configure HTTPS for the IIS site? If yes, you can refer to:

http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/d91246d6-29bc-4d5f-815f-820e45b58c58/

2)    If not, ensure the same authentication setting (only select basic authentication) for both parent site and the OLAP site. Also, ensure you are using the port which the site binds.  Note the URL format: http://localhost/olap_2008/msmdpump.dll will not work but http://localhost:404/olap_2008/msmdpump.dll will work.

3)    Since you are using basic authentication, then you cannot use the URL in SSMS, because there is no place to type the account and password. If still not work please post the detail error message.

 

Regards,

Raymond

Answer 6

Hi,

1. We didn't configure HTTPS yet. We will before we go live.

2. Both the parent (wich is the default site for MOSS) and the OLAP app are on Basic Authentication only. Both are bound to port 80 (if I understand you correctly that is)

3. I'm aware of that. I'm using Excel 2007 as the client, since this is what our clients will be using.

The weird thing is, it all works when we allow anonymous access. But this is obvious as we set the security context ourselves in IIS.

So, just to make sure I've described my problem correctly:

When Basic Authentication is enabled, I use excel to access SSAS. I use the 'data from other sources' --> "Analysis Services" option in the data tab of the ribbon in excel.

I then get a dialog that requires me to fill in the server name. This is the server name I use: http://bi-factory.com/olapnic/msmdpump.dll  For the credentials I choose: "use the following name and password" and fill in my user credentials I have on the server. This userid is a member of the SSAS Server admin role on the server. I click 'Next'.

I know Basic Authentication works OK, because when I type the wrong password, I get an error right away. The next dialog lets me select the OLAP Database and the specific cube. I pick a cube and click 'Next'. In the next dialog I save the ODC and then the 'Import Data' dialog asks me where I want to put the pivot table. I click OK en then I get the 'Data Source Initialization Failed' message.



As far as errors go, the user gets the following message:

"Initialization of data source failed. Check the database server...."


In the event log, the following message appears:

"Log Name:      Application
Source:        MSOLAP ISAPI Extension: \\?\C:\inetpub\wwwroot\olap\msmdpump.dll
Date:          2/8/2010 10:48:11 AM
Event ID:      10
Task Category: (269)
Level:         Information
Keywords:      Classic
User:          N/A
Computer:      (cut)
Description:
The description for Event ID 10 from source MSOLAP ISAPI Extension: \\?\C:\inetpub\wwwroot\olap\msmdpump.dll cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.

If the event originated on another computer, the display information had to be saved with the event.

The following information was included with the event:

Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
  <System>
    <Provider Name="MSOLAP ISAPI Extension: \\?\C:\inetpub\wwwroot\olap\msmdpump.dll" />
    <EventID Qualifiers="16653">10</EventID>
    <Level>4</Level>
    <Task>269</Task>
    <Keywords>0x80000000000000</Keywords>
    <TimeCreated SystemTime="2010-02-08T09:48:11.000Z" />
    <EventRecordID>386085</EventRecordID>
    <Channel>Application</Channel>
    <Computer>(cut)</Computer>
    <Security />
  </System>
  <EventData>
    <Data>
    </Data>
  </EventData>
</Event>"


 I can't make any sense of the SSAS flight recorder. Can't see anything that indicates a reaction from SSAS.

Thanks again for your attention to my challenge :-)

Coen

Answer 7

I then get a dialog that requires me to fill in the server name. This is the server name I use: http://bi-factory.com/olapnic/msmdpump.dll  For the credentials I choose: "use the following name and password" and fill in my user credentials I have on the server. This userid is a member of the SSAS Server admin role on the server. I click 'Next'.

I know Basic Authentication works OK, because when I type the wrong password, I get an error right away. The next dialog lets me select the OLAP Database and the specific cube. I pick a cube and click 'Next'. In the next dialog I save the ODC and then the 'Import Data' dialog asks me where I want to put the pivot table. I click OK en then I get the 'Data Source Initialization Failed' message.

 

It seems your IIS configuration is working, so I think the issues could be caused by the msmdpump.  

1)    Could you try to reinstall the provider MSLOAP 10.0 in your client? Can you access to SSAS through SSMS (using tip/ip not http) in the same client machine? If no, then it could be a component issue. Perhaps the provider or the installation is corrupted.

2)    Open the msmdpump.ini in your IIS folder, the path should be C:\inetpub\wwwroot\olap, check the server name, since the IIS and SSAS on the same machine, then it should be:

<ServerName>localhost</ServerName>

 

Regards,

Raymond

Answer 8

Please note that the whole thing works great if we allow Anonymous Access in IIS, which means I can query just fine with Excel without MSOLAP installed on the client computer. Problems only occur if we configure Basic Authentication in IIS.

1. The client doesn't have the MSOLAP 10 driver installed, as msmdpump is intended to allow access to SSAS *without* having to install MSOLAP on the client. This is exactly what we need, because we'll only be servicing clients in other companies. SSAS works just fine, either through the TCP/IP or msmdpump.

2. This is the contents of msmdpump.ini:

<ConfigurationSettings>
     <ServerName>2DECIDESRV01</ServerName>
     <SessionTimeout>3600</SessionTimeout>
     <ConnectionPoolSize>100</ConnectionPoolSize>
     <MinThreadPoolSize>200</MinThreadPoolSize>
     <MaxThreadPoolSize>500</MaxThreadPoolSize>
     <MaxThreadsPerClient>100</MaxThreadsPerClient>
</ConfigurationSettings>

Again, please note, everything is working great as long as we're allowing Anonymous Access. In fact, if you'd like to try it youself, I've a demo-cube in place, so just go ahead: http://bi-factory.com/olapnic/msmdpump.dll. Works fine in Excel.

I'm stumped by this, to be honest.

Thanks again,

Coen

Answer 9

Well, it seems we've cracked it, at least partially. Our understanding of how authentication works here seemed to be out of sync with reality. We assumed IIS would pass the credentials to the msmdpump that would allow for some sform of seamless authentication. We discovered, however, that the credentials have to be in the connection string. Since we use excel as a client, this means we need to make sure the ODC is providing the right credentials.

So far so good. We've made sure the username and password are stored in the ODC and everything works like a dream. The only thing left figure out now is how to store the user's credentials dynamically into the ODC everytime a user logs on. Maybe we can make MOSS jump through a hoop so it'll do this for us.

Cheers,

Coen

Answer 10

stumpie2.. please help me with this...

please tell me where is this u r talking about ODC to provide credentials, as i thought also same u did that IIS would pass credentials to the msmdpump !!!

do u mean credentials in web.config in c:\inetpub\wwwroot\olap\web.config  ???   i really would appreciate your fast response.

i'm having a problem connecting to analysis services 2008 with http access on windows server 2008 r2...?

i'm getting this message...

can not connect to http://localhost/olap/msmdpump.dll

the remote server returned an error : ( 500 ) internal server error.

Please HELP....

Thanks.

Answer 11

As far as I understand it now, and it works on our box this way:

IIS is merely guarding the front door. So the authentication IIS does is only on the web server level, just to check if the user is allowed access to the virtual dir. msmdpump is not aware of the credentials the user provided to IIS. Be aware that msmdpump seems to be created for developers wanting to build their own qury app for SSAS. So it expects to be provided with the credentials in the connection string sent by the client app.

In my case this is Excel. The only way I know to make sure Excel sends credentials in the connection string is to create and Office Data Connection (ODC) with exel as describes here. In our case, there are some exceptions to this description.

In the data connection wizard, under 'Connect to database server', write the http://<<serverURL>>/olap/msmdpump.dll
In the same dialog, for Logon Credentials, choose "use the following Use Name and Password" and fill in the user credentials used for the basic authentication.

Now when you get to the 'Save Data Connection File and Finish' dialog, make sure to tick the box next to 'Save password in file' option. This will make sure the credentials used are stored in the connection string. Afterwards just finish the wizard and it should work nicely.

I'm still working to find out how this works with Excel Services though.

Coen

Answer 12

i will try it ... appreciate you responce...

but right now solved by connecting to analysis services on windows server 2003 which allows anonymous access

while windows server 2008 R2 have issue in not allowing anonymous connection ... the thing i knew to solved is kerberous authentication as there is a domain here..

anyway thanks.

Answer 13

Hi

maybe someone can help me. If I connect to my Analysis Services 2008 Server with http-authentication and msmdpump.dll I can connect to my cubes. It works for basic authentication and anonymous. But anonymous authentication is 10 times faster.

Both authentications connect very fast and offer the projects and cubes from the server. After selecting a cube it takes 1 sec to receive the data (anonymous authentication) or 10 sec (basic authentication). Of course we can not go productive with anonymous authentication. AD is not available. We are not using https.

As I thought it could be the Server OS I installed a new Windows Server 2008 (instead of 2003). Unfortunately: exactly the same effect

Any ideas?

 

Thanks

Answer 14

Hi Dear,

I was looking for the authentication for the msmdpump and I successed in authenticating it.

• Disallow Anonymous access.
• Just allow basit authentication.
• And write in Default Domain : <SystemName> or <DomainName>.
• Authenticate your following connectString either system credentials or domain credentials respectively.

I have tested on live and local url with windows and domain user credentials.

"Provider=msolap;User id=<UserName>;Password=<User Password>;Data Source="http://<serverURL>/olap/msmdpump.dll"

Regards,
Muhammad Ghufran

 

 

---

In the race for the excellence, there is no finish line.