SQL Server

HTTPS Server Certificate Require Hostname Match

See more HTTP Examples

Demonstrates and explains the RequireHostnameMatch property. 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
    DECLARE @iTmp0 int
    -- The RequireHostnameMatch property was added in Chilkat v11.0.0
    -- to ensure the URL's hostname matches at least one of the server certificate SAN's (Subject Alternative Names)
    -- 
    -- In actuality, it is the SNI hostname that must match.  If the SNI hostname is not explicitly set,
    -- then Chilkat uses the hostname from the URL as the SNI hostname.

    -- Here's an example using chilkatsoft.com
    -- The SSL server certificate for chilkatsoft.com has 2 Subject Alternative Names:
    -- 
    -- 1) DNS Name: *.chilkatsoft.com
    -- 2) DNS Name: chilkatsoft.com
    -- 
    -- See Explaining the SNI Hostname in TLS

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

    EXEC sp_OASetProperty @http, 'RequireHostnameMatch', 1

    -- This should succeed because "www.chilkatsoft.com" matches the SAN entry "*.chilkatsoft.com"
    DECLARE @html nvarchar(4000)
    EXEC sp_OAMethod @http, 'QuickGetStr', @html OUT, 'https://www.chilkatsoft.com/helloWorld.html'

    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    PRINT '1) Succeeded: ' + @iTmp0

    -- At the time of writing this example, the IP address for chilkatsoft.com is 3.101.18.47
    -- If we send the request using the IP address, it will fail because the IP address is does 
    -- not match any of the SAN entries in the server certificate.
    EXEC sp_OAMethod @http, 'QuickGetStr', @html OUT, 'https://3.101.18.47/helloWorld.html'

    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    PRINT '2) Succeeded: ' + @iTmp0

    -- However, it will succeed if we explicitly set the SNI hostname.
    EXEC sp_OASetProperty @http, 'SniHostname', 'www.chilkatsoft.com'
    EXEC sp_OAMethod @http, 'QuickGetStr', @html OUT, 'https://3.101.18.47/helloWorld.html'

    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    PRINT '3) Succeeded: ' + @iTmp0

    -- Remove our explicit SNI hostname.
    EXEC sp_OASetProperty @http, 'SniHostname', ''

    -- Now let's try wrong.host.badssl.com
    -- The SSL server certificate for badssl.com has 2 Subject Alternative Names:
    -- 
    -- 1) DNS Name: *.badssl.com
    -- 2) DNS Name: badssl.com

    -- The domain wrong.host.badssl.com will fail the RequireHostnameMatch because
    -- the wildcarded domain SAN entry only extends 1 level deep.  
    EXEC sp_OAMethod @http, 'QuickGetStr', @html OUT, 'https://wrong.host.badssl.com/'

    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    PRINT '4) Succeeded: ' + @iTmp0

    -- The expected output is:
    -- 1) Succeeded: True
    -- 2) Succeeded: False
    -- 3) Succeeded: True
    -- 4) Succeeded: False

    EXEC @hr = sp_OADestroy @http


END
GO