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

Using raw TCP for Sql Server connection makes little sense. Should it connect, all you can tell is something's accepting connections. Unless you plan to introduce the TDS protocol Sql Server uses, maybe better a way would be to use `Invoke-SqlCmd` to run `select @@version;` or the like? — vonPryz, Sep 07 '18 at 05:12
@vonPryz um, I dont really know those advanced concepts like TDS, I'm basically just stating to dive into sql in powershell, so I was following another thread on what I should do. The guy had a function for testing the port then testing the connection, so I'm just following along. What I understand is if this function returns connection successful on 1433, then I should be able to resolve the current issue I have with test sql connection function cause it too is also returning not connected status, so I thought its because of port 1433 issue — Cataster, Sep 07 '18 at 05:58

Kirill Pashkov · Answer 1 · 2018-09-07T06:40:42.560

You probably did not connect in time and yet trying to check if state is currently connected. Try to use BeginConnect method for Net.Sockets.TcpClient class it has timeout option that might help you. I have fixed your code:

$port   = 1433
$timeout = 1000 #ms

$tcp = New-Object Net.Sockets.TcpClient
$wait = $tcp.BeginConnect($dbhost,$port,$null,$null)
[void]$wait.AsyncWaitHandle.WaitOne($timeout,$false)
if ($tcp.Connected) {
  'connected'
} else {
  'not connected'
}
$tcp.Close()
$tcp.Dispose()

score 0 · Accepted Answer · answered Sep 07 '18 at 10:50

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'
}

dude, you just gave me the greatest hint ever! so apparently when i pass the $dbhost, it was null the whole time! i didnt think powershell cared about order, so i had the dbhost being set to a file being read with values all the way later down in the script, so of course dbhost was not getting ANY value because i had to set the variables BEFORE it! THANK YOU! — Cataster, Sep 07 '18 at 13:32

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

2 Answers2