SQL Server
SQL Server
Salesforce OAuth2 Access Token
See more OAuth2 Examples
Demonstrates how to get a Salesforce OAuth2 access token from a desktop application or script.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 @success int
SELECT @success = 0
-- To further clarify, see OAuth 2.0 Authorization Flow
DECLARE @oauth2 int
EXEC @hr = sp_OACreate 'Chilkat.OAuth2', @oauth2 OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- This should be the port in the localhost callback URL for your connected app.
-- The callback URL would look like "http://localhost:3017/" if the port number is 3017.
EXEC sp_OASetProperty @oauth2, 'ListenPort', 3017
EXEC sp_OASetProperty @oauth2, 'AuthorizationEndpoint', 'https://login.salesforce.com/services/oauth2/authorize'
EXEC sp_OASetProperty @oauth2, 'TokenEndpoint', 'https://login.salesforce.com/services/oauth2/token'
-- Replace this with your actual client ID (Consumer Key)
EXEC sp_OASetProperty @oauth2, 'ClientId', 'CLIENT_ID'
-- We don't have to include the client secret.
-- See the screenshot of our Connected App below..
-- This is for Require Proof Key for Code Exchange (PKCE)
-- See OAuth2 PKCE Explained
EXEC sp_OASetProperty @oauth2, 'CodeChallenge', 1
EXEC sp_OASetProperty @oauth2, 'CodeChallengeMethod', 'S256'
-- To force the OAuth consent screen to reappear (even if previously granted), use:
EXEC sp_OAMethod @oauth2, 'AddAuthQueryParam', @success OUT, 'prompt', 'consent'
-- Begin the OAuth2 authorization flow. This returns a URL that should be loaded in a browser.
DECLARE @url nvarchar(4000)
EXEC sp_OAMethod @oauth2, 'StartAuth', @url OUT
EXEC sp_OAGetProperty @oauth2, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 <> 1
BEGIN
EXEC sp_OAGetProperty @oauth2, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @oauth2
RETURN
END
-- Launch the default browser on the system and navigate to the url.
-- The LaunchBrowser method was added in Chilkat v10.1.2.
EXEC sp_OAMethod @oauth2, 'LaunchBrowser', @success OUT, @url
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @oauth2, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @oauth2
RETURN
END
-- Wait for the user to approve or deny authorization in the browser.
DECLARE @numMsWaited int
SELECT @numMsWaited = 0
EXEC sp_OAGetProperty @oauth2, 'AuthFlowState', @iTmp0 OUT
WHILE (@numMsWaited < 90000) and (@iTmp0 < 3)
BEGIN
EXEC sp_OAMethod @oauth2, 'SleepMs', NULL, 100
SELECT @numMsWaited = @numMsWaited + 100
END
-- If the browser does not respond within the specified time, AuthFlowState will be:
--
-- 1: Waiting for Redirect – The OAuth2 background thread is waiting for the browser's redirect request.
-- 2: Waiting for Final Response – The thread is awaiting the final access token response.
-- In either case, cancel the background task initiated by StartAuth.
EXEC sp_OAGetProperty @oauth2, 'AuthFlowState', @iTmp0 OUT
IF @iTmp0 < 3
BEGIN
EXEC sp_OAMethod @oauth2, 'Cancel', @success OUT
PRINT 'No response from the browser!'
EXEC @hr = sp_OADestroy @oauth2
RETURN
END
-- Check AuthFlowState to determine if authorization was granted, denied, or failed:
--
-- 3: Success – OAuth2 flow completed, the background thread exited, and the successful response is in AccessTokenResponse.
-- 4: Access Denied – OAuth2 flow completed, the background thread exited, and the error response is in AccessTokenResponse.
-- 5: Failure – OAuth2 flow failed before completion, the background thread exited, and error details are in FailureInfo.
EXEC sp_OAGetProperty @oauth2, 'AuthFlowState', @iTmp0 OUT
IF @iTmp0 = 5
BEGIN
PRINT 'OAuth2 failed to complete.'
EXEC sp_OAGetProperty @oauth2, 'FailureInfo', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @oauth2
RETURN
END
EXEC sp_OAGetProperty @oauth2, 'AuthFlowState', @iTmp0 OUT
IF @iTmp0 = 4
BEGIN
PRINT 'OAuth2 authorization was denied.'
EXEC sp_OAGetProperty @oauth2, 'AccessTokenResponse', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @oauth2
RETURN
END
EXEC sp_OAGetProperty @oauth2, 'AuthFlowState', @iTmp0 OUT
IF @iTmp0 <> 3
BEGIN
EXEC sp_OAGetProperty @oauth2, 'AuthFlowState', @iTmp0 OUT
PRINT 'Unexpected AuthFlowState:' + @iTmp0
EXEC @hr = sp_OADestroy @oauth2
RETURN
END
PRINT 'OAuth2 authorization granted!'
EXEC sp_OAGetProperty @oauth2, 'AccessToken', @sTmp0 OUT
PRINT 'Access Token = ' + @sTmp0
-- Save the full JSON access token response to a file.
DECLARE @sbJson int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbJson OUT
EXEC sp_OAGetProperty @oauth2, 'AccessTokenResponse', @sTmp0 OUT
EXEC sp_OAMethod @sbJson, 'Append', @success OUT, @sTmp0
EXEC sp_OAMethod @sbJson, 'WriteFile', @success OUT, 'qa_data/tokens/_salesforce.json', 'utf-8', 0
-- The saved JSON response looks like this:
-- {
-- "access_token": "00D41000....uLZBpT6",
-- "refresh_token": "5Aep....25xdGgkrV",
-- "signature": "cjTbSc5DvcKpaMoRTzuQTJLb1tcMw8LEO01flq4aMD4=",
-- "scope": "refresh_token id",
-- "instance_url": "https://d41000000f8a0eak-dev-ed.my.salesforce.com",
-- "id": "https://login.salesforce.com/id/00D41000000F8A0EAK/005410000....xAAE",
-- "token_type": "Bearer",
-- "issued_at": "1738348388166"
-- }
-- --------------------------------------------------------------------------
-- Here's a screenshot of a Salesforce Connected App
-- with the correct Callback URL for this example,
-- and also selects "Require Proof Key for Code Exchange (PKCE)"
-- and does not requires the secret for the authorization or refresh flow,
-- and also allows for the Client Credentials flow.
-- 
EXEC @hr = sp_OADestroy @oauth2
EXEC @hr = sp_OADestroy @sbJson
END
GO