Https access to analysis service not working from SQLServer powershell module

Question

question

OleFriisNielsen-9362 asked Oct 14, '21 RichMatheisen-8856 commented Oct 28, '21

Https access to analysis service not working from SQLServer powershell module

I have setup analysis services on iis following this guide: configure-http-access-to-analysis-services-on-iis-8-0 and have added iisClientCertificateMappingAuthentication on top. This works fine when accessing the website from Excel, PowerBI and SSMS. However when I want to extract data with the SQLServer powershell module via invoke-ascmd, I only get a error back that a connection to the server couldn't be made.

I have tried connecting to the server with the powershell module in various ways and right now I'm at the following point:

Client -> IIS Server : Works from Excel, PowerBI and SSMS, but not invoke-ascmd.
IIS Server -> IIS Server Public IP : Works from Excel, PowerBI and SSMS, but not invoke-ascmd.
IIS Server -> IIS Server 127.0.0.1 : Works from Excel, PowerBI, SSMS and invoke-ascmd.

So now I'm struggling with trying to figure out why only the SQLServer module is being denied when connecting to the public IP, but works on 127.0.0.1.

windows-server-powershell windows-server-iis sql-server-analysis-services

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2021-10-28T12:57:30.853Z

OleFriisNielsen-9362 answered Oct 28, '21 RichMatheisen-8856 commented Oct 28, '21

Turned out that the server didn't have TLS 1.0 enabled which is what Powershell was using, after enabling TLS 1.0 on the server everything worked as expected.

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RichMatheisen-8856 · Oct 28, 2021 at 09:30 PM

Nobody should be using TLS 1.0 anymore! Or TLS 1.1 either for that matter!

PowerShell uses the TLS protocols that are active on your machine. It will negotiate with the other machine to agree on a protocol to use. You should ask the owner of the web site why they continue to use an unsecure version of TLS.

0 ·

Answer 2 · 2021-10-14T15:11:36.787Z

RichMatheisen-8856 answered Oct 14, '21

Are you running Excel, PowerBi, and SSMS using the same user account as the SQL server uses? Does the SQL service account have permission/rights to access the network?

It sounds like it's a pretty product-specific problem (either IIS or SQL).

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question