Using SQL Server SMO with a Port number

Question

In SSMS if you are connecting to SQL Server instance set to specific port other than 1433 you simply put ",port number" after the instance name. In the same regard if I am using System.Data.SqlClient.SqlConnection and want to populate a data set with System.Data.DataSet, I would do the same when specifying the Data Source portion of the connection string to pass into that object.

Is there not a way to do this using Microsoft.SqlServer.Management.Smo.Server class?

I have tried variations with no luck like:

$cnString = "Data Source=Server\instance,port;Initial Catalog=master;Trusted_Connection=true;"
$cn = new-object system.data.sqlclient.sqlconnection($cnstring)
$cnSql = New-Object Microsoft.sqlserver.management.common.serverconnection($cn)
$s = New-Object Microsoft.sqlserver.management.smo.server($cnSql)
$s | Select Name, Version

Or just trying:

$s = New-Object Microsoft.sqlserver.management.smo.server "Server\Instance,port"
$s | Select Name, Version

Answer 1

One thing you could do is just set the ServerConnection portion of the Server object to an explicit connection string, specifying the port name.

You could either do this by accessing the Server.ConnectionContext property:

$ConnectionString = "data source = yourinstancename,10055; initial catalog = master; trusted_connection = true;"

$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server

$SqlServer.ConnectionContext.ConnectionString = $ConnectionString

$SqlServer.Databases |
    Select-Object Name

Or by passing the ServerConnection object in when you instantiate the Server object:

$ConnectionString = "data source = yourinstancename,10055; initial catalog = master; trusted_connection = true;"

$ServerConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$ServerConnection.ConnectionString = $ConnectionString

$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($ServerConnection)

$SqlServer.Databases |
    Select-Object Name

EDIT: I just re-read your question, and it seems like one of your attempts is basically identical to my second one up above. I took your code and all I changed was the data source portion of your connection string, and it worked just fine for me:

$cnString = "Data Source=Server\instance,port;Initial Catalog=master;Trusted_Connection=true;"
$cn = new-object system.data.sqlclient.sqlconnection($cnstring)
$cnSql = New-Object Microsoft.sqlserver.management.common.serverconnection($cn)
$s = New-Object Microsoft.sqlserver.management.smo.server($cnSql)
$s | Select Name, Version

Answer 2

You can specify TCP/IP as the network protocol using the 'Network Library=DBMSSOCN' connection setting. Then the indicated SQL Server port is used:

$cnString = "Data Source=Server\instance,port;Network Library=DBMSSOCN;Initial Catalog=master;Trusted_Connection=true;"

Here's a link with additional information: SqlConnection -> Connect via an IP address

This assumes that you have enabled TCP/IP connections to SQL Server: Enable TCP/IP Network Protocol for SQL Server