(SQL Server) Download Google OAuth2 Certs (JWK) and Load into Chilkat PublicKey Objects

Demonstrates how to download the JWK from https://www.googleapis.com/oauth2/v3/certs and load the public keys into Chilkat public key objects.

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
    DECLARE @iTmp0 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 @http int
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @jwkStr nvarchar(4000)
    EXEC sp_OAMethod @http, 'QuickGetStr', @jwkStr OUT, 'https://www.googleapis.com/oauth2/v3/certs'
    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

    -- We have the following:

    --     {
    --       "keys": [
    -- 	{
    -- 	  "kid": "e8732db06287515556213b80acbcfd08cfb302a9",
    -- 	  "n": "4RIrO30287Wsq3gqXCMkUYMVAeI3H8...w2mbMNEBQ",
    -- 	  "kty": "RSA",
    -- 	  "e": "AQAB",
    -- 	  "alg": "RS256",
    -- 	  "use": "sig"
    -- 	},
    -- 	{
    -- 	  "kid": "8462a71da4f6d611fc0fecf0fc4ba9c37d65e6cd",
    -- 	  "e": "AQAB",
    -- 	  "n": "xT_ngLZNmT5GBtJZeTB...Ft4gK0eoFi0d3l8bcw",
    -- 	  "alg": "RS256",
    -- 	  "use": "sig",
    -- 	  "kty": "RSA"
    -- 	}
    --       ]
    --     }

    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    DECLARE @success int
    EXEC sp_OAMethod @json, 'Load', @success OUT, @jwkStr

    DECLARE @numKeys int
    EXEC sp_OAMethod @json, 'SizeOfArray', @numKeys OUT, 'keys'
    DECLARE @i int
    SELECT @i = 0
    WHILE @i < @numKeys
      BEGIN
        EXEC sp_OASetProperty @json, 'I', @i
        DECLARE @jsonKey int
        EXEC sp_OAMethod @json, 'ObjectOf', @jsonKey OUT, 'keys[i]'
        DECLARE @pubKey int
        -- Use "Chilkat_9_5_0.PublicKey" for versions of Chilkat < 10.0.0
        EXEC @hr = sp_OACreate 'Chilkat.PublicKey', @pubKey OUT

        EXEC sp_OAMethod @jsonKey, 'Emit', @sTmp0 OUT
        EXEC sp_OAMethod @pubKey, 'LoadFromString', @success OUT, @sTmp0
        IF @success = 0
          BEGIN
            EXEC sp_OAGetProperty @pubKey, 'LastErrorText', @sTmp0 OUT
            PRINT @sTmp0
            EXEC @hr = sp_OADestroy @http
            EXEC @hr = sp_OADestroy @json
            EXEC @hr = sp_OADestroy @pubKey
            RETURN
          END

        PRINT @i
        EXEC sp_OAMethod @pubKey, 'GetPem', @sTmp0 OUT, 1
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @jsonKey

        SELECT @i = @i + 1
      END


    PRINT 'Success.'

    -- Sample output:

    -- 0
    -- -----BEGIN RSA PUBLIC KEY-----
    -- MIIBCgKCAQEA4RIrO30287Wsq3gqXCMkUYMVAeI3H8LVE6IXR1krdFeGnZLiGUPw
    -- cbkeVpXf3lmJdsStOg+jijces2DZCfPyIBiQuLYfxxmAZE6ErJ0QJFg1stwli2Pz
    -- 9ncYhFoqi8pXr7kEzEJBTzX4thuw56ydbGsshSEznPXoerCJOc7UI2+n0wFCWQ4Y
    -- LHbh/PrWt4vdadyUUUW/QpQHXQLdD8q/Qwqdj0O9zlJE7R6Elw2E9EqnHyIGu1hm
    -- LxhqrTru1M18SUhONYbVskV/BCEdVKs//X96849HorWQDCAgVMWfGsdMVq55FAdJ
    -- 680N5UmQDRynIZ4+PeNGN4S9iw2mbMNEBQIDAQAB
    -- -----END RSA PUBLIC KEY-----
    -- 
    -- 1
    -- -----BEGIN RSA PUBLIC KEY-----
    -- MIIBCgKCAQEAxT/ngLZNmT5GBdkLtJZjNeTB+8B5yWgrq/e5eMZ1hrZhcmLK+dSn
    -- IkpOPV8/OekV67EnQ7I4II2rcNJnHGrGKZziXO3XN2gtUHE+mBJC99oULSbX/QwB
    -- Kz7gC/IBPq9EuxTt6Oq6fPkVQ9DbRIgWJSEGBF/KRaNl3kyAlIZfpY7XgHyJTTv8
    -- E7yAcYKPR+36gzdl+ps0sDLKzUuAtZNq8llK0u80z6AtAUIYwWdkEhM9upy6keKI
    -- TasIxcsO7M6kZPINUSbh6t5VAm8FuqRmxpgg+9c9/GQSGd89InVypoVzWLQ+wOGg
    -- 5G4H6JqIgtj0TRFt4gK0eoFi2U0d3l8bcwIDAQAB
    -- -----END RSA PUBLIC KEY-----
    -- 
    -- Success.

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @pubKey


END
GO