SQL Server

Get ETK Public Key (api-acpt.ehealth.fgov.be)

See more Belgian eHealth Platform Examples

The following URL returns JSON, which contains a PKCS7 signed data:

https://api-acpt.ehealth.fgov.be/etee/v1/etks?identifier=12345678901&type=SSIN

This example extracts the signed data, validates it, and then extracts the public key from the certificate (obtained from signed content in the PKCS7)

Note: The URL above uses "12345678901" which is not valid. You should replace it with a valid number.

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

    DECLARE @jsonStr nvarchar(4000)
    EXEC sp_OAMethod @http, 'QuickGetStr', @jsonStr OUT, 'https://api-acpt.ehealth.fgov.be/etee/v1/etks?identifier=12345678901&type=SSIN'
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        RETURN
      END


    PRINT @jsonStr

    -- The JSON contains something like this:

    -- [
    --     {
    --         "key": {
    --             "applicationIdentifier": "",
    --             "ssin": "12345678901"
    --         },
    --         "value": "MIAGCSq....AAAAAAAA=="
    --     }
    -- ]

    -- Note: The above is a JSON array (not a JSON object)
    -- It should be loaded into a Chilkat JSON array.
    DECLARE @jarr int
    EXEC @hr = sp_OACreate 'Chilkat.JsonArray', @jarr OUT

    EXEC sp_OAMethod @jarr, 'Load', @success OUT, @jsonStr
    IF @success = 0
      BEGIN

        PRINT 'Failed to load JSON.'
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @jarr
        RETURN
      END

    DECLARE @json int
    EXEC sp_OAMethod @jarr, 'ObjectAt', @json OUT, 0
    DECLARE @bdPkcs7 int
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdPkcs7 OUT

    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'value'
    EXEC sp_OAMethod @bdPkcs7, 'AppendEncoded', @success OUT, @sTmp0, 'base64'
    EXEC @hr = sp_OADestroy @json

    -- Let's verify the PKCS7, and then examine the signing cert,
    -- and get the signing cert's public key.
    DECLARE @crypt int
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT

    -- Validate the signedData PKCS7, and replace the contents of bdPkcs7 with the extracted signed content.
    EXEC sp_OAMethod @crypt, 'OpaqueVerifyBd', @success OUT, @bdPkcs7
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @jarr
        EXEC @hr = sp_OADestroy @bdPkcs7
        EXEC @hr = sp_OADestroy @crypt
        RETURN
      END

    -- The signed content is the DER of a certificate.
    -- In other words, bdPkcs7 now contains a certificate.
    DECLARE @cert int
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

    EXEC sp_OAMethod @cert, 'LoadFromBd', @success OUT, @bdPkcs7
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @jarr
        EXEC @hr = sp_OADestroy @bdPkcs7
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    -- Show some certificate information:

    EXEC sp_OAGetProperty @cert, 'SubjectDN', @sTmp0 OUT
    PRINT 'Subject: ' + @sTmp0

    EXEC sp_OAGetProperty @cert, 'SerialNumber', @sTmp0 OUT
    PRINT 'Serial: ' + @sTmp0

    EXEC sp_OAGetProperty @cert, 'IssuerDN', @sTmp0 OUT
    PRINT 'Issuer: ' + @sTmp0

    -- Let's get the cert's public key...
    DECLARE @pubKey int
    EXEC @hr = sp_OACreate 'Chilkat.PublicKey', @pubKey OUT

    EXEC sp_OAMethod @cert, 'GetPublicKey', @success OUT, @pubKey

    -- OK, you now have the public key and can do whatever is needed..
    EXEC sp_OAGetProperty @pubKey, 'KeyType', @sTmp0 OUT
    PRINT @sTmp0
    EXEC sp_OAGetProperty @pubKey, 'KeySize', @iTmp0 OUT
    PRINT @iTmp0

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @jarr
    EXEC @hr = sp_OADestroy @bdPkcs7
    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @cert
    EXEC @hr = sp_OADestroy @pubKey


END
GO