question

OleFriisNielsen-9362 avatar image
0 Votes"
OleFriisNielsen-9362 asked RichMatheisen-8856 commented

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-powershellwindows-server-iissql-server-analysis-services
5 |1600 characters needed characters left characters exceeded

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

OleFriisNielsen-9362 avatar image
0 Votes"
OleFriisNielsen-9362 answered RichMatheisen-8856 commented

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

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

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 Votes 0 ·
RichMatheisen-8856 avatar image
0 Votes"
RichMatheisen-8856 answered

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

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