5

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

2 Answers 2

7

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
2
  • I just tried it again and what I had did work. Not to sure what I was doing wrong. However your first example looks much cleaner and more readable to me. I had tried that but couldn't get it to work and see why with your example.
    – user507
    Commented Dec 8, 2013 at 4:39
  • I wasn't using PowerShell, but C#. This solved my problem. I was connecting to a docker container on localhost, 1433 and I banged my head on this for an hour or two. Good work here! Commented May 21, 2022 at 14:28
0

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

1
  • That does not work when I try it.
    – user507
    Commented Dec 7, 2013 at 3:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.