SQL Server
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 ModelsOAuth2 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 SQL Server Downloads
-- 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)
DECLARE @success int
SELECT @success = 0
-- 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
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonRsaKey OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
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
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
EXEC @hr = sp_OACreate 'Chilkat.Jwt', @jwt OUT
-- Construct the JOSE header...
DECLARE @jose int
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
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
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
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
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