Skip to main content
Solved

A certificate is required to complete client authentication

  • September 1, 2020
  • 4 replies
  • 2874 views

htimmermans
Captain
Forum|alt.badge.img+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

 

Best answer by Mark Jongeling

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

View original
Did this topic help you find an answer to your question?
This topic has been closed for comments

4 replies

Forum|alt.badge.img+15
  • Superhero
  • 196 replies
  • September 1, 2020

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


htimmermans
Captain
Forum|alt.badge.img+12
  • Author
  • Captain
  • 61 replies
  • September 1, 2020

@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?


Forum|alt.badge.img+15
  • Superhero
  • 196 replies
  • September 1, 2020

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).


Mark Jongeling
Administrator
Forum|alt.badge.img+23
  • Administrator
  • 3936 replies
  • Answer
  • September 1, 2020

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


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings