June 25, 2010 at 11:48 am
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?
December 2, 2010 at 3:19 am
Hi
You can change CLR permission Level to unsafe
January 21, 2011 at 11:09 pm
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
January 22, 2011 at 1:45 am
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)
January 22, 2011 at 2:40 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy