-
Notifications
You must be signed in to change notification settings - Fork 43
Enumerate SQL Server Instances and TCP Settings
Malcolm Stewart edited this page Oct 29, 2020
·
2 revisions
These scripts require PowerShell. If you have multiple instances of SQL Server on the machine, they will enumerate them all.
Get-WMIObject -Class 'Win32_Service' | Where-Object {($_.Name -match 'MSSQL(?:\$.+|SERVER)')} | Format-List -Property Name, State, ProcessID;
-----------------------------------------
Name : MSSQL$MICROSOFT##WID
State : Stopped
ProcessID : 0
Name : MSSQLSERVER
State : Running
ProcessID : 7696
Name : MSSQLServerOLAPService
State : Stopped
ProcessID : 0
[String]$NameSpaceName = (Get-WMIObject -NameSpace 'root\Microsoft\SqlServer' -Class '__NameSpace' | Where-Object {$_.Name -match 'ComputerManagement'} | Sort-Object -Property Name -Descending | Select-Object -First 1).Name;
If ($False -eq [String]::IsNullOrEmpty($NameSpaceName)) {
Get-WMIObject -NameSpace "root\Microsoft\SqlServer\$($NameSpaceName)" -Query "SELECT * FROM ServerNetworkProtocol WHERE ProtocolName = 'Tcp'" | Format-List -Property PSComputerName, InstanceName, ProtocolDisplayName, Enabled;
} Else {
'The SQL Server WMI provider does not appear to be installed on this machine.' | Write-Host -ForegroundColor 'Red';
};
----------------------------------------
PSComputerName : SQLPROD01
InstanceName : MSSQLSERVER
ProtocolDisplayName : TCP/IP
Enabled : True
[String]$NameSpaceName = (Get-WMIObject -NameSpace 'root\Microsoft\SqlServer' -Class '__NameSpace' | Where-Object {$_.Name -match 'ComputerManagement'} | Sort-Object -Property Name -Descending | Select-Object -First 1).Name;
If ($False -eq [String]::IsNullOrEmpty($NameSpaceName)) {
Get-WMIObject -NameSpace "root\Microsoft\SqlServer\$($NameSpaceName)" -Query "SELECT * FROM ServerNetworkProtocolProperty WHERE ProtocolName = 'Tcp' AND PropertyName = 'TcpPort' AND IPAddressName = 'IPAll'" | Format-List -Property InstanceName, ProtocolName, IPAddressName, PropertyName, PropertyStrVal;
} Else {
'The SQL Server WMI provider does not appear to be installed on this machine.' | Write-Host -ForegroundColor 'Red';
};
---------------------------------
InstanceName : MSSQLSERVER
ProtocolName : Tcp
IPAddressName : IPAll
PropertyName : TcpPort
PropertyStrVal : 1433
Get-WMIObject -Class 'Win32_Service' | Where-Object {(($_.Name -match 'MSSQL(?:\$.+|SERVER)') -and ($_.State -eq 'Running'))} | ForEach-Object {
$ServiceName = $_.Name;
$ProcessID = $_.ProcessID;
"$($ServiceName) - PID $($ProcessID)" | Write-Host -ForegroundColor 'Cyan';
& 'NetStat.exe' -ano | Where-Object {$_ -match "\s\s+LISTENING\s\s+$($ProcessID)"} | Write-Host -ForegroundColor 'Cyan';
};
-----------------------------
MSSQLSERVER - PID 7696
TCP 0.0.0.0:1433 0.0.0.0:0 LISTENING 7696
TCP 127.0.0.1:1434 0.0.0.0:0 LISTENING 7696
TCP [::]:1433 [::]:0 LISTENING 7696
TCP [::1]:1434 [::]:0 LISTENING 7696