asp.net - 从 SQL Server 中的存储过程调用 Web 服务时，客户端证书凭据未被识别错误

Question

我正在尝试从 SQL Server 2014 中的存储过程调用 Web 服务，但出现此错误

客户端证书凭据未被识别

此代码在 SQL Server 2017 中按预期工作，但在 SQL Server 2014 中引发错误。如何解决 SQL Server 2014 中的问题？

我在 power shell 中创建了一个证书并启用了 OLE 自动化。

--Creation of certificate
New-SelfSignedCertificate -DnsName "ServerXMLHTTP", "ServerXMLHTTP" -CertStoreLocation "cert:\LocalMachine\My"

--SQL Code to invoke the web service from a stored procedure
DECLARE @username VARCHAR(20)
DECLARE @password VARCHAR(20)
DECLARE @obj INT
DECLARE @sUrl VARCHAR(200)
DECLARE @response VARCHAR(8000)
DECLARE @hr INT
DECLARE @src VARCHAR(255)
DECLARE @desc VARCHAR(255)
DECLARE @status INT
DECLARE @statusText VARCHAR(200)
DECLARE @postData NVARCHAR(4000)
DECLARE @source VARBINARY(MAX),  
        @encoded_base64 VARCHAR(MAX),  
        @auth VARCHAR(MAX)
DECLARE @ret BIGINT

SET @username = 'suhas'
SET @password = '12345'

SET @source = CONVERT(VARBINARY(MAX), @username+':'+@password) 

-- Convert from varbinary to base64 string 
SET @encoded_base64 = CAST(N'' AS XML).value('xs:base64Binary(sql:variable       
("@source"))', 'varchar(max)') 
SET @postData = '{"LeadDetails":{"LeadID":"3715089","Counter":"2"}}';   

 
SET @sUrl = 'https://someWebLink/suhas'


print @surl
print @postdata
print @encoded_base64
SET @auth = 'Basic ' + @encoded_base64
print @auth
exec @ret= sp_OACreate 'MSXML2.SERVERXMLhttp', @obj out
IF @ret <> 0     
BEGIN    
      EXEC sp_OAGetErrorInfo @obj, @source OUT, @desc OUT    
      SELECT      hResult = convert(varbinary(4), @ret),     
                  source = @source,     
                  description = @desc,     
                  FailPoint = 'Create failed',     
                  MedthodName = 'POST'     
      --goto destroy     
      return    
END    
print @ret--YES
exec @ret=sp_OAMethod @obj, 'Open', null, 'POST', @sUrl, false
IF @ret <> 0     
BEGIN    
      EXEC sp_OAGetErrorInfo @obj, @source OUT, @desc OUT    
      SELECT      hResult = convert(varbinary(4), @ret),     
                  source = @source,     
                  description = @desc,     
                  FailPoint = 'Create failed',     
                  MedthodName = 'POST'     
      --goto destroy     
      return    
END    
print @ret--YES
exec @ret=sp_OAMethod @obj, 'setRequestHeader', null, 'Authorization', @auth
IF @ret <> 0     
BEGIN    
      EXEC sp_OAGetErrorInfo @obj, @source OUT, @desc OUT    
      SELECT      hResult = convert(varbinary(4), @ret),     
                  source = @source,     
                  description = @desc,     
                  FailPoint = 'AUTHO failed',     
                  MedthodName = 'POST'     
      ---goto destroy     
      return    
END    
print @ret--YES
exec @ret=sp_OAMethod @obj, 'setRequestHeader', null, 'Accept', 'application/raw'
IF @ret <> 0     
BEGIN    
      EXEC sp_OAGetErrorInfo @obj, @source OUT, @desc OUT    
      SELECT      hResult = convert(varbinary(4), @ret),     
                  source = @source,     
                  description = @desc,     
                  FailPoint = 'PASSING CONTENT TYPE failed',     
                  MedthodName = 'POST'     
      --goto destroy     
      return    
END    
print @ret--YES
EXEC @ret =sp_OAMethod @OBJ,'setOption',NULL,3,'LOCAL_MACHINE\My\ServerXMLHTTP'
IF @ret <> 0     
BEGIN    
      EXEC sp_OAGetErrorInfo @obj, @source OUT, @desc OUT    
      SELECT      hResult = convert(varbinary(4), @ret),     
                  source = @source,     
                  description = @desc,     
                  FailPoint = 'CERTIFICATE PASSING failed',     
                  MedthodName = 'POST'     
      --goto destroy     
      return    
END    
print @ret
EXEC @ret= sp_OAMethod @obj, 'send' , NULL, @postData
IF @ret <> 0     
BEGIN    
      EXEC sp_OAGetErrorInfo @obj, @source OUT, @desc OUT    
      SELECT      hResult = convert(varbinary(4), @ret),     
                  source = @source,     
                  description = @desc,     
                  FailPoint = 'PASSING DATA failed',     
                  MedthodName = 'POST'     
    --  goto destroy     
      return    
END    
print @ret
exec @ret= sp_OAgetproperty @obj, 'responseText', @response out
print @ret 
print @response
exec @ret= sp_OAgetproperty @obj, 'Status', @status out
print @ret
exec @ret= sp_OAgetproperty @obj, 'StatusText', @statusText out
print @ret
exec sp_OADestroy @obj
 
select @status [StatusCode], @statusText [StatusText], @response [Response]

return 

标签： asp.netsql-servernetworkingssmsdatabase-administration