Configure SQL Server TCP Protocol using SMO

 

I’ve started working on a new version of xSQLServer DSC Resource which in my opinion lacks few very important feature to be totally used in production. The first thing i was looking for in this bunch of resources was a meaning to configure TCP connexions. We don’t use dynamic ports in production in most of our customers, so we have a bunch of scripts to configure the registry key, and it’s working pretty good. But i knew from long time that i can also do this using PowerShell and SMO assemblies. So let’s have a look about how it works.

As SMO are assemblies, you need to have SQL Server (maybe only Management Studio?) installed on the computer that’ll execute scripts. Microsoft says that the prefered way of loading assemblies, is to import the SQLPS Module. Well, ok, but we may want to use only SMO assemblies needed for our task 😉

Load SMO Assemblies

So let’s use only the three assemblies that i need to access TCP protocol configuration

$Assemblies=
"Microsoft.SqlServer.Management.Common",
"Microsoft.SqlServer.Smo",
"Microsoft.SqlServer.SqlWmiManagement "

Foreach ($Assembly in $Assemblies) {
    $Assembly = [Reflection.Assembly]::LoadWithPartialName($Assembly)
}

Connect to SQL Server WMI

We have all what we need loaded in the GAC, let’s do some PowerShell now! And the first thing to do is to build an object to access WMI informations about SQL Server.

$SMO = 'Microsoft.SqlServer.Management.Smo.'
$WMIObject = New-Object ($smo + 'Wmi.ManagedComputer')

We have few informations in this object, the services associated to SQL Server, the client protocols, the instance(s) available and the Name of the server. Seems enough for me right now 🙂

Read Protocols Configuration

Let’s have a closer look at ClientProtocols property.

$WMIObject.ClientProtocols

 

We have much more informations and one that you’ll use to access the property of a protocol this is the URN 🙂 Why ? because everything we saw until there is just readable, and we need to write configuration.

Change Protocols Configuration

You can see that if you use my favourite CMDLet

$WMIObject | Get-Member

If you look at all the properties, they have all a get ability. So we’ll use the GetSMoObject method and use the URN property to access target data.

$URN = "ManagedComputer[@Name='LABSQL2K14']/ServerInstance `
[@Name='TEST1']/ServerProtocol[@Name='Tcp']"
$TCPProtocol = $WMIObject.GetSmoObject($URN)

We have now access to everything we need to change TCP Configuration for our SQL Server accessibility. For example, if you want to list every IP addresses available for the configuration you just have to do the following

$TCPProtocol.IPAddresses

Target a specific IPAddress

This will return many lines. Let’s have a closer look.

There is a property called IPaddressProperties that seems pretty handy for what we need 😀

$TCPProtocol.IPAddresses | ? { $_.ipAddress -eq '10.0.1.9' } | 
      select -ExpandProperty IPAddressProperties | ft -AutoSize

Exactly what we need! And everything is writable.

So, in order to modify them, it’s easy to do the following

$NIC = $TCPProtocol.IPAddresses | ? { $_.ipAddress -eq '10.0.1.9' }
$NIC.IPAddressProperties[4].Value="12345"
$NIC.IPAddressProperties[1].Value=$true

And to make sure that changes are applied

$TCPProtocol.Alter()

Change for all IPAddress

This is pretty the same. But we’ll use a full URN to access the IPAll IPAddress that’ll have exactly the same name everytime, so pretty easy to target it like that.

$WMIObject.GetSmoObject($URN + "/ `
   IPAddress[@Name='IPAll']").IPAddressProperties[1].Value="1433"
$TCPProtocol.Alter()

Changes are visible in your SQL Server Configuration Manager

OK this is everything for this post. If you have any comments or needs any precision, feel free to post bellow.