Monthly Archive


Connecting via SMO to a named instance

A question came up in tonight’s User group session regarding connecting to SQL server instances using SMO

If you have just a default instance – just give the server name

$server = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.Server" -ArgumentList "W08R2SQl12"


If you have a named instance the give the instance name as well

$serverI = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.Server" -ArgumentList "W08R2SQl12\instance_name"


These work as long as the SQL Server browser service is running. 

If it isn’t this is what I think you have to do.

This is untested and a best guess. I will try and test.

$cons = "server=W08R2SQL12\instance_name,port_number;Trusted_Connection=true;multipleactiveresultsets=false"

$cn = New-Object -TypeName "System.Data.SqlClient.SqlConnection" -ArgumentList $cons
$serverZ = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.Server" -ArgumentList $cn
If anyone manages to test this please let me know – I’ll test as soon as I can

One Response to Connecting via SMO to a named instance

  • Chad Miller says:

    I tested your code and it works fine. Although generally I’ll omit the instance name if I provide the port number. The instance name isn’t need if the port number is provided, but to be clear it works either way. I also generally use the fully qualified computername ( in order to leverage DNS to re-point SQL Servers in a DR scenario. So the connection string will would be
    $cons = “,port_number;Trusted_Connection=true;multipleactiveresultsets=false”

    Another way of specifying SQL Server port number is to use the SQLConnection object:

    try {add-type -AssemblyName “Microsoft.SqlServer.ConnectionInfo, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91” -EA Stop}
    catch {add-type -AssemblyName “Microsoft.SqlServer.ConnectionInfo”}

    try {add-type -AssemblyName “Microsoft.SqlServer.Smo, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91” -EA Stop; $smoVersion = 10}
    catch {add-type -AssemblyName “Microsoft.SqlServer.Smo”; $smoVersion = 9}

    $con = new-object (“Microsoft.SqlServer.Management.Common.ServerConnection”) “,1862”
    $server = new-object (“Microsoft.SqlServer.Management.Smo.Server”) $con

    Lastly since a connection isn’t made to the SQL Server until properties are retrieved, I prefer to explicitly call $con.Connect() or in your example $cn.Open() before creating my SMO server object.

Leave a Reply