Solved

A certificate is required to complete client authentication

  • 1 September 2020
  • 4 replies
  • 2255 views

Userlevel 2
Badge +12

I'm trying to get a response from a webservice, located outside our organisation. The site is secure (https), but other than that, the piece of code (dbo.make_soap_http_request in this case) is doing the trick on an internal webservice (http, not secure, Navision webservice).

The parameters (url, username, password etc etc) used when executing the instruction below work fine when executed from my or another desktop (no special certificates installed by our IT department) when using SOAP-UI for example).

Parameter @http_uri below contains an URL to a secure (https) site. 

    exec dbo.make_soap_http_request @http_uri            
, @http_request_method
, @soap_request_body
, 'http://gateway.integration.volvo.com/soap/1_0/WsMqServices/processMessageRequest'
, @username
, @password
, @soap_response output

The error I get:

A certificate is required to complete client authentication  

I have searched the internet on why this happens and how to get things working. But no luck so far.

What I have tried vigorously is setting things related to the need to use a certificate (look foor “Setoption” below). But nothing seems to work.

Anybody a clue where to go from here? Or how to get around this problem? Or am I the only one trying to connect to a secure server from a sql procedure?

 

The code for make_soap_http_request: It's a bit of a Frankenstein project, with pieces gathered from various sources found Google-ing…. :unamused:

IF    @http_request_method = ''

BEGIN

select FailPoint = 'Method Name must be set'

return

END


declare @objectID int

, @hResult int

, @source varchar(255)

, @desc varchar(255)

, @authHeader NVARCHAR(64) = 'BASIC Y3Mtd3Mtcy1ITUctUElOTkFDTEU6Z2djSjcmZSU4JjktZC0m'

, @statusText varchar(1000)

, @status varchar(1000)

, @len int

, @send varchar(8000)

;




set @soap_response = 'FAILED'

--EXEC @hResult = sp_OACreate 'MSXML2.ServerXMLHTTP', @objectID OUT

EXEC @hResult = sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @objectID OUT

IF @hResult <> 0

BEGIN

EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT

SELECT hResult = convert(varbinary(4), @hResult),

source = @source,

description = @desc,

FailPoint = 'Create failed',

MedthodName = @http_request_method

goto destroy

return

END

-- open the destination URI with Specified method

EXEC @hResult = sp_OAMethod @objectID, 'open', null, @http_request_method, @http_uri, 'false', @username, @password

IF @hResult <> 0

BEGIN

EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT

SELECT hResult = convert(varbinary(4), @hResult),

source = @source,

description = @desc,

FailPoint = 'Open failed',

MedthodName = @http_request_method

goto destroy

return

END


-- set request headers

EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Type', 'text/xml;charset=UTF-8'

IF @hResult <> 0

BEGIN

EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT

SELECT hResult = convert(varbinary(4), @hResult),

source = @source,

description = @desc,

FailPoint = 'SetRequestHeader failed',

MedthodName = @http_request_method

goto destroy

return

END


--EXEC @hResult = sp_OASetProperty @objectID, 'Option', 2, 13056

--EXEC @hResult = sp_OAMethod @objectID, 'setOption' , NULL , 2 , 13056

--EXEC @hResult = sp_OAMethod @objectID, 'setOption' , NULL , 3 , 'CURRENT_USER\My\BAS-HOLDING-CA'

EXEC @hResult = sp_OAMethod @objectID, 'setOption' , null, 2 , 13056


IF @hResult <> 0

BEGIN

EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT

SELECT hResult = convert(varbinary(4), @hResult),

source = @source,

description = @desc,

FailPoint = 'setoption failed',

MedthodName = @http_request_method

goto destroy

return

END


-- set request headers

EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Authentication', @authHeader

IF @hResult <> 0

BEGIN

EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT

SELECT hResult = convert(varbinary(4), @hResult),

source = @source,

description = @desc,

FailPoint = 'SetRequestHeader failed',

MedthodName = @http_request_method

goto destroy

return

END

-- set soap action

EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'SOAPAction', @soap_action

IF @hResult <> 0

