SQL Server Requires Chilkat v11.0.0+
SQL Server
Duo Auth API - Async Auth
See more Duo Auth MFA Examples
If you enable async, then your application will be able to retrieve real-time status updates from the authentication process, rather than receiving no information until the process is complete.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 @success int
SELECT @success = 0
-- This example assumes the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
DECLARE @integrationKey nvarchar(4000)
SELECT @integrationKey = 'DIMS3V5QDVG9J9ABRXC4'
DECLARE @secretKey nvarchar(4000)
SELECT @secretKey = 'HWVQ46nubLBxhnRlKddTltWIi3hL0fIQF2qTvLab'
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OASetProperty @http, 'Accept', 'application/json'
-- Use your own hostname here:
DECLARE @url nvarchar(4000)
SELECT @url = 'https://api-a03782e1.duosecurity.com/auth/v2/auth'
-- This example requires Chilkat v9.5.0.89 or greater because Chilkat will automatically
-- generate and send the HMAC signature for the requires based on the integration key and secret key.
EXEC sp_OASetProperty @http, 'Login', @integrationKey
EXEC sp_OASetProperty @http, 'Password', @secretKey
DECLARE @req int
EXEC @hr = sp_OACreate 'Chilkat.HttpRequest', @req OUT
EXEC sp_OAMethod @req, 'AddParam', NULL, 'username', 'matt'
EXEC sp_OAMethod @req, 'AddParam', NULL, 'factor', 'push'
-- The device ID can be obtained from the preauth response. See Duo Preauth Example
EXEC sp_OAMethod @req, 'AddParam', NULL, 'device', 'DP6GYVTQ5NK82BMR851F'
-- Add the async param to get an immediate response, then periodically check for updates to find out when the MFA authentication completes for fails.
EXEC sp_OAMethod @req, 'AddParam', NULL, 'async', '1'
EXEC sp_OASetProperty @req, 'HttpVerb', 'POST'
EXEC sp_OASetProperty @req, 'ContentType', 'application/x-www-form-urlencoded'
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpReq', @success OUT, @url, @req, @resp
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @resp
RETURN
END
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'status code = ' + @iTmp0
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @json, 'Load', @success OUT, @sTmp0
EXEC sp_OASetProperty @json, 'EmitCompact', 0
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
PRINT @sTmp0
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF @iTmp0 <> 200
BEGIN
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @json
RETURN
END
-- Sample successful output:
-- status code = 200
-- {
-- "stat": "OK",
-- "response": {
-- "txid": "45f7c92b-f45f-4862-8545-e0f58e78075a"
-- }
-- }
DECLARE @txid nvarchar(4000)
EXEC sp_OAMethod @json, 'StringOf', @txid OUT, 'response.txid'
-- Use your own hostname here:
DECLARE @sbUrl int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbUrl OUT
EXEC sp_OAMethod @sbUrl, 'Append', @success OUT, 'https://api-a03782e1.duosecurity.com/auth/v2/auth_status?txid='
EXEC sp_OAMethod @sbUrl, 'Append', @success OUT, @txid
DECLARE @url nvarchar(4000)
EXEC sp_OAMethod @sbUrl, 'GetAsString', @url OUT
PRINT 'Auth status URL: ' + @url
DECLARE @sbResult int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResult OUT
DECLARE @responseStatus nvarchar(4000)
DECLARE @responseStatus_msg nvarchar(4000)
-- Wait for a response...
DECLARE @i int
SELECT @i = 0
DECLARE @maxWaitIterations int
SELECT @maxWaitIterations = 100
WHILE @i < @maxWaitIterations
BEGIN
-- Wait 3 seconds.
EXEC sp_OAMethod @http, 'SleepMs', NULL, 3000
PRINT 'Polling...'
EXEC sp_OAMethod @http, 'HttpNoBody', @success OUT, 'GET', @url, @resp
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @sbUrl
EXEC @hr = sp_OADestroy @sbResult
RETURN
END
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF @iTmp0 <> 200
BEGIN
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'error status code = ' + @iTmp0
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
PRINT @sTmp0
PRINT 'Failed.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @sbUrl
EXEC @hr = sp_OADestroy @sbResult
RETURN
END
-- Sample response:
-- {
-- "stat": "OK",
-- "response": {
-- "result": "waiting",
-- "status": "pushed",
-- "status_msg": "Pushed a login request to your phone..."
-- }
-- }
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @json, 'Load', @success OUT, @sTmp0
-- The responseResult can be "allow", "deny", or "waiting"
EXEC sp_OAMethod @sbResult, 'Clear', NULL
EXEC sp_OAMethod @json, 'StringOfSb', @success OUT, 'response.result', @sbResult
EXEC sp_OAMethod @json, 'StringOf', @responseStatus OUT, 'response.status'
EXEC sp_OAMethod @json, 'StringOf', @responseStatus_msg OUT, 'response.status_msg'
EXEC sp_OAMethod @sbResult, 'GetAsString', @sTmp0 OUT
PRINT @sTmp0
PRINT @responseStatus
PRINT @responseStatus_msg
PRINT ''
EXEC sp_OAMethod @sbResult, 'ContentsEqual', @iTmp0 OUT, 'waiting', 1
IF @iTmp0 = 1
BEGIN
SELECT @i = @i + 1
END
ELSE
BEGIN
-- Force loop exit..
SELECT @i = @maxWaitIterations
END
END
PRINT 'Finished.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @sbUrl
EXEC @hr = sp_OADestroy @sbResult
END
GO