SQL Protocol How to secure and disable unnecessary protocol with a Power Shell script

As part of a project at one of my customers I was asked to create an automatic installation that will be a secure installation and will use an Alias, disable unnecessary protocols and use a fix port number instead of dynamic.

In my last post I shared with you all a Power Shell script that helped us to create SQL Alias automatically as part of an automatic installation.

This time I'd like to share how this can be done as a part of securing a SQL server and disable unnecessary protocols like Named Pipe, VIA, Shared Memory and setting the port number to a fixed port.

First part the property declaration, in this part we will choose the instance name, port number, connection string and which protocol to disable.

Second Part SMO declaration and the manage SMO object

Third part is the protocol reference

Forth part is the port part where we are fixing the port

-----------------

 # propertyies declarations
 $computerName="."
 $instance="(local)"
 $tcpPort='1434'
 $ProtocolUri="ManagedComputer[@Name='$env:computername']/ServerInstance[@Name='MSSQLSERVER']/ServerProtocol"
 $tcpEnabled=$true
 $npEnabled=$false
 $smEnabled=$true
  
  
 # load the sqlsmo assemblies
 # $null = suppress output of assembly loading
 $null=[reflection.assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo')
 $null=[reflection.assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement')
  
 # create a managed computer smo object
 $ManagedComputer=New-ObjectMicrosoft.SqlServer.Management.Smo.WMI.ManagedComputer$computerName
  
 # get refernce to server protocols
 $tcp=$ManagedComputer.GetSmoObject("$ProtocolUri[@Name='Tcp']")
 $np=$ManagedComputer.GetSmoObject("$ProtocolUri[@Name='Np']")
 $sm=$ManagedComputer.GetSmoObject("$ProtocolUri[@Name='Sm']")
  
 # enable or disbale protocol
 $tcp.IsEnabled=$tcpEnabled
 $tcp.Alter()
  
 $np.IsEnabled=$npEnabled
 $np.Alter()
  
 $sm.IsEnabled=$smEnabled
 $sm.Alter()
  
  
  
 # determine if the instance is default or named
 $smoTcp=if('.','(local)'-contains$instance) 
 {
 # Default database instance
 $smoComputer.ServerInstances['MSSQLSERVER'].ServerProtocols['Tcp']
 } 
 else
 { 
 # Named database instance
 $smoComputer.ServerInstances[$instance].ServerProtocols['Tcp']
 }
  
  
 # assign new port number to tcp protocol $tcpPort is the instance port declared at the top of this script
 $smoTcp.IPAddresses['IPAll'].IPAddressProperties['TcpPort'].Value =$tcpPort
 $smoTcp.Alter()

We can perform lots of tweaks and tricks on SQL Server, using the Power Shell script. Use it if you need to secure servers.