SQL Server Requires Chilkat v11.5.0+
SQL Server
Box: Get a Service Account Token with JWT
See more Box Examples
Use the encrypted private key in a Box application configuration file to sign a short-lived assertion and exchange it for an access token. Configure and authorize a JWT Server app first. The enterprise subject obtains the service-account token.
See the Box API guide for setup and the complete workflow.
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
DECLARE @sTmp0 nvarchar(4000)
DECLARE @sTmp1 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- Requires Chilkat 11.5.0 or later and a previously unlocked Chilkat bundle.
-- See Global Unlock Sample.
-- Guide: Box API Guide.
SELECT @success = 0
DECLARE @config int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @config OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OAMethod @config, 'LoadFile', @success OUT, 'qa_data/tokens/box_jwt_config.json'
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @config, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @config
RETURN
END
DECLARE @key int
EXEC @hr = sp_OACreate 'Chilkat.PrivateKey', @key OUT
EXEC sp_OAMethod @config, 'StringOf', @sTmp0 OUT, 'boxAppSettings.appAuth.privateKey'
EXEC sp_OAMethod @config, 'StringOf', @sTmp1 OUT, 'boxAppSettings.appAuth.passphrase'
EXEC sp_OAMethod @key, 'LoadEncryptedPem', @success OUT, @sTmp0, @sTmp1
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @key, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @config
EXEC @hr = sp_OADestroy @key
RETURN
END
DECLARE @header int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @header OUT
EXEC sp_OAMethod @header, 'UpdateString', @success OUT, 'alg', 'RS256'
EXEC sp_OAMethod @header, 'UpdateString', @success OUT, 'typ', 'JWT'
EXEC sp_OAMethod @config, 'StringOf', @sTmp0 OUT, 'boxAppSettings.appAuth.publicKeyID'
EXEC sp_OAMethod @header, 'UpdateString', @success OUT, 'kid', @sTmp0
DECLARE @jwt int
EXEC @hr = sp_OACreate 'Chilkat.Jwt', @jwt OUT
DECLARE @prng int
EXEC @hr = sp_OACreate 'Chilkat.Prng', @prng OUT
DECLARE @nonce nvarchar(4000)
EXEC sp_OAMethod @prng, 'GenRandom', @nonce OUT, 32, 'hex'
EXEC sp_OAGetProperty @prng, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 <> 1
BEGIN
EXEC sp_OAGetProperty @prng, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @config
EXEC @hr = sp_OADestroy @key
EXEC @hr = sp_OADestroy @header
EXEC @hr = sp_OADestroy @jwt
EXEC @hr = sp_OADestroy @prng
RETURN
END
DECLARE @claims int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @claims OUT
EXEC sp_OAMethod @config, 'StringOf', @sTmp0 OUT, 'boxAppSettings.clientID'
EXEC sp_OAMethod @claims, 'UpdateString', @success OUT, 'iss', @sTmp0
EXEC sp_OAMethod @config, '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'
EXEC sp_OAMethod @claims, 'UpdateString', @success OUT, 'jti', @nonce
EXEC sp_OAMethod @jwt, 'GenNumericDate', @iTmp0 OUT, 45
EXEC sp_OAMethod @claims, 'UpdateInt', @success OUT, 'exp', @iTmp0
DECLARE @assertion nvarchar(4000)
EXEC sp_OAMethod @header, 'Emit', @sTmp0 OUT
EXEC sp_OAMethod @claims, 'Emit', @sTmp1 OUT
EXEC sp_OAMethod @jwt, 'CreateJwtPk', @assertion OUT, @sTmp0, @sTmp1, @key
EXEC sp_OAGetProperty @jwt, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 <> 1
BEGIN
EXEC sp_OAGetProperty @jwt, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @config
EXEC @hr = sp_OADestroy @key
EXEC @hr = sp_OADestroy @header
EXEC @hr = sp_OADestroy @jwt
EXEC @hr = sp_OADestroy @prng
EXEC @hr = sp_OADestroy @claims
RETURN
END
DECLARE @curl int
EXEC @hr = sp_OACreate 'Chilkat.HttpCurl', @curl OUT
EXEC sp_OAMethod @config, 'StringOf', @sTmp0 OUT, 'boxAppSettings.clientID'
EXEC sp_OAMethod @curl, 'SetVar', NULL, 'client_id', @sTmp0
EXEC sp_OAMethod @config, 'StringOf', @sTmp0 OUT, 'boxAppSettings.clientSecret'
EXEC sp_OAMethod @curl, 'SetVar', NULL, 'client_secret', @sTmp0
EXEC sp_OAMethod @curl, 'SetVar', NULL, 'assertion', @assertion
EXEC sp_OAMethod @curl, 'ClearTargetOutput', NULL, '*'
EXEC sp_OAMethod @curl, 'ClearVar', NULL, 'access_token'
EXEC sp_OAMethod @curl, 'AddTargetOutput', NULL, 'access_token', 'access_token'
EXEC sp_OAMethod @curl, 'DoYourThing', @success OUT, 'curl -X POST "https://api.box.com/oauth2/token" --data-urlencode "grant_type=urn:ietf:params:oauth:grant-type:jwt-bearer" --data-urlencode "client_id={{client_id}}" --data-urlencode "client_secret={{client_secret}}" --data-urlencode "assertion={{assertion}}"'
EXEC sp_OAGetProperty @curl, 'StatusCode', @iTmp0 OUT
IF (@success <> 1) or (@iTmp0 <> 200)
BEGIN
EXEC sp_OAGetProperty @curl, 'StatusCode', @iTmp0 OUT
PRINT 'Box request failed. HTTP status: ' + @iTmp0
EXEC sp_OAGetProperty @curl, 'FailReason', @iTmp0 OUT
PRINT 'Failure reason: ' + @iTmp0
EXEC @hr = sp_OADestroy @config
EXEC @hr = sp_OADestroy @key
EXEC @hr = sp_OADestroy @header
EXEC @hr = sp_OADestroy @jwt
EXEC @hr = sp_OADestroy @prng
EXEC @hr = sp_OADestroy @claims
EXEC @hr = sp_OADestroy @curl
RETURN
END
EXEC sp_OAMethod @curl, 'VarDefined', @iTmp0 OUT, '!'
IF @iTmp0 <> 1
BEGIN
PRINT 'Expected response fields are missing.'
EXEC @hr = sp_OADestroy @config
EXEC @hr = sp_OADestroy @key
EXEC @hr = sp_OADestroy @header
EXEC @hr = sp_OADestroy @jwt
EXEC @hr = sp_OADestroy @prng
EXEC @hr = sp_OADestroy @claims
EXEC @hr = sp_OADestroy @curl
RETURN
END
-- Reuse the token in this HttpCurl instance. JWT tokens are renewed by making
-- a new assertion/token request, not by using a refresh token.
PRINT 'JWT access token obtained.'
EXEC @hr = sp_OADestroy @config
EXEC @hr = sp_OADestroy @key
EXEC @hr = sp_OADestroy @header
EXEC @hr = sp_OADestroy @jwt
EXEC @hr = sp_OADestroy @prng
EXEC @hr = sp_OADestroy @claims
EXEC @hr = sp_OADestroy @curl
END
GO