Connecting to SSAS via HTTPS through Excel not possible

Question

Hello there,

I've set up web access to my SSAS database today. Both SSAS and IIS are running on a remote server in the same domain as my PC. The initial configuration was done with HTTP, and I had no problem reaching the HTTP SSAS access on my PC with SSMS and Excel.

I have then changed the binding of my IIS site to HTTPS and deleted the HTTP binding. I already had a certificate ready so doing this was no problem either. I adjusted the firewall settings and was able to reach my new URL https://servername.domainname.de/OLAP/msmdpump.dll both through Edge and SSMS.

However my Excel access is now not working and I have no idea why, so I thought I'd ask the experts.

When I go to Excel and try to Get Data from Analysis Services, and enter the aforementioned URL, I get an error saying that either Excel cannot establish a connection to my remote server, or that my remote server is not running SSAS. Since SSMS can reach the URL, both of those possibilities seem to be ruled out for me. The weirdest thing is that when I select "From SQL Server Analysis Services Database (Import)" instead of "From Analysis Services", Excel can reach the URL.

I really think that there is something wrong with making a live connection using Excel, since my SSMS can connect to the whole SSAS Project, meaning the URL itself works and my Firewall is set up properly.

Does anyone have any idea how I could fix this issue?

Answer 1

Here are some possible reasons:

Service start issue: Even though you said SSMS can connect to the URL, it may be that the SSAS service is not running on the remote server or is not properly configured. You can try restarting the "SQL Server Analysis Services" service on the server to address this issue. In the service manager, find the "SQL Server Analysis Services" service, right-click and select "Restart".

Compatibility issue: Make sure your Excel version is compatible with the Analysis Services version. If there is version compatibility issues, a connection may not be established.

If none of the above suggestions solve your problem, I‘d’ suggest you raise a support ticket to Microsoft.

Please contact your admin/IT department create a support ticket via Microsoft 365 Admin Center> Support> New service request. That support team there will have the correct channel and resources to help you investigate more and find what exactly the reason has caused this situation.