(SQL Server) Verify a Google JWT Using Google's Public Key

See more Google APIs Examples

Demonstrates how to verify a JWT that was signed using Google's RSA private key.

This example verifies the RSA signature. It also does the following:

• Checks to see if the time constraints ("nbf" and "exp") are valid.
• Recovers the original JOSE header.
• Recovers the original claims JSON.

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 assumes 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 @sbPubKeys int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbPubKeys OUT

    DECLARE @success int
    EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, 'https://www.googleapis.com/oauth2/v3/certs', @sbPubKeys
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbPubKeys
        RETURN
      END

    EXEC sp_OAMethod @sbPubKeys, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    -- Here are the keys:

    -- {
    --   "keys": [
    --     {
    --       "e": "AQAB",
    --       "n": "4bAT6C6EeX8Dspje3FrAXw-nnhNk04e1RmNa4kjc0CHf6Pk7ryARlwA-6YilyPABqQfYHx60s8oSnxvUVprFfQ2-Q8aAZO7bPKSxnoGlcKERL2oLNA4Msvc89N9Y5ycThZUplf_QC19e6jyYXN6Nz-UnJSCLrtQY8tVhhVRs61j4A2N_p-enAi-r704Qi1-v-DKV4eVRkClKViploo8NyjUaT9L4vbBssPCjyimJzsWnEe1fED5c4LnHeArYzA_FEn3JJotqDIz9t2VnvZNTMhizHEX4VnORlEWMEfR8n4CEHQx7PcQUOmfqyw08gWeXQl1-uTjtIGaE-sRIv9u_vQ",
    --       "kty": "RSA",
    --       "use": "sig",
    --       "alg": "RS256",
    --       "kid": "2af90e87be140c20038898a6efa11283dab6031d"
    --     },
    --     {
    --       "n": "nzGsrziOYrMVYMpvUZOwkKNiPWcOPTYRYlDSdRW4UpAHdWPbPlyqaaphYhoMB5DXrVxI3bdvm7DOlo-sHNnulmAFQa-7TsQMxrZCvVdAbyXGID9DZYEqf8mkCV1Ohv7WY5lDUqlybIk1OSHdK7-1et0QS8nn-5LojGg8FK4ssLf3mV1APpujl27D1bDhyRb1MGumXYElwlUms7F9p9OcSp5pTevXCLmXs9MJJk4o9E1zzPpQ9Ko0lH9l_UqFpA7vwQhnw0nbh73rXOX2TUDCUqL4ThKU5Z9Pd-eZCEOatKe0mJTpQ00XGACBME_6ojCdfNIJr84Y_IpGKvkAEksn9w",
    --       "use": "sig",
    --       "kid": "87bbe0815b064e6d449cac999f0e50e72a3e4374",
    --       "e": "AQAB",
    --       "alg": "RS256",
    --       "kty": "RSA"
    --     }
    --   ]
    -- }

    -- -------------------------------------------------------------------------------------------
    -- Replace this with your actual token.
    -- This sample token contains a kid that does not match any of the above Google public keys.
    -- -------------------------------------------------------------------------------------------
    DECLARE @token nvarchar(4000)
    SELECT @token = 'eyJhbGciOiJSUzI1NiIsImtpZCI6IjQyZmY5MGQ3ZDM0OGM5NzM4MWE3YzExOWVmMWY1MzI0ZWEzZjViZWIifQ.eyJpc3MiOiJodHRwczovL2FjY291bnRzLmdvb2dsZS5jb20iLCJzdWIiOiIxMTExMTExMTExMTExMTExMTExMTExMTExMTExMTExMTExMTExMTExMTExIiwiaWF0IjoxNjExMTE1MTQzLCJleHAiOjE2MTEyMDE1NDMsImF1ZCI6IjQyOTc1NzExNTE0ODg0OSJ9.pLem5i0bx3M7lJYj7jKv2Nq7c07X5YpZz-x1uM5RniW-v4LsX-lKIVvOq2x3-WoPqkzLXJfP0kG0dx1uD2q1NfFQK60YwKH4FnFtB6INnUP1dRVpP9_pTTKyAE28I3s5Tay4PbPdrCl7ZLCIJzCfpCW1TiWeVoPjp5HgZKTBHdP_sEkN_yO5dQerQXAkFJkV3kNgF9jI3ayT-KPqOIH6GVoWXjHFDyA2EYgJPEFRo5WSe6XycJ85p5duwT-OoBcb_kJZG9PxYd91eHlPCzp8vGxzIb2dVROCBxyM8e8W0cd9v15hfmpg9R-eG9vCM5y63ZLChZLFeHFx0Pd7hvAqfKg'

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

    DECLARE @header nvarchar(4000)
    EXEC sp_OAMethod @jwt, 'GetHeader', @header OUT, @token


    PRINT @header

    -- Sample header:
    -- {"alg":"RS256","kid":"87bbe0815b064e6d449cac999f0e50e72a3e4374"}

    -- Load the public key matching the "kid" into a Chilkat public key object, then verify..

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

    EXEC sp_OAMethod @json, 'Load', @success OUT, @header
    DECLARE @kid nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @kid OUT, 'kid'


    PRINT 'kid = ' + @kid

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

    EXEC sp_OAMethod @jsonPubKeys, 'LoadSb', @success OUT, @sbPubKeys

    DECLARE @jsonKey int
    EXEC sp_OAMethod @jsonPubKeys, 'FindRecord', @jsonKey OUT, 'keys', 'kid', @kid, 1
    EXEC sp_OAGetProperty @jsonPubKeys, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN

        PRINT 'Did not find a matching public key based on the kid.'
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbPubKeys
        EXEC @hr = sp_OADestroy @jwt
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @jsonPubKeys
        RETURN
      END

    EXEC sp_OAMethod @jsonKey, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- Load the matching public key into a Chilkat public key object.
    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
    EXEC @hr = sp_OADestroy @jsonKey

    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pubKey, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbPubKeys
        EXEC @hr = sp_OADestroy @jwt
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @jsonPubKeys
        EXEC @hr = sp_OADestroy @pubKey
        RETURN
      END

    -- ----------------------------------------------------------------------------------------
    -- Now we can validate the JWT using Google's public key as shown in this example:
    -- (Except we use the public key obtained as shown above instead of a public key loaded from a PEM file.
    -- 
    -- See Verify JWT Using an RSA Public Key

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @sbPubKeys
    EXEC @hr = sp_OADestroy @jwt
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @jsonPubKeys
    EXEC @hr = sp_OADestroy @pubKey


END
GO