I am trying connect to a third party API which can do payment transactions. we have the code build on PHP which does it correctly. But our current application is purely running on MS ACCESS and SQL server. So I am trying to build the same functionality on SQL server stored procedures.
Below is the PHP code which is running without any error(s):
<?php
date_default_timezone_set('Asia/Calcutta');
//sandbox source key
$sourcekey = "_xxxxxxxxxxxxxxxxxx";
$pin = "xxxxxx";
// generate random seed value
$seed=time().rand();
// setting hash value using sha256
$clear= $sourcekey.$seed.$pin;
$hash="s2/".$seed."/".hash('sha256',$clear);
//sandbox endpoint
$service_url = "https://example.com/transactions";
//$encoded=base64_encode("$sourcekey:$hash");
$headers = ['Content-type: application/json' ];
$request = [
'command' => 'cc:sale',
'amount' => '5.00',
'amount_detail' => array(
'tax' => '1.00',
'tip' => '0.50'
),
'creditcard' => array(
'cardholder' => 'John doe',
'number' => '4000100011112224',
'expiration' => '0919',
'cvc' => '123',
'avs_street' => '1234 Main',
'avs_zip' => '12345'
),
'invoice' => '123456789'
];
$curl_post_data = json_encode($request);
$curl = curl_init($service_url);
curl_setopt($curl, CURLOPT_URL, $service_url);
curl_setopt($curl, CURLOPT_HTTPAUTH, CURLAUTH_BASIC);
print_r("$sourcekey:$hash");
curl_setopt($curl, CURLOPT_USERPWD, "$sourcekey:$hash");
curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
curl_setopt($curl, CURLOPT_POST, true);
curl_setopt($curl, CURLOPT_POSTFIELDS, $curl_post_data);
curl_setopt($curl, CURLOPT_HTTPHEADER, $headers);
$curl_response = curl_exec($curl);
print_r($curl_response);
$response = json_decode($curl_response);
print_r($response);
?>
For some reason the below post request from SQL procedure is not getting authorized even when the same API key is used.
SQL code
DECLARE @authHeader NVARCHAR(64);
DECLARE @contentType NVARCHAR(64);
DECLARE @postData NVARCHAR(2000);
DECLARE @responseText NVARCHAR(2000);
DECLARE @responseXML NVARCHAR(2000);
DECLARE @ret INT;
DECLARE @status NVARCHAR(32);
DECLARE @statusText NVARCHAR(32);
DECLARE @token INT;
DECLARE @url NVARCHAR(256);
SET @authHeader = 'BASIC _xxxxxxxxxxxxxxxx';
SET @contentType = 'application/x-www-form-urlencoded';
SET @postData = '';
SET @url = 'https://example.com//transactions';
-- Open the connection.
EXEC @ret = sp_OACreate 'MSXML2.ServerXMLHTTP', @token OUT;
IF @ret <> 0 RAISERROR('Unable to open HTTP connection.', 10, 1);
-- Send the request.
EXEC @ret = sp_OAMethod @token, 'open', NULL, 'POST', @url, 'false';
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Authentication', @authHeader;
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Content-type', @contentType;
EXEC @ret = sp_OAMethod @token, 'send', NULL, @postData;
-- Handle the response.
EXEC @ret = sp_OAGetProperty @token, 'status', @status OUT;
EXEC @ret = sp_OAGetProperty @token, 'statusText', @statusText OUT;
EXEC @ret = sp_OAGetProperty @token, 'responseText', @responseText OUT;
-- Show the response.
PRINT 'Status: ' + @status + ' (' + @statusText + ')';
PRINT 'Response text: ' + @responseText;
-- Close the connection.
EXEC @ret = sp_OADestroy @token;
IF @ret <> 0 RAISERROR('Unable to close HTTP connection.', 10, 1);
Error Message
{"error":"Valid authentication required","errorcode":0}