SQL Server

Get TLS Server's SPKI Fingerprint

See more Socket/SSL/TLS Examples

Gets the SPKI fingerprint of a TLS server.

What is Pinning?

Pinning is the process of associating a host with their expected X509 certificate or public key. Once a certificate or public key is known or seen for a host, the certificate or public key is associated or 'pinned' to the host. If more than one certificate or public key is acceptable, then the program holds a pinset. In this case, the advertised identity must match one of the elements in the pinset. A host or service's certificate or public key can be added to an application at development time, or it can be added upon first encountering the certificate or public key. The former - adding at development time - is preferred since preloading the certificate or public key out of band usually means the attacker cannot taint the pin. ..

TLS public key pinning is implemented by (1) making it possible to easily get the SPKI fingerprint of a certificate, and (2) adding the TlsPinSet property to classes that can establish TLS connections.

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 @tlsSock int
    EXEC @hr = sp_OACreate 'Chilkat.Socket', @tlsSock OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Establish a TLS connection with any remote TLS server.
    -- The type of server could be anything: HTTP, IMAP, SMTP, FTP, POP3, etc.
    -- as long as it is TLS (i.e. it connects implicitly using TLS).
    DECLARE @bConnectUsingTls int
    SELECT @bConnectUsingTls = 1
    DECLARE @maxWaitMs int
    SELECT @maxWaitMs = 5000
    EXEC sp_OAMethod @tlsSock, 'Connect', @success OUT, 'imap.gmail.com', 993, @bConnectUsingTls, @maxWaitMs
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @tlsSock, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @tlsSock
        RETURN
      END

    -- Get the server certificate.
    DECLARE @cert int
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

    EXEC sp_OAMethod @tlsSock, 'GetServerCert', @success OUT, @cert
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @tlsSock, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @tlsSock
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    -- The GetSpkiFingerprint method returns the SPKI Fingerprint suitable for use in pinning.
    --  (See RFC 7469.)  An SPKI Fingerprint is defined as the output of a known cryptographic hash
    --  algorithm whose input is the DER-encoded ASN.1 representation of  the Subject Public Key Info
    -- (SPKI) of an X.509 certificate.  The first argument specifies the hash algorithm and may be
    -- "sha256", "sha384", "sha512", "sha1", "md2", "md5", "haval", "ripemd128", 
    -- "ripemd160","ripemd256", or "ripemd320".   
    -- The second argument specifies the encoding, and may be "base64", "hex", 
    -- or any of the encoding modes specified at http://www.cknotes.com/chilkat-binary-encoding-list/
    -- DN = "Distinguished Name"

    EXEC sp_OAMethod @cert, 'GetSpkiFingerprint', @sTmp0 OUT, 'sha256', 'base64'
    PRINT 'SPKI Fingerprint: ' + @sTmp0

    EXEC @hr = sp_OADestroy @tlsSock
    EXEC @hr = sp_OADestroy @cert


END
GO