Can't connect to SQL Server database server on port 1433

Question

I am trying to start scripting PowerShell SQL queries for a task, but before that i am testing to make sure my DB connection works.

I already created a table in SQL Server using SSMS 17, and as part of connection test, I am testing if I can connect to the database server fine on port 1433 (which is also opened in firewall rules).

This is the snippet I'm using to test the port connection to SQL Server:

$port   = 1433

$tcp = New-Object Net.Sockets.TcpClient
if ([void]$tcp.Connect($dbhost, $port)) {
  'connected'
} else {
  'not connected'
}
$tcp.Dispose()

where $dbhost = myservername.domain.com

Every time I run the script it returns:

not connected

Why is that?

I checked the server product in SSMS, and its using

Microsoft SQL Server Enterprise: Core-based Licensing (64-bit)

The reason I mention this is because some online solutions were mentioning server and instance, and that if I have the SQL Server Express, I have to list Express as part of the hostname or something. But I have enterprise edition instead...so I'm guessing its the default MSSQLServer which does not have to be specified as part of the dbhostname

Answer 1

The Net.Sockets.TcpClient.Connect method returns void so the PowerShell if statement will never evaluate to $true. Check the Net.Sockets.TcpClient.Connected property after connecting instead:

$port   = 1433

$tcp = New-Object Net.Sockets.TcpClient
$tcp.Connect($dbhost, $port)
if ($tcp.Connected) {
  'connected'
} else {
  'not connected'
}
$tcp.Dispose()

Note that the connection attempt will raise an exception if it fails so the if is superfluous. You could instead use try/catch:

$port   = 1433

$tcp = New-Object Net.Sockets.TcpClient
try {
    $tcp.Connect($dbhost, $port)
    $tcp.Dispose()
    'connected'
} catch [System.Net.Sockets.SocketException] {
    $_.Exception.ToString()
    'not connected'
}