(SQL Server) Socket Connect Failure Cases

Demonstrates a few common situations that would cause a TCP connect to a remote host:port to fail, an examines the LastErrorText for each case.

This example discusses the following cases:

1. The remote host is not listening on the desired port.
2. No remote host exists at the IP address (such as on a LAN).
3. The local Windows Firewall blocks the outbound connection.
4. Your ISP blocks the outbound connection.
5. The remote Windows Firewall blocks the inbound connection.
6. The domain cannot be resolved to an IP address.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @socket int
    -- Use "Chilkat_9_5_0.Socket" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Socket', @socket OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @tls int
    SELECT @tls = 0
    DECLARE @maxWaitMillisec int
    SELECT @maxWaitMillisec = 5000

    -- First test the case where the remote host is not listening on the desired port.
    -- In this case, my LAN has a computer at 192.168.1.137, but nothing on that computer
    -- is listening for inbound connection to port 6660.
    DECLARE @success int
    EXEC sp_OAMethod @socket, 'Connect', @success OUT, '192.168.1.137', 6660, @tls, @maxWaitMillisec
    EXEC sp_OAGetProperty @socket, 'LastErrorText', @sTmp0 OUT
    PRINT @sTmp0

    -- The call to Connect should fail quickly, and the LastErrorText will contain
    -- a message about the connection being rejected:
    -- 
    --      socket2Connect:
    --        connect2:
    --          ConnectFailReason: Connection rejected
    --        --connect2
    --      --socket2Connect

    -- Now test the case where there is no computer on the network at the desired IP address.
    -- For example, on my LAN, there is no computer at 192.168.1.188.  If ping is used to test,
    -- the error message would be "Destination host unreachable".

    EXEC sp_OAMethod @socket, 'Connect', @success OUT, '192.168.1.188', 80, @tls, @maxWaitMillisec
    EXEC sp_OAGetProperty @socket, 'LastErrorText', @sTmp0 OUT
    PRINT @sTmp0

    -- In this case, the connection is "rejected" as before, but the connect hangs for the entire
    -- maximum waiting period (maxWaitMillisec), which is 5 seconds in this case.
    -- (If the maxWaitMillisec is a large enough amount of time, the operating system
    -- may fail the connect with a "connection rejected" after a long wait. 
    -- The difference is that when the remote host exists and is reachable, the failure is quick.)
    -- The portion of the LastErrorText for this failure is shown here:

    --    connectInner:
    --      hostname: 192.168.1.188
    --      port: 80
    --      tls: 0
    --      maxWaitMs: 5000
    --      socket2Connect:
    --        connect2:
    --          connectSocket:
    --            connect_ipv6_or_ipv4:
    --              timeout waiting for connect to complete
    --              numSec: 5
    --              numMicroSec: 0
    --              failedWaitToConnect: Socket operation timeout.
    --            --connect_ipv6_or_ipv4
    --          --connectSocket
    --          ConnectFailReason: Timeout
    --        --connect2
    --      --socket2Connect
    --      Failed.
    --    --connectInner

    -- Imagine that a new rule is added to Windows Firewall to block all connections
    -- for this example app.  The characteristics of the failure are the same as for
    -- the case where nothing on the remote computer is listening at the given port.
    -- The connection attempt fails immediately, and the error message (from the OS) is
    -- "connection rejected".  Also notice that SSL/TLS never enters the equation because
    -- the SSL/TLS handshake begins *after* the TCP connection is completed.  When the 
    -- TCP connection fails, it never reaches the point of even beginning the SSL/TLS negotiation.
    SELECT @tls = 1
    EXEC sp_OAMethod @socket, 'Connect', @success OUT, '192.168.1.188', 80, @tls, @maxWaitMillisec
    EXEC sp_OAGetProperty @socket, 'LastErrorText', @sTmp0 OUT
    PRINT @sTmp0

    --    connectInner:
    --      hostname: smtp.gmail.com
    --      port: 465
    --      tls: 1
    --      maxWaitMs: 5000
    --      socket2Connect:
    --        connect2:
    --          ConnectFailReason: Connection rejected
    --        --connect2
    --      --socket2Connect
    --      Failed.
    --    --connectInner

    -- What about the case where the ISP blocks an outbound connection?  My home ISP is 
    -- Comcast cable, and they block outbound SMTP port 25.  
    SELECT @tls = 0
    EXEC sp_OAMethod @socket, 'Connect', @success OUT, 'pop3.btconnect.com', 25, @tls, @maxWaitMillisec
    EXEC sp_OAGetProperty @socket, 'LastErrorText', @sTmp0 OUT
    PRINT @sTmp0

    -- The result is a timeout:
    --    connectInner:
    --      hostname: pop3.btconnect.com
    --      port: 25
    --      tls: 0
    --      maxWaitMs: 5000
    --      socket2Connect:
    --        connect2:
    --          connectSocket:
    --            connect_ipv6_or_ipv4:
    --              timeout waiting for connect to complete
    --              numSec: 5
    --              numMicroSec: 0
    --              failedWaitToConnect: Socket operation timeout.
    --            --connect_ipv6_or_ipv4
    --          --connectSocket
    --          ConnectFailReason: Timeout
    --        --connect2
    --      --socket2Connect
    --      Failed.
    --    --connectInner

    -- What about the case where the Windows Firewall at the remote host is blocking the
    -- inbound connection.  On Chilkat's internal LAN, there is a computer at 192.168.1.127
    -- running an SSH server.  After adding an inbound rule to block port 22, external connections
    -- fail with a timeout, as shown below:

    EXEC sp_OAMethod @socket, 'Connect', @success OUT, '192.168.1.127', 22, @tls, @maxWaitMillisec
    EXEC sp_OAGetProperty @socket, 'LastErrorText', @sTmp0 OUT
    PRINT @sTmp0

    --    connectInner:
    --      hostname: 192.168.1.127
    --      port: 22
    --      tls: 0
    --      maxWaitMs: 5000
    --      socket2Connect:
    --        connect2:
    --          connectSocket:
    --            connect_ipv6_or_ipv4:
    --              timeout waiting for connect to complete
    --              numSec: 5
    --              numMicroSec: 0
    --              failedWaitToConnect: Socket operation timeout.
    --            --connect_ipv6_or_ipv4
    --          --connectSocket
    --          ConnectFailReason: Timeout
    --        --connect2
    --      --socket2Connect
    --      Failed.
    --    --connectInner

    -- Finally, what if we try to connect to a hostname that does not resolve to an IP address?

    EXEC sp_OAMethod @socket, 'Connect', @success OUT, 'www.thisdoesnotresolvetoanyipaddressxyz.com', 22, @tls, @maxWaitMillisec
    EXEC sp_OAGetProperty @socket, 'LastErrorText', @sTmp0 OUT
    PRINT @sTmp0

    -- In this case there is an immediate failure:
    --    connectInner:
    --      hostname: www.thisdoesnotresolvetoanyipaddressxyz.com
    --      port: 22
    --      tls: 0
    --      maxWaitMs: 5000
    --      socket2Connect:
    --        connect2:
    --          connectSocket:
    --            connect_ipv6_or_ipv4:
    --              resolveHostname6:
    --                getAddressInfo:
    --                  Failed to get host address info. (3)
    --                  SocketError: WSAHOST_NOT_FOUND No such host is known.
    --                  hostOrIpAddr: www.thisdoesnotresolvetoanyipaddressxyz.com
    --                  port: 22
    --                  Versions of Windows earlier than Windows XP are limited to handling IPv4 only
    --                  On Windows Server 2003 and Windows XP, IPv6 addresses are returned only if IPv6 is installed on the local computer.
    --                --getAddressInfo
    --              --resolveHostname6
    --              Domain to IP address resolution failed.
    --            --connect_ipv6_or_ipv4
    --          --connectSocket
    --          ConnectFailReason: DNS lookup failed
    --        --connect2
    --      --socket2Connect
    --      Failed.
    --    --connectInne

    EXEC @hr = sp_OADestroy @socket


END
GO