Remote connections to instances with a non-default port time out

[WesToleman]

The SQL Powershell path provider times out when attempting to remotely access instances running on ports other than 1433.

Measure-Command { Get-Item 'SQLSERVER:\SQL\PrimaryServer\DEFAULT' } | Select-Object -ExpandProperty TotalSeconds
# Get-Item : Cannot retrieve the dynamic parameters for the cmdlet. SQL Server PowerShell provider error: Could not connect to 'PrimaryServer\DEFAULT'. [Failed to connect to server PrimaryServer. --> A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) --> Access is denied]
# 117.6668215

Measure-Command { Get-Item 'SQLSERVER:\SQL\PrimaryServer\DEFAULT\Databases' } | Select-Object -ExpandProperty TotalSeconds
# Get-Item : Cannot retrieve the dynamic parameters for the cmdlet. SQL Server PowerShell provider error: Could not connect to 'PrimaryServer\DEFAULT'. [Failed to connect to server PrimaryServer. --> A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) --> Access is denied]
# 205.5115042

Accessing the root path is fast and Invoke-SqlCmd connects to the instance without issue

Measure-Command { Get-Item 'SQLSERVER:\SQL\PrimaryServer' } | Select-Object -ExpandProperty TotalSeconds
# 0.0078557

Measure-Command { Invoke-SqlCmd -ServerInstance 'PrimaryServer,1455' -Query 'select @@version' } | Select-Object -ExpandProperty TotalSeconds
# 0.060131

There's no issue when accessing the instance from the host itself

Measure-Command { Get-Item 'SQLSERVER:\SQL\localhost' } | Select-Object -ExpandProperty TotalSeconds
# 0.0086672

Measure-Command { Get-Item 'SQLSERVER:\SQL\localhost\DEFAULT' } | Select-Object -ExpandProperty TotalSeconds
# 0.0191936

Measure-Command { Get-Item 'SQLSERVER:\SQL\localhost\DEFAULT\Databases' } | Select-Object -ExpandProperty TotalSeconds
# 0.0171693

Remotely accessing both default and named instances running on port 1433 works without issue.

Is there something I can reconfigure to work around this issue?

[shueybubbles]

Do connections with SSMS or other tools work from the same client machine? I'd guess the port is blocked by the firewall on the host. Maybe try a tcp: connection instead of named pipes.

[WesToleman]

Connections with SSMS and Invoke-SqlCmd both work so it's not a firewall issue.

[Matteo-T]

I've moved it over to where it (may) belong, i.e. the SQLServer PowerShell repo (which I recently opened for Issues).

https://github.com/microsoft/SQLServerPSModule/issues/21

[WesToleman]

Hi @Matteo-T, have you accidentally closed the transferred issue or is this solved?

[Matteo-T]

@WesToleman - it was actually intentional. Based on my preliminary understanding, this has nothing to do with SMO and it's rather some SQL PowerShell issue. If that turns out to be incorrect, I'll move it back here and let David know.

[WesToleman]

@Matteo-T, I'm confused, this is issue 21 in the SqlServer PowerShell Module repository. I'm writing this comment from https://github.com/microsoft/SQLServerPSModule/issues/21.

[Matteo-T]

Oh I see. In that case, yes. I goofed up. Let me make it right. Sorry about that.