calling SSL web service from CLR

  • I need to call a web service through CLR.

    The code below is working fine when calling direct from http://....

    namespace BusinessDaysCalc

    {

    public partial class UserDefinedFunctions

    {

    [SqlFunction(DataAccess = DataAccessKind.Read)]

    public static SqlDateTime CalcBusinessDays(SqlInt32 month, SqlInt32 year, SqlInt32 daysInc)

    {

    // Read the URL of web service

    var wsCaller = Common.RetWsKondorPlus();

    if (string.IsNullOrEmpty(wsCaller) || wsCaller.StartsWith("ERROR"))

    return (SqlDateTime.Null);

    // Set always to accept certificates

    // System.Net.ServicePointManager.ServerCertificateValidationCallback +=

    // (sender, certificate, chain, sslPolicyErrors) => (true);

    // Initialize web service

    var service = new WebServiceKondorPlus.Service {Url = wsCaller};

    // Call the web service

    var dt = service.GetBusinessDate(month.Value, year.Value, daysInc.Value);

    return new SqlDateTime(dt);

    }

    };

    }

    When calling from https://.... I am getting the following error:

    Msg 6522, Level 16, State 1, Line 2

    A .NET Framework error occurred during execution of user-defined routine or aggregate "ufnCalcBusinessDays":

    System.Net.WebException: The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel. ---> System.Security.Authentication.AuthenticationException: The remote certificate is invalid according to the validation procedure.

    System.Security.Authentication.AuthenticationException:

    at System.Net.Security.SslState.StartSendAuthResetSignal(ProtocolToken message, AsyncProtocolRequest asyncRequest, Exception exception)

    at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)

    at System.Net.Security.SslState.StartSendBlob(Byte[] incoming, Int32 count, AsyncProtocolRequest asyncRequest)

    at System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)

    at System.Net.Security.SslState.StartReadFrame(Byte[] buffer, Int32 readBytes, AsyncProtocolRequest asyncRequest)

    at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)

    at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)

    at System.Net.Security.SslState.StartSendBlob(Byte[] incoming, Int32 count, AsyncProtocolRequest asyncRequest)

    at System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)

    at System.Net.Security.SslState.StartReadFrame(Byte[] buffer, Int32 readBytes, AsyncPro

    ...

    System.Net.WebException:

    at System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebRequest request)

    at System.Web.Services.Protocols.HttpWebClientProtocol.GetWebResponse(WebRequest request)

    at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)

    at BusinessDaysCalc.WebServiceKondorPlus.Service.GetBusinessDate(Int32 month, Int32 year, Int32 daysInc)...

    I then comment out the line for the certification delegation:

    // Set always to accept certificates

    System.Net.ServicePointManager.ServerCertificateValidationCallback += (sender, certificate, chain, sslPolicyErrors) => (true);

    , reload the assembly and the result is:

    Msg 6522, Level 16, State 1, Line 2

    A .NET Framework error occurred during execution of user-defined routine or aggregate "ufnCalcBusinessDays":

    System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

    System.Security.SecurityException:

    at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

    at System.Security.CodeAccessPermission.Demand()

    at System.Net.ServicePointManager.set_ServerCertificateValidationCallback(RemoteCertificateValidationCallback value)

    at BusinessDaysCalc.UserDefinedFunctions.CalcBusinessDays(SqlInt32 month, SqlInt32 year, SqlInt32 daysInc)

    .

    If i run the same code outside the clr (in a console application), it is working fine for SSL calls.(https://....)

    Am I missing something?

  • Hi

    You can change CLR permission Level to unsafe

  • AHi!

    I am having a similar problem connecting from the SQL server 2005 using a VB.NET CLR .NET 2.0 with XML-RPC on HTTPS to a XML-RPC-HTTPs remote service. CLR Strong Name is fine.

    On my case I am getting this error. I added the CLR .DLL with Trust Strong Name to the RUn Security Service using

    .NET Framework 2.0 Configuration (version 2.0.50727.42)

    Msg 6522, Level 16, State 1, Procedure PMPPwdRretrieve, Line 0

    A .NET Framework error occurred during execution of user-defined routine or aggregate "PMPPwdRretrieve":

    System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

    System.Security.SecurityException:

    at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

    at System.Security.CodeAccessPermission.Demand()

    at System.Net.ServicePointManager.set_ServerCertificateValidationCallback(RemoteCertificateValidationCallback value)

    at PmpXmlRPCdll.StoredProcedurePMPPwdRetrieve.PMPPwdRretrieve(String Url, String certABPth, String ResourName, String UserAccount, String reason, String& PwdPMP, Int32& ResCode)

    .

    How did you resolve this issue. I need to integrate SQL Server 2005 with a other product using a XML-RPC Https calling.

    Do you have any idea ??

    Many Thanks.

    Miguel Soto

    From Santiago Chile

  • Unfortunatelly i couldn't resolve the https issue but for http here is my script:

    USE[redb]

    go

    -- CLEANUP

    DROP FUNCTION [dbo].[ufnCalcBusinessDays]

    DROP ASSEMBLY [REDB.XmlSerializers]

    DROP ASSEMBLY [REDB]

    -- Create Assembly

    ALTER DATABASE REDB SET TRUSTWORTHY ON

    CREATE ASSEMBLY REDB FROM 'd:\Data Volume\SQL\CLR\BusinessDaysCalc.dll'

    WITH PERMISSION_SET = EXTERNAL_ACCESS

    CREATE ASSEMBLY [REDB.XmlSerializers] FROM 'd:\Data Volume\SQL\CLR\BusinessDaysCalc.XmlSerializers.dll'

    WITH PERMISSION_SET = SAFE

    CREATE FUNCTION [ufnCalcBusinessDays] (@month int, @year int, @daysInc int)

    RETURNS [DATETIME]

    AS EXTERNAL NAME [REDB].[BusinessDaysCalc.UserDefinedFunctions].[CalcBusinessDays]

    SELECT dbo.ufnCalcBusinessDays(01,2010, 7)

  • I am using this library CookComputing.XmlRpc.dll, Date Modifies 22-05-2005 14:27,File Number : 0.9.2.1

    from XML-RPC.NET Charles Cook(c) 2001-2005

    My program PmpXmlRPCdll.dll that I upload as CLR UNSAFE(And tried with External Access, too) + I had to load all the Framework Library assembly required on Host Database SQL Server 2005 SP2.

    PmpXmlRPCdll.dll

    Imports CookComputing.XmlRpc

    Imports System.Security.Cryptography.X509Certificates

    Imports System.Net

    Public Structure Respassword

    Public resultCode As Integer

    Public message As String

    End Structure

    Public Interface IPwdRetrieve

    <CookComputing.XmlRpc.XmlRpcMethod("pmp.password.retrieve")> _

    Function pwdretrieve(ByVal resourceName As String, ByVal UserAccount As String, ByVal reason As String) As Respassword

    End Interface

    Public Class StoredProcedurePMPPwdRetrieve

    <Microsoft.SqlServer.Server.SqlProcedure()> _

    Public Shared Sub PMPPwdRretrieve(ByVal Url As String, ByVal certABPth As String, _

    ByVal ResourName As String, ByVal UserAccount As String, ByVal reason As String, _

    ByRef PwdPMP As String, ByRef ResCode As Integer)

    Dim mathProxy As IPwdRetrieve

    Dim ClientProtocol As XmlRpcClientProtocol

    mathProxy = CType(XmlRpcProxyGen.Create(GetType(IPwdRetrieve)), IPwdRetrieve)

    ClientProtocol = CType(mathProxy, XmlRpcClientProtocol)

    Dim pmpClientX509Cert As X509Certificate = X509Certificate.CreateFromCertFile(certABPth)

    ClientProtocol.ClientCertificates.Add(pmpClientX509Cert)

    ServicePointManager.ServerCertificateValidationCallback = AddressOf PMPCertificateValidationCallBack

    ClientProtocol.Url = Url

    Dim result As Respassword = mathProxy.pwdretrieve(ResourName, UserAccount, reason)

    PwdPMP = result.message

    ResCode = CInt(result.resultCode)

    End Sub

    Private Shared Function PMPCertificateValidationCallBack(ByVal sender As Object, _

    ByVal certificate As X509Certificate, ByVal chain As X509Chain, ByVal sslPolicyErrors As Security.SslPolicyErrors) As Boolean

    ' Since we are using Self signed or Microsoft CA, we are skiping the OCSP check and certificate validation

    Return True

    End Function

    End Class

    On HTTP and without the Certificate and Security connection it is working fine, I can call the remote XML-RPC HTTP web Service

    I tried with the XML-RPC.NET Charles Cook(c) 2001-2005 remote example and a calling from the SQL 2005 procedure with the CLR work fine.

    I go getting a similar error that you got with the https and using the certificate. I added the assembly to the

    .NET Framework 2.0 Configuration (version 2.0.50727.42) on the Runtime Security Policy but i doesn't work. I am getting the same error on the Https.

    Msg 6522, Level 16, State 1, Procedure PMPPwdRretrieve, Line 0

    A .NET Framework error occurred during execution of user-defined routine or aggregate "PMPPwdRretrieve":

    System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

    System.Security.SecurityException:

    at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

    at System.Security.CodeAccessPermission.Demand()

    at System.Net.ServicePointManager.set_ServerCertificateValidationCallback(RemoteCertificateValidationCallback value)

    at PmpXmlRPCdll.StoredProcedurePMPPwdRetrieve.PMPPwdRretrieve(String Url, String certABPth, String ResourName, String UserAccount, String reason, String& PwdPMP, Int32& ResCode)

    Do you have more information about this kind of integration from SQL Server 2005, using XML-RPC on HTTPS with Certificate.?

    I have to integrate this as soon as possible and I have been stopped on this issue for alreade 1 weeks..

    Miguel Soto

    Santiago Chile

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply