SQL Server

Socket Convert Existing TCP Connection to SSL/TLS

Demonstrates the Chilkat Socket ConvertToSsl method. SQL Server

-- 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)
    DECLARE @success int
    SELECT @success = 0

    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @sock int
    EXEC @hr = sp_OACreate 'Chilkat.Socket', @sock OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- --------------------------------------------------------------------
    -- This example uses the public TCP/TLS echo service at https://tcpbin.com/
    -- --------------------------------------------------------------------

    -- The echo server at tcpbin.com expects to receive TCP connections on port 4242, and TLS connections on port 4243.
    -- We could simply connect to port 4243 using TLS like this.
    -- However, further below, this example shows how to initially make a TCP connection, and then convert the connection to TLS.
    DECLARE @useTls int
    SELECT @useTls = 1
    DECLARE @port int
    SELECT @port = 4243
    DECLARE @maxWaitMs int
    SELECT @maxWaitMs = 5000
    EXEC sp_OAMethod @sock, 'Connect', @success OUT, 'tcpbin.com', @port, @useTls, @maxWaitMs
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @sock, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sock
        RETURN
      END

    -- OK, we have a TLS connection.
    -- Note: The server chooses the TLS protocol version. 
    -- We can see the version of the TLS connection that was established:

    EXEC sp_OAGetProperty @sock, 'TlsVersion', @sTmp0 OUT
    PRINT 'TLS version: ' + @sTmp0

    EXEC sp_OAMethod @sock, 'Close', @success OUT, @maxWaitMs

    -- ---------------------------------------------------------------------
    -- Now we'll do the same, except first we connect to port 4243 using TCP,
    -- and then convert the connection to TLS.
    -- 
    -- This may seem pointless, and it is for the case w/ this server.  However, there could be situations
    -- where you're interacting with a server of some kind where there is a need to convert to/from TLS.
    -- (This is common internal to many protocols, such as SMTP, IMAP, FTP, etc., and it is often called "explicit" SSL/TLS)

    -- Connect again, except this time useTls is 0, so we only have a simple TCP connection after this call.
    SELECT @useTls = 0
    EXEC sp_OAMethod @sock, 'Connect', @success OUT, 'tcpbin.com', @port, @useTls, @maxWaitMs
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @sock, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sock
        RETURN
      END

    -- Convert the existing TCP connection to TLS.
    -- (The method name uses "SSL" because long ago, at the time of initial development, TLS 1.0 did not yet exist.)
    EXEC sp_OAMethod @sock, 'ConvertToSsl', @success OUT
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @sock, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sock
        RETURN
      END

    -- OK, we now have a TLS connection.
    -- Again, we can see the TLS version:

    EXEC sp_OAGetProperty @sock, 'TlsVersion', @sTmp0 OUT
    PRINT 'TLS version: ' + @sTmp0

    EXEC sp_OAMethod @sock, 'Close', @success OUT, @maxWaitMs

    EXEC @hr = sp_OADestroy @sock


END
GO