Https access to analysis service not working from SQLServer powershell module

Ole Friis Nielsen 96 Reputation points
2021-10-14T07:31:38.393+00:00

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.

Internet Information Services
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,244 questions
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,362 questions
0 comments
Accepted answer
  1. Ole Friis Nielsen 96 Reputation points
    2021-10-28T12:57:30.853+00:00

    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 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Rich Matheisen 44,776 Reputation points
    2021-10-14T15:11:36.787+00:00

    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).

    0 comments