functions/Get-DbaTcpPort.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
function Get-DbaTcpPort {
    <#
  .SYNOPSIS
   Returns the TCP port used by the specified SQL Server.
 
  .DESCRIPTION
   By default, this function returns just the TCP port used by the specified SQL Server.
 
   If -Detailed is specified, the server name, IPAddress (ipv4 and ipv6), port number and an indicator of whether or not the port assignment is static are returned.
 
   Remote sqlwmi is used by default. If this doesn't work, then remoting is used. If neither work, it defaults to T-SQL which can provide only the port.
 
  .PARAMETER SqlInstance
   The SQL Server that you're connecting to.
 
  .PARAMETER Credential
   Allows you to connect to servers using alternate Windows credentials
 
   $scred = Get-Credential, then pass $scred object to the -Credential parameter.
 
  .PARAMETER Detailed
   If this switch is enabled, an object with server name, IPAddress (ipv4 and ipv6), port and static ($true/$false) for one or more SQL Servers is returned.
 
  .PARAMETER ExcludeIpv6
   If this switch is enabled, IPv6 information is excluded from detailed output.
 
  .PARAMETER Silent
   If this switch is enabled, the internal messaging functions will be silenced.
 
  .NOTES
   Tags: SQLWMI, tcp
 
   Website: https://dbatools.io
   Copyright: (C) Chrissy LeMaire, clemaire@gmail.com
   License: GNU GPL v3 https://opensource.org/licenses/GPL-3.0
 
  .LINK
   https://dbatools.io/Get-DbaTcpPort
 
  .EXAMPLE
   Get-DbaTcpPort -SqlInstance sqlserver2014a
 
   Returns just the port number for the default instance on sqlserver2014a.
 
  .EXAMPLE
   Get-DbaTcpPort -SqlInstance winserver\sqlexpress, sql2016
 
   Returns an object with server name and port number for the sqlexpress on winserver and the default instance on sql2016.
 
  .EXAMPLE
   Get-DbaTcpPort -SqlInstance sqlserver2014a, sql2016 -Detailed
 
   Returns an object with server name, IPAddress (ipv4 and ipv6), port and static ($true/$false) for sqlserver2014a and sql2016.
 
   Remote sqlwmi is used by default. If this doesn't work, then remoting is used. If neither work, it defaults to T-SQL which can provide only the port.
 
  .EXAMPLE
   Get-DbaRegisteredServer -SqlInstance sql2014 | Get-DbaTcpPort -ExcludeIpv6 -Detailed
 
   Returns an object with server name, IPAddress (just ipv4), port and static ($true/$false) for every server listed in the Central Management Server on sql2014.
 #>

    [CmdletBinding()]
    param (
        [parameter(Mandatory = $true, ValueFromPipeline = $true)]
        [Alias("ServerInstance", "SqlServer")]
        [DbaInstanceParameter[]]$SqlInstance,
        [Alias("SqlCredential")][PSCredential]$Credential,
        [switch]$Detailed,
        [Alias("Ipv4")]
        [switch]$ExcludeIpv6,
        [switch]$Silent
    )
    
    process {
        foreach ($instance in $SqlInstance) {
            if ($detailed -eq $true) {
                try {
                    $scriptblock = {
                        $instance = $args[0]
                        
                        Add-Type -AssemblyName Microsoft.VisualBasic
                        
                        foreach ($servername in $wmi.ServerInstances) {
                            $instanceName = $servername.Name
                            $wmiinstance = $wmi.Services | Where-Object { $_.DisplayName -eq "SQL Server ($instanceName)" }
                            $vsname = ($wmiinstance.AdvancedProperties | Where-Object { $_ -match 'VSNAME' }).Value
                            
                            if ($vsname.length -eq 0) {
                                $vsname = "$instance\$instanceName"
                            }
                            
                            $vsname = $vsname.Replace("\MSSQLSERVER", "")
                            
                            try {
                                $regroot = ($wmiinstance.AdvancedProperties | Where-Object { $_ -match 'REGROOT' }).Value
                                $dacport = (Get-ItemProperty "HKLM:\$regroot\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp").TcpDynamicPorts
                                
                                [PsCustomObject]@{
                                    ComputerName = $instance
                                    InstanceName = $instanceName
                                    SqlInstance  = $vsname
                                    IPAddress    = "0.0.0.0"
                                    Port         = $dacport
                                    Static       = $false
                                    Type         = "DAC"
                                }
                            }
                            catch {
                                # it's just not our day
                            }
                            
                            $tcp = $servername.ServerProtocols | Where-Object Name -eq Tcp
                            $ips = $tcp.IPAddresses
                            
                            # This is a remote command so do not use Write-message
                            Write-Verbose "Parsing information for $($ips.count) IP addresses."
                            foreach ($ip in $ips) {
                                $props = $ip.IPAddressProperties | Where-Object { $_.Name -eq "TcpPort" -or $_.Name -eq "TcpDynamicPorts" }
                                
                                foreach ($prop in $props) {
                                    if ([Microsoft.VisualBasic.Information]::IsNumeric($prop.value)) {
                                        $port = $prop.value
                                        if ($prop.name -eq 'TcpPort') {
                                            $static = $true
                                        }
                                        else {
                                            $static = $false
                                        }
                                        break
                                    }
                                }
                                
                                [PsCustomObject]@{
                                    ComputerName = $instance
                                    InstanceName = $instanceName
                                    SqlInstance  = $vsname
                                    IPAddress    = $ip.IPAddress.IPAddressToString
                                    Port         = $port
                                    Static       = $static
                                    Type         = "Normal"
                                }
                            }
                        }
                    }
                    
                    $computer = $instance.ComputerName
                    $resolved = Resolve-DbaNetworkName -ComputerName $instance -Verbose:$false
                    $computername = $resolved.FullComputerName
                    
                    try {
                        Write-Message -Level Verbose -Message "Trying with ComputerName ($computer)."
                        $someIps = Invoke-ManagedComputerCommand -ComputerName $computer -ArgumentList $computer -ScriptBlock $scriptblock
                    }
                    catch {
                        Write-Message -Level Verbose -Message "Trying with FullComputerName because ComputerName failed."
                        $someIps = Invoke-ManagedComputerCommand -ComputerName $computername -ArgumentList $fqdn -ScriptBlock $scriptblock
                    }
                }
                catch {
                    Stop-Function -Message "Could not get detailed information." -Target $instance -ErrorRecord $_
                }
                
                $cleanedUp = $someIps | Sort-Object IPAddress
                
                if ($ExcludeIpv6) {
                    $octet = '(?:0?0?[0-9]|0?[1-9][0-9]|1[0-9]{2}|2[0-5][0-5]|2[0-4][0-9])'
                    [regex]$ipv4 = "^(?:$octet\.){3}$octet$"
                    $cleanedUp = $cleanedUp | Where-Object { $_.IPAddress -match $ipv4 }
                }
                
                $cleanedUp
            }
            
            if ($Detailed -eq $false -or ($Detailed -eq $true -and $someIps -eq $null)) {
                try {
                    $server = Connect-SqlInstance -SqlInstance "TCP:$instance" -SqlCredential $Credential -MinimumVersion 9
                }
                catch {
                    Stop-Function -Message "Failure" -Category ConnectionError -ErrorRecord $_ -Target $servername -Continue
                }
                
                # WmiComputer can be unreliable :( Use T-SQL
                $sql = "SELECT local_tcp_port FROM sys.dm_exec_connections WHERE session_id = @@SPID"
                $port = $server.Query($sql)
                
                [PSCustomObject]@{
                    ComputerName = $server.NetName
                    InstanceName = $server.ServiceName
                    SqlInstance  = $server.DomainInstanceName
                    Port         = $port.local_tcp_port
                }
            }
        }
    }
}