Hi All,

I have been trying to expose my analysis server for secure/authenticated web access using https. This is being done through ISA server. When I connect directly to it from within the LAN (using http, with the URL http://[myserver]/olap/msmdpump.dll ) it works fine. However, when I connect using https through ISA (with the URL https://[publishedAddressOfMyServer/olap/msmdpump.dll ) it fails with the following error:

System.Runtime.InteropServices.COMException (0x80004005): Either a connection cannot be made to the [publishedAddressOfMyServer] server, or Analysis Services is not running on the computer specified.

at ADODB.ConnectionClass.Open(String ConnectionString, String UserID, String Password, Int32 Options)

The server is Windows 2003 Server, Analysis Services 2005, Microsoft ISA Server (being used for publishing & SSL). The client is a custom .NET application that uses the OWC 11 Pivot Table and ChartSpace components. The connection string we're using to connect to Analysis Services via HTTPS is as follows:

Provider=MSOLAP;Persist Security Info=True;Data Source=https://[publishedAddressOfMyServer/olap/msmdpump.dll;Initial Catalog=[dbName];User Id=[userId];Password=[password]

Also, I have noticed that in the cases where it works, the client requests in the IIS log look a bit like this:

2006-03-14 11:47:37 W3SVC1 127.0.0.1 POST /olap/msmdpump.dll - 80 [user] 127.0.0.1 HTTP/1.1 MSOLAP+9.0+Client 200 0 0 156

However, when it fails it looks more like this:

2006-03-14 11:37:20 W3SVC1 172.19.10.21 POST /olap/msmdpump.dll/msolap.asp - 443 - 172.19.12.60 HTTP/1.1 Light+DCube 401 1 2148074252 2500

Note that in the failed requests, the client signature changes from "MSOLAP 9.0 Client" to "Light DCube" and "/msolap.asp" gets appended to the URL. After it finishes the authentication handshake, it eventually ends in a fairly predictable 404 error as the URL olap/msmdpump.dll/msolap.asp doesn't exist. Note also that the IIS virtual directory "olap" was configured in accordance with the instructions found here: http://www.microsoft.com/technet/prodtechnol/sql/2005/httpasws.mspx

Any help on resolving this problem would be greatly appreciated.

Thank you.

Kind regards,
Miles

One of the network guys eventually helped me solve this. The problem was, our SSL certificate was just an in-house one that was not signed by a trusted authority and hence it was being rejected by our client software. The resolution was to install the root certificate that our servercertificate was signed within the trusted root certificates store and then it started working. Another solution would be to purchase a trusted certificate from thawte / verisign / whomever else so it would be automatically trusted. ...Just in case someone else encounters the same behaviour and gets stuck.

That's interesting. We have exactly the same scenario except we are already using a trusted certificate and yet we still have this behaviour.

What's strange is we don't see this when accessing the cube via Excel, we see this when accesing via ProClarity.

I've had support call open with Microsoft to resolve this but currently they are baffled. It currently looks like some sort of MDAC configuration issue on the client, however the support person can't get anybody internally to explain what "Light DCube" actually is!!!

Microsoft can you look into this?

OK, good news to report. Together with Microsoft support we have identified the cause of our issue and Microsoft have produced a fix for us. (A big thank you to Anne Zorner from microsoft support UK for her efforts)

It turned out to be an obscure client side issue rather than anything to do with the HTTPS setup on the server. In our scenario, I was accessing the cube via ProClarity Desktop Professional and the issue was only present on client systems running Windows 2000 with MDAC 2.8 SP1 (which ProClarity installs). The issue turned out to be related to security enhancements introduced by Microsoft where the username and password should have been passed to the web server but instead the password was being omitted. We had 3 teams looking at the issue; the operating system team, the SQL server team and the ProClarity team and in the end the fix was a 1 line code changein ProClarity to make the correct function call (I don't know the detail of function this is) In our scenario, making the change in ProClarity was the fastest solution, but to server a wider audience I feel the fix should have been made within MDAC.

I have just the same problem with Excel 2003 sp3.

IIS logs looks like:

2007-11-16 13:10:16 W3SVC1 192.168.150.5 POST /krolap/msmdpump.dll/msolap.asp - 80 - 212.xx.xx.186 Light+DCube 401 2 2148074254
2007-11-16 13:10:16 W3SVC1 192.168.150.5 POST /krolap/msmdpump.dll/msolap.asp - 80 dddd.mmmm@st123.com 212.44.69.186 Light+DCube 401 1 1326
2007-11-16 13:11:09 W3SVC1 192.168.150.5 POST /krolap/msmdpump.dll/msolap.asp - 80 - 212.xx.xx.186 Light+DCube 401 2 2148074254
2007-11-16 13:11:09 W3SVC1 192.168.150.5 POST /krolap/msmdpump.dll/msolap.asp - 80 dddd.mmmm@st123.com 212.44.69.186 Light+DCube 401 1 1326
2007-11-16 13:23:24 W3SVC1 192.168.150.5 POST /krolap/msmdpump.dll/msolap.asp - 80 - 212.xx.xx.186 Light+DCube 401 2 2148074254
2007-11-16 13:23:24 W3SVC1 192.168.150.5 POST /krolap/msmdpump.dll/msolap.asp - 80 dddd.mmmm@st123.com 212.44.69.186 Light+DCube 401 1 1326

I'm sure I use OLEDB Provider for Analysis Services 9.0 (version 9.00.3042.00) to connect to SSAS2005 cube.

SSAS runs on the same machine there IIS is. There is no https setup on IIS.

It seems that Excel tries to connect to older server version than SQL Server 2005.

Any thoughts?

Thanks!

PS When using Excel 2007 from another machine everything works fine.

In our case we had the SQL Server, Operating System and ProClarity people look at this issue and I guess Microsoft determined that the cheapetst/easiest option was for the ProClarity team to produce the fix to our issue.

I feel that this should be adressed by the SQL server team to produce a fix to MDAC instead, that way all pplications using it would benefit.

Hello,

I've setuped a web acess to analysis services with this tutorial:
http://www.microsoft.com/technet/prodtechnol/sql/2005/httpasws.mspx

I have the same problem described in this posts:
In Excel 2007 accessing an Analysis Services Cube with this server http://moss/olap/msmdpump.dll
Is OK

When I access with HTTPS (https://moss/olap/msmdpump.dll) Excel responds
"Either a connection could not be made to the moss server, or Analysis Services is not Running on the computer specified"

The IIS has this lines in the log file:
Code Snippet

2008-02-06 17:22:05 W3SVC1714963100 192.168.60.189 POST /msmdpump.dll/msolap.asp - 443 - 192.168.60.189 Light+DCube 401 1 0
2008-02-06 17:22:05 W3SVC1714963100 192.168.60.189 POST /msmdpump.dll/msolap.asp - 443 MOSS\Administrator 192.168.60.189 Light+DCube 200 0 0

I assume the excel 2007 can't trust my certificate. In Internet explorer I can add it to the trusted roots but in excel 2007 I can't
When I go to the trusted Locations from the excel 2007 trust center I can't add the https://moss/ site aparently only disc locations are allowed.

Do I have to Pay a supplier of certificates like verisign to make excel 2007 trust my certificate??

I should have the options to import a certificated to the trust center of excel 2007 like I do in Internet Explorer..

askmehow

hi,
We are got the HTTP Access of SSAS 2005 working, Our Client is Excel 2003.
But we are having problem over HTTPS.

Does anybody here knows why?We have enabled SSL and created our own SSL certificate and enabled Basic Authentication.

Thanks,
Vinod

I'm currently experiencing the same problem in a simpler setup:

- no HTTPS, everything is HTTP
- no ISA firewalls or proxy servers
- clients are not in a domain trusted by the server's domain, so credentials must be passed
- IIS is using NTLM authentication. All clients can authenticate ok if they manually browse to the URL in IE

Client #1 is Excel 2003 running on Windows Server 2003 and works ok
Client #2 is Excel 2007 running on Windows XP and fails with the above symptoms

As with the previous cases, the client that isn't working is attempting to hit msolap.asp with the wrong user agent and the client that is working is using msmdpump.dll. In the failing client we have tried using "Other/Advanced" in the connection wizard then manually picked the analysis services 9.0 provider from the list rather than just choosing "SQL Server Analysis Services" in the first dialog and letting it figure out which version to use, but that didn't change anything.

I am having exactly the same issue. How did you go about solving this using a temp cert, instead of the cert from CA authroties?

Same problem with Excel 2013  and SSAS 2012.  

Cert is from an authority and matches domain.  Cert is also trusted by Excel. 

Is there an answer for this?