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.
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.
1 additional answer
Sort by: Most helpful
-
Rich Matheisen 45,091 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).