Determine the port that SQL is using or set it if needed via program

Question

1) I'm looking for a way within a VB.Net/ADO.Net program to determine if and what port an named instance of Microsoft SQL is using without resorting to a registry key (too many different places to look), hacked event log (unreliable), or opening SQL Configuration Manager (again, I need to do this is a program).

2) If I determine that SQL is not configured to use TCP then is there a reliable way through VB.Net/ADO.Net to enable TCP/IP and set a port?

In both cases these functions would be running with elevated privileges during a setup.

score 0 · Accepted Answer · answered Sep 10 '14 at 02:04

I ply my trade in powershell, but you should be able to translate this pretty well. NB: in my testing, the hash keys (e.g. 'MSSQLSERVER', 'Tcp') were case-sensitive.

$machine = new-object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer '.'
$t = $machine.ServerInstances['MSSQLSERVER'].ServerProtocols['Tcp']
$t.IsEnabled = $true; #enable TCP
$port = $t.IPAddresses['IPAll'].IPAddressProperties['TcpPort'];
if ($port.Value -ne '1433') {
   $port.Value = '1434'; #or whatever port you want to set it to
}
$t.alter();

Determine the port that SQL is using or set it if needed via program

1 Answers1