(SQL Server) Box.com OAuth2 with JSON Web Tokens

See more Box Examples

Demonstrates how to obtain an OAuth2 access token using a JSON Web Token. The following explanation is copied from Box Authentication Models

OAuth2 with JSON Web Tokens enables an application to connect directly to Box and obtain authorization to access files and folders without requiring users to log in. Using OAuth2 with JSON Web Tokens an application can provide Box features without users even being aware that Box exists.

Instead of requiring the user to log in to Box, the application generates JSON Web Token (JWT) verified by an RSA keypair. If this authentication succeeds then the application obtains an access token that grants authorization to operate on Box files and folders. This machine-to-machine authentication replaces the first leg of the three-legged authentication process defined by OAuth2, and enables users of your application to work with Box content without seeing Box login requests.

OAuth2 with JSON Web Tokens is designed to be used with Box Platform. You can use OAuth2 with JWT with both Service Accounts and App Users.

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)
    DECLARE @sTmp1 nvarchar(4000)
    DECLARE @sTmp2 nvarchar(4000)
    -- This requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- When you created an RSA key pair using the Box web user interface,
    -- you downloaded a json file named something like "7152782_kkdxptq2_config.json"
    -- This contains the following:

    -- {
    --   "boxAppSettings": {
    --     "clientID": "0kraci84o0jfr7yuw596tf394iigzbe7",
    --     "clientSecret": "xxxxxxxxxxxxxxxxxxxxxxxxx",
    --     "appAuth": {
    --       "publicKeyID": "kkdxptq2",
    --       "privateKey": "-----BEGIN ENCRYPTED PRIVATE KEY-----\nMIIFDj ... nceU=\n-----END ENCRYPTED PRIVATE KEY-----\n",
    --       "passphrase": "xxxxxxxxxxxxxxxxxxxxxxxx"
    --     }
    --   },
    --   "enterpriseID": "7152782"
    -- }
    -- 

    -- Load it into a Chilkat JSON object to allow access to the content.
    DECLARE @jsonRsaKey int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonRsaKey OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @jsonRsaKey, 'LoadFile', @success OUT, 'qa_data/tokens/7152782_kkdxptq2_config.json'

    -- Load the private key into a Chilkat private key object.
    DECLARE @passphrase nvarchar(4000)
    EXEC sp_OAMethod @jsonRsaKey, 'StringOf', @passphrase OUT, 'boxAppSettings.appAuth.passphrase'
    DECLARE @privateKeyPem nvarchar(4000)
    EXEC sp_OAMethod @jsonRsaKey, 'StringOf', @privateKeyPem OUT, 'boxAppSettings.appAuth.privateKey'

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

    EXEC sp_OAMethod @rsaKey, 'LoadEncryptedPem', @success OUT, @privateKeyPem, @passphrase
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rsaKey, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @jsonRsaKey
        EXEC @hr = sp_OADestroy @rsaKey
        RETURN
      END

    -- The JSON Web Token will be created using the JWT class
    DECLARE @jwt int
    -- Use "Chilkat_9_5_0.Jwt" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Jwt', @jwt OUT

    -- Construct the JOSE header...
    DECLARE @jose int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jose OUT

    -- Chilkat supports the following algorithms: "RS256", "RS384", and "RS512".  (Chilkat also supports other algorithms that Box does not yet support.)
    EXEC sp_OAMethod @jose, 'UpdateString', @success OUT, 'alg', 'RS256'
    EXEC sp_OAMethod @jose, 'UpdateString', @success OUT, 'typ', 'JWT'
    EXEC sp_OAMethod @jsonRsaKey, 'StringOf', @sTmp0 OUT, 'boxAppSettings.appAuth.publicKeyID'
    EXEC sp_OAMethod @jose, 'UpdateString', @success OUT, 'kid', @sTmp0

    -- Now let's build the JWT claims. Most of this is just boilerplate (i.e. the same every time..)
    -- The JWT claims contain these required and optional elements:

    -- iss (required, String)  The Client ID of the service that created the JWT assertion.
    -- sub (required, String)  enterprise_id for a token specific to an enterprise when creating and managing app users, or the app user_id for a token specific to an individual app user.
    -- box_sub_type (required, String)  "enterprise" or "user" depending on the type of token being requested in the sub claim.
    -- aud (required, String) Always "https://api.box.com/oauth2/token" for OAuth2 token requests
    -- jti (required, String) A universally unique identifier specified by the client for this JWT. This is a unique string that is at least 16 characters and at most 128 characters.
    -- exp (required, NumericDate) The unix time as to when this JWT will expire. This can be set to a maximum value of 60 seconds beyond the issue time. Note: It is recommended to set this value to less than the maximum allowed 60 seconds.
    -- iat (optional, NumericDate) Issued at time. The token cannot be used before this time.
    -- nbf (optional, NumericDate) Not before. Specifies when the token will start being valid.
    -- 

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

    EXEC sp_OAMethod @jsonRsaKey, 'StringOf', @sTmp0 OUT, 'boxAppSettings.clientID'
    EXEC sp_OAMethod @claims, 'UpdateString', @success OUT, 'iss', @sTmp0
    EXEC sp_OAMethod @jsonRsaKey, 'StringOf', @sTmp0 OUT, 'enterpriseID'
    EXEC sp_OAMethod @claims, 'UpdateString', @success OUT, 'sub', @sTmp0
    EXEC sp_OAMethod @claims, 'UpdateString', @success OUT, 'box_sub_type', 'enterprise'
    EXEC sp_OAMethod @claims, 'UpdateString', @success OUT, 'aud', 'https://api.box.com/oauth2/token'

    -- Generate 32 random bytes (base64 encoded) for the "jti"
    DECLARE @prng int
    -- Use "Chilkat_9_5_0.Prng" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Prng', @prng OUT

    EXEC sp_OAMethod @prng, 'GenRandom', @sTmp0 OUT, 32, 'base64'
    EXEC sp_OAMethod @claims, 'UpdateString', @success OUT, 'jti', @sTmp0

    -- Set the expiration time to 60 seconds after the current time.
    EXEC sp_OAMethod @jwt, 'GenNumericDate', @iTmp0 OUT, 60
    EXEC sp_OAMethod @claims, 'UpdateInt', @success OUT, 'exp', @iTmp0

    -- We're going to do the following POST to get a JSON response that contains our OAuth2 access token:

    -- 	POST /oauth2/token
    -- 	Content-Type: application/x-www-form-urlencoded
    -- 	grant_type=urn:ietf:params:oauth:grant-type:jwt-bearer&
    -- 	assertion=<JWT>&
    -- 	client_id=<client_id>&
    -- 	client_secret=<client_secret>

    -- First, make the initial connection.
    -- A single REST object, once connected, can be used for many Box REST API calls.
    -- The auto-reconnect indicates that if the already-established HTTPS connection is closed,
    -- then it will be automatically re-established as needed.
    DECLARE @rest int
    -- Use "Chilkat_9_5_0.Rest" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Rest', @rest OUT

    DECLARE @bAutoReconnect int
    SELECT @bAutoReconnect = 1
    EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'api.box.com', 443, 1, @bAutoReconnect
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @jsonRsaKey
        EXEC @hr = sp_OADestroy @rsaKey
        EXEC @hr = sp_OADestroy @jwt
        EXEC @hr = sp_OADestroy @jose
        EXEC @hr = sp_OADestroy @claims
        EXEC @hr = sp_OADestroy @prng
        EXEC @hr = sp_OADestroy @rest
        RETURN
      END

    -- Add the query params.
    -- Calling ClearAllParts is wise if previous requests were sent prior to this one on the same REST object..
    EXEC sp_OAMethod @rest, 'ClearAllParts', @success OUT
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'grant_type', 'urn:ietf:params:oauth:grant-type:jwt-bearer'
    EXEC sp_OAMethod @jsonRsaKey, 'StringOf', @sTmp0 OUT, 'boxAppSettings.clientID'
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'client_id', @sTmp0
    EXEC sp_OAMethod @jsonRsaKey, 'StringOf', @sTmp0 OUT, 'boxAppSettings.clientSecret'
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'client_secret', @sTmp0
    EXEC sp_OAMethod @jose, 'Emit', @sTmp1 OUT
    EXEC sp_OAMethod @claims, 'Emit', @sTmp2 OUT
    EXEC sp_OAMethod @jwt, 'CreateJwtPk', @sTmp0 OUT, @sTmp1, @sTmp2, @rsaKey
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'assertion', @sTmp0

    DECLARE @jsonResponse nvarchar(4000)
    EXEC sp_OAMethod @rest, 'FullRequestFormUrlEncoded', @jsonResponse OUT, 'POST', '/oauth2/token'
    EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @jsonRsaKey
        EXEC @hr = sp_OADestroy @rsaKey
        EXEC @hr = sp_OADestroy @jwt
        EXEC @hr = sp_OADestroy @jose
        EXEC @hr = sp_OADestroy @claims
        EXEC @hr = sp_OADestroy @prng
        EXEC @hr = sp_OADestroy @rest
        RETURN
      END

    -- If successful, we'll get a response status code equal to 200,
    -- and a JSON response that looks like this:

    -- 	{
    -- 	   "access_token": "mNr1FrCvOeWiGnwLL0OcTL0Lux5jbyBa",
    -- 	   "expires_in": 4169,
    -- 	   "restricted_to": [],
    -- 	   "token_type": "bearer"
    -- 	}
    -- 

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

    EXEC sp_OASetProperty @jResponse, 'EmitCompact', 0
    EXEC sp_OAMethod @jResponse, 'Load', @success OUT, @jsonResponse

    EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
    IF @iTmp0 <> 200
      BEGIN
        EXEC sp_OAMethod @jResponse, 'Emit', @sTmp0 OUT
        PRINT @sTmp0

        PRINT 'Failed.'
        EXEC @hr = sp_OADestroy @jsonRsaKey
        EXEC @hr = sp_OADestroy @rsaKey
        EXEC @hr = sp_OADestroy @jwt
        EXEC @hr = sp_OADestroy @jose
        EXEC @hr = sp_OADestroy @claims
        EXEC @hr = sp_OADestroy @prng
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @jResponse
        RETURN
      END

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

    -- Get the access token:
    DECLARE @accessToken nvarchar(4000)
    EXEC sp_OAMethod @jResponse, 'StringOf', @accessToken OUT, 'access_token'

    PRINT 'Access token, valid for 60 minutes: ' + @accessToken

    EXEC @hr = sp_OADestroy @jsonRsaKey
    EXEC @hr = sp_OADestroy @rsaKey
    EXEC @hr = sp_OADestroy @jwt
    EXEC @hr = sp_OADestroy @jose
    EXEC @hr = sp_OADestroy @claims
    EXEC @hr = sp_OADestroy @prng
    EXEC @hr = sp_OADestroy @rest
    EXEC @hr = sp_OADestroy @jResponse


END
GO