Accessing Analysis Services from Excel 2010 client over HTTPS
-
Sunday, February 17, 2013 1:33 PM
I have configured a SharePoint 2010 farm consisting of 1 WFE, 1 APPSVR, 1 DB Server, 1 SSAS server. The web application hosted on WFE1 is SSL enabled. The site also hosts SSRS reports. Kerberos is setup appropriately since PPS, SSRS reports are working fine so I dont think there is an error in Kerb configuration. WFE is internet facing and the users will also be accessing SSAS from their desktop using Excel 2010 client(not excel services). So i had to go through SSAS over HTTP configuration. The url that I provide is http://servername/OLAP/msmdpump.dll and the connection string in shared data source is as follows:
Data Source=http://servername/OLAP/msmdpump.dll;Initial Catalog=CubeName
Test connection fails with the error message "Connection was lost". This I guess has something to do with SSAS over HTTPS configuration but I'm not sure how to setup that.
All Replies
-
Monday, February 18, 2013 1:32 AM
Hi Samrat,
Not sure if this is your issue, but you should give the database name in the Catalog parameter, not the cube name. The cube name is in the query string.
Richard
-
Monday, February 18, 2013 5:52 AM
Hi Richard,
I'm sorry here, by CubeName i meant Analysis Services Database name i.e.
Data Source=http://servername/OLAP/msmdpump.dll;Initial Catalog=SSASDatabaseName
-Samrat
- Edited by Samrat Bhatnagar Monday, February 18, 2013 5:52 AM Correction
-
Wednesday, February 20, 2013 6:37 AMModerator
Hi Samrat,
If you want to test the connection quickly, please refer to the following steps:
- In a Excel, Click "Connections".
- In "Get External Data" section, click "From Other Souces" and then choose "From Analysis Services".
- In Server name, enter the HTTP address of the msmdpump extension: http://my-web-srv01/OLAP/msmdpump.dll.
Here are some related articles about HTTP access to Analysis Services:
http://technet.microsoft.com/en-us/library/gg492140.aspx
http://social.technet.microsoft.com/Forums/en/sqlanalysisservices/thread/da306309-8e83-4806-8f7f-044eff3a3fdaIf you have any feedback on our support, please click here.
Regards,
Elvis Long
TechNet Community Support
-
Wednesday, February 20, 2013 12:07 PM
Hi All,
It seems that my Excel service application configuration was correct. The server where MSMDPUMP was hosted had SSL installed. I was able to connect to HTTPS://ServerName/OLAP/MSMDPUMP.dll from SSMS over the domain and from internet as well. But Excel 2010 client simply refused to connect to the ODC hosted on SharePoint. After 2 days of frustration in resolving this the frustration grew even more when this started working. No rocket science here. Just one change was required. After creating connection which pointed to ODC file or MSMDPUMP a dialog box appears where you need to provide Username and Password. In the dialog box i was earlier entering the credentials WITHOUT saving them but Excel wasnt able to refresh. This time i decided to save the password in the dialog box and things worked. You now know the reason for me to get more frustrated. Thanks to the guy that figured this. I wish Excel could take the credentials once in for all for refreshing the connection and ask for password only when required.
Thanks you all for taking time to pour in your suggestions. I hope the above helps others as well. I left out the shared data source creation option since that wasnt required for SSAS internet access via Excel over HTTPS.
-Samrat
-
Friday, March 08, 2013 5:38 PM
Hi Samrat
Thank you for posting your solution. But I am still not clear on how to proceed. Can you please provide step by step instructions to resolve this issue? We are not able to connect to HTTPS://ServerName/OLAP/MSMDPUMP.dll using Excel 2010 using basic authentication. However we were able to connect using HTTP.
Thank you
-Ramesh
-
Friday, March 08, 2013 6:11 PM
Elvis
How can I use https connection instead of http connection to http://my-web-srv01/OLAP/msmdpump.dll. I wanted to use
https://my-web-srv01/OLAP/msmdpump.dll.
Regards
Ramesh
-
Friday, March 08, 2013 7:49 PM
Hi Ramesh,
Just to clarify in my scenario i was using Windows Authentication for accessing SSAS via HTTPS/ HTTP. Did you also mean to refer to same mechanism of authentication in your case?
For HTTPS to work SSL certificate needs to be installed on the server where MSMDPUMP is configured. Once this is done configure the IIS web application hosting MSMDPUMP to be SSL enabled. To test if IIS web application is HTTPS enabled you can add a HTML file by the name default.htm in the OLAP directory and then try to access the below mentioned url from IE:
If the above url shows up displaying contents of default.htm that would confirm HTTPS is configured for the web application and the msmdpump url would also work. This I had to do because OLAP dir dosent have any web content
One crucial point is that if you access HTTPS url from a PC that is not in the same domain as that of computer hosting OLAP dir then while connecting to OLAP HTTPS url via Excel 2010, when excel asks for credentials make sure to save the credentials. In my scenario saving the credentials worked.
Regards,
Samrat
-
Wednesday, March 13, 2013 1:17 PM
Thank you Samrat for responding.
1. Created a temp certificate on my dev server using IIS 7.0. Configured the site https://my-web-srv01 : Note I did not use the OLAP folder, I am guessing that is necessary so I have my msmdpump.dll and ini etc.. in the website root.
2. Set the Basic Authentication for the website. Now I am able to connenct to the SSAS using Excel 2010 and 2013 using HTTP connection. That test indicates that my basic authentication is working and I am able to connect to the SSAS instance.
3. Used IE9 to browse the https site and got the server certificate and in stall it in client computer's trusted root certification authorties folder, as suggested by other bloggers.
3. Now I changed the http to https in the connection, leaving the rest of the paramters the same as in step#2, then I get the following error
"Either a connection cannot be made <serverver>. or SSAS is not running on the computer specified.
The issue seems to me with HTTPS connection issue between excel and website that is hosting the msmdpump.dll. I am not sure what else is needed besides having the website certificate added to the trusted root authorities. I tested with IE9 also that IE9 does not complain about the ceritificate anymore after adding to the tursted root cert authorities folder. It is the excel that seems to be having problems. Unfortunately I cann not use the SS Management Studio, since I am using basic authentication vs windows authentication. Any ideas how to resolve this issue?
Thank you
Ramesh
All that I I read some blogs and Added website certificated to the client computer's trusted root certificates folder. The server and the Client computer are in different domains.
-
Wednesday, March 13, 2013 2:47 PM
Hi Ramesh,
You have mentioned that you are using Basic Authentication(BA). BA in MSMDPUMP.DLL scenario is something i'm yet to explore and hence i may not be able to help you.
Some of my learning from using Windows Authentication in MSMDPUMP that might help you are as follows:
1. SSL certificate from a trusted provider(like Verisign etc) is required to be installed on the server only. Not sure how to go about in the scenario if SSL certificates are self generated.
2. It doesnt matter if server with MSMDPUMP and client are on different domains. The whole point of MSMDPUMP is to make cubes available over internet. In case of differing domains the excel client will ask for user name and password. When it asks make sure to save the password in the dialog box and things work.
Regards,
Samrat
.png)