BEGIN

EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT

SELECT hResult = convert(varbinary(4), @hResult),

source = @source,

description = @desc,

FailPoint = 'SetRequestHeader failed',

MedthodName = @http_request_method

goto destroy

return

END


set @len = len(@soap_request_body)

EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Length', @len

IF @hResult <> 0

BEGIN

EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT

SELECT hResult = convert(varbinary(4), @hResult),

source = @source,

description = @desc,

FailPoint = 'SetRequestHeader failed',

MedthodName = @http_request_method

goto destroy

return

END

/*

-- if you have headers in a table called RequestHeader you can go through them with this

DECLARE @HeaderKey varchar(500), @HeaderValue varchar(500)

DECLARE RequestHeader CURSOR

LOCAL FAST_FORWARD

FOR

SELECT HeaderKey, HeaderValue

FROM RequestHeaders

WHERE Method = @http_request_method

OPEN RequestHeader

FETCH NEXT FROM RequestHeader

INTO @HeaderKey, @HeaderValue

WHILE @@FETCH_STATUS = 0

BEGIN

--select @HeaderKey, @HeaderValue, @http_request_method

EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, @HeaderKey, @HeaderValue

IF @hResult <> 0

BEGIN

EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT

SELECT hResult = convert(varbinary(4), @hResult),

source = @source,

description = @desc,

FailPoint = 'SetRequestHeader failed',

MedthodName = @http_request_method

goto destroy

return

END

FETCH NEXT FROM RequestHeader

INTO @HeaderKey, @HeaderValue

END

CLOSE RequestHeader

DEALLOCATE RequestHeader

*/

-- send the request

set @send = 'send("' + REPLACE(@soap_request_body, '"', '''') + '")';

-- Aangepast HT EXEC @hResult = sp_OAMethod @objectID, 'send', null, @soap_request_body

--EXEC @hResult = sp_OAMethod @objectID, 'send', null, @soap_request_body

EXEC @hResult = sp_OAMethod @objectID, @send;

IF @hResult <> 0

BEGIN

EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT

SELECT hResult = convert(varbinary(4), @hResult),

source = @source,

description = @desc,

FailPoint = 'Send failed',

MedthodName = @http_request_method

goto destroy

return

END


-- Get status text

exec sp_OAGetProperty @objectID, 'StatusText', @statusText out

exec sp_OAGetProperty @objectID, 'Status', @status out

select @status, @statusText, @http_request_method

-- Get response text

exec sp_OAGetProperty @objectID, 'responseText', @soap_response out

--select @soap_response

IF @hResult <> 0

BEGIN

EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT

SELECT hResult = convert(varbinary(4), @hResult),

source = @source,

description = @desc,

FailPoint = 'ResponseText failed',

MedthodName = @http_request_method

goto destroy

return

END

destroy:

exec sp_OADestroy @objectID

 

icon

Best answer by Mark Jongeling 1 September 2020, 13:03

View original

This topic has been closed for comments

4 replies

Userlevel 5
Badge +15

Is there a particular reason you do a http call via sql? Our would you consider using a process flow?

https://docs.thinkwisesoftware.com/docs/sf/process_flows.html#https-connector

Userlevel 2
Badge +12

@René W 

I'm not sure if a http(s) connector works for soap services…. I could give it a try for this particular one, but some of the requests will have to be scheduled, and I don't think process flows work outside the GUI… Or do they?

Userlevel 5
Badge +15

I haven't used or tested it my self, but the documentation does say something about scheduled process flows, including http(s) support:

https://docs.thinkwisesoftware.com/docs/sf/process_flows.html#process-schedules

This is done via the Indicium service layer (which is mandatory starting 2020.2, so you'’ll probably will have it anyway).

Userlevel 7
Badge +23

System Flows are being handled by Indicium (Universal) according to the Schedules in IAM for the application(s). The flows can indeed run without having the GUI open. They don't require user input to start running; unlike process flows that do need user input and therefore the GUI needs to be opened. 

More on it here: https://docs.thinkwisesoftware.com/docs/sf/process_flows.html#process-schedules