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 outputThe 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…. 
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
View original
