神刀安全网

Configure SQL Server TCP Protocol using SMO

Posted inSQL Server

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) }
  $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')
  $SMO = 'Microsoft.SqlServer.Management.Smo.' $WMIObject = New-Object ($smo + 'Wmi.ManagedComputer')   

Configure SQL Server TCP Protocol using SMO

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 Configure SQL Server TCP Protocol using SMO

Read Protocols Configuration

Let’s have a closer look at ClientProtocols property.

$WMIObject.ClientProtocols
  $WMIObject.ClientProtocols   

Configure SQL Server TCP Protocol using SMO

We have much more informations and one that you’ll use to access the property of a protocol this is the URN Configure SQL Server TCP Protocol using SMO 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
  $WMIObject | Get-Member   

Configure SQL Server TCP Protocol using SMO

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)
  $URN = "ManagedComputer[@Name='LABSQL2K14']/ServerInstance ` [@Name='TEST1']/ServerProtocol[@Name='Tcp']" $TCPProtocol = $WMIObject.GetSmoObject($URN)   

Configure SQL Server TCP Protocol using SMO

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

  $Assemblies= "Microsoft.SqlServer.Management.Common", "Microsoft.SqlServer.Smo", "Microsoft.SqlServer.SqlWmiManagement "   Foreach ($Assembly in $Assemblies) {     $Assembly = [Reflection.Assembly]::LoadWithPartialName($Assembly) }   

0

  $Assemblies= "Microsoft.SqlServer.Management.Common", "Microsoft.SqlServer.Smo", "Microsoft.SqlServer.SqlWmiManagement "   Foreach ($Assembly in $Assemblies) {     $Assembly = [Reflection.Assembly]::LoadWithPartialName($Assembly) }   

1

Target a specific IPAddress

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

Configure SQL Server TCP Protocol using SMO

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

  $Assemblies= "Microsoft.SqlServer.Management.Common", "Microsoft.SqlServer.Smo", "Microsoft.SqlServer.SqlWmiManagement "   Foreach ($Assembly in $Assemblies) {     $Assembly = [Reflection.Assembly]::LoadWithPartialName($Assembly) }   

2

  $Assemblies= "Microsoft.SqlServer.Management.Common", "Microsoft.SqlServer.Smo", "Microsoft.SqlServer.SqlWmiManagement "   Foreach ($Assembly in $Assemblies) {     $Assembly = [Reflection.Assembly]::LoadWithPartialName($Assembly) }   

3

Configure SQL Server TCP Protocol using SMO

Exactly what we need! And everything is writable.

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

  $Assemblies= "Microsoft.SqlServer.Management.Common", "Microsoft.SqlServer.Smo", "Microsoft.SqlServer.SqlWmiManagement "   Foreach ($Assembly in $Assemblies) {     $Assembly = [Reflection.Assembly]::LoadWithPartialName($Assembly) }   

4

  $Assemblies= "Microsoft.SqlServer.Management.Common", "Microsoft.SqlServer.Smo", "Microsoft.SqlServer.SqlWmiManagement "   Foreach ($Assembly in $Assemblies) {     $Assembly = [Reflection.Assembly]::LoadWithPartialName($Assembly) }   

5

And to make sure that changes are applied

  $Assemblies= "Microsoft.SqlServer.Management.Common", "Microsoft.SqlServer.Smo", "Microsoft.SqlServer.SqlWmiManagement "   Foreach ($Assembly in $Assemblies) {     $Assembly = [Reflection.Assembly]::LoadWithPartialName($Assembly) }   

6

  $Assemblies= "Microsoft.SqlServer.Management.Common", "Microsoft.SqlServer.Smo", "Microsoft.SqlServer.SqlWmiManagement "   Foreach ($Assembly in $Assemblies) {     $Assembly = [Reflection.Assembly]::LoadWithPartialName($Assembly) }   

7

Configure SQL Server TCP Protocol using SMO

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.

  $Assemblies= "Microsoft.SqlServer.Management.Common", "Microsoft.SqlServer.Smo", "Microsoft.SqlServer.SqlWmiManagement "   Foreach ($Assembly in $Assemblies) {     $Assembly = [Reflection.Assembly]::LoadWithPartialName($Assembly) }   

8

  $Assemblies= "Microsoft.SqlServer.Management.Common", "Microsoft.SqlServer.Smo", "Microsoft.SqlServer.SqlWmiManagement "   Foreach ($Assembly in $Assemblies) {     $Assembly = [Reflection.Assembly]::LoadWithPartialName($Assembly) }   

9

Changes are visible in your SQL Server Configuration Manager

Configure SQL Server TCP Protocol using SMO

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

原文  https://pwrshell.net/configure-sql-server-tcp-protocol-using-smo/

转载本站任何文章请注明:转载至神刀安全网,谢谢神刀安全网 » Configure SQL Server TCP Protocol using SMO

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
分享按钮