CODEWITHSUNDEEP
c#wcfexceptiontriggerssqlclr
Aaaaaaaa
Aaaaaaaa

Reputation: 2114

CLR Trigger exception when try to communicate with a WCF service

I have a CLR SQL Trigger, that tries to communicate with a WCF service on the basis of the following article

When I try to update/insert a record, I get the following exception:

No row was updated.    
The data in row 1 was not committed.    
Error Source: .Net SqlClient Data Provider.    
Error Message: A .NET Framework error occured during execution of a user-defined routine or aggregate "WCFTrigger": System.Security.HostProtectionException: Attempt to perform an operation that was forbidden by the CLR host.    
The protected resource (only available with full trust) where: All    
The demanded resources were: Synchronization, ExternalThreading    
System.Security.HostProtectionException:    
  at System.ServiceModel.Description.TypeLoader.LoadContractDescriptionHelper(Type ContactType, Type ServiceType, Object serviceImplementation)    
  at System.ServiceModel.ChannelFactory '1.CreateDescription()    
  at System.ServiceModel.ChannelFactory.InitializeEndpoint(Binding binding, 
EndpointAddress address)    
  at System.ServiceModel.ChannelFactory '1..ctor(Binding binding, EndpointAddress address)    
  at System.ServiceModel.ClientBase '1..ctor(Binding binding, EndpointAddress address)    
  at ServiceClient.WCFServiceReference.ServiceContractClient..ctor(Binding binding, 
EndpointAddress address)

The host is just like in the article, the client's app.config:

<?xml version="1.0"?>
<configuration>
    <system.serviceModel>
        <bindings>
            <wsHttpBinding>
                <binding name="WSHttpBinding_IServiceContract" closeTimeout="00:01:00" openTimeout="00:01:00" receiveTimeout="00:10:00" sendTimeout="00:01:00" bypassProxyOnLocal="false" transactionFlow="false" hostNameComparisonMode="StrongWildcard" maxBufferPoolSize="524288" maxReceivedMessageSize="65536" messageEncoding="Text" textEncoding="utf-8" useDefaultWebProxy="true" allowCookies="false">
                    <readerQuotas maxDepth="32" maxStringContentLength="8192" maxArrayLength="16384" maxBytesPerRead="4096" maxNameTableCharCount="16384"/>
                    <reliableSession ordered="true" inactivityTimeout="00:10:00" enabled="false"/>
                    <security mode="Message">
                        <transport clientCredentialType="Windows" proxyCredentialType="None" realm=""/>
                        <message clientCredentialType="Windows" negotiateServiceCredential="true" algorithmSuite="Default"/>
                    </security>
                </binding>
            </wsHttpBinding>
        </bindings>
        <client>
            <endpoint address="http://localhost:8000/services/MyService" binding="wsHttpBinding" bindingConfiguration="WSHttpBinding_IServiceContract" contract="WCFServiceReference.IServiceContract" name="WSHttpBinding_IServiceContract">
                <identity>
                    <userPrincipalName value="[email protected]"/>
                </identity>
            </endpoint>
        </client>
    </system.serviceModel>
<startup><supportedRuntime version="v2.0.50727"/></startup></configuration>

Has anybody any idea, what is that and why?

The code of the trigger:

public partial class Triggers {
    [SqlProcedure()]
    public static void SendData( String crudType ) {
        EndpointAddress endpoint = new EndpointAddress( new Uri( "http://localhost:8000/services/myservice" ) );
        WSHttpBinding httpBinding = new WSHttpBinding();
        ServiceClient.WCFServiceReference.ServiceContractClient  myClient = new ServiceClient.WCFServiceReference.ServiceContractClient( httpBinding, endpoint );

            switch( crudType ) {
                case "Update":
                    myClient.UpdateOccured();
                    break;
                case "Insert":
                    myClient.InsertOccured();
                    break;
        }
    }

    [Microsoft.SqlServer.Server.SqlTrigger( Name = "WCFTrigger",
       Target = "tbCR", Event = "FOR UPDATE, INSERT" )]
    public static void Trigger1() {
        SqlTriggerContext myContext = SqlContext.TriggerContext;

        switch( myContext.TriggerAction ) {
            case TriggerAction.Update:
                SendData( "Update" );
                break;
            case TriggerAction.Insert:
                SendData( "Insert" );
                break;
        }
    }
}

The proxy:

namespace ServiceClient.WCFServiceReference {
    [System.CodeDom.Compiler.GeneratedCodeAttribute("System.ServiceModel", "4.0.0.0")]
    [System.ServiceModel.ServiceContractAttribute(ConfigurationName="WCFServiceReference.IServiceContract")]
    public interface IServiceContract {

        [System.ServiceModel.OperationContractAttribute(Action="http://tempuri.org/IServiceContract/UpdateOccured", ReplyAction="http://tempuri.org/IServiceContract/UpdateOccuredResponse")]
        void UpdateOccured();

        [System.ServiceModel.OperationContractAttribute(Action="http://tempuri.org/IServiceContract/InsertOccured", ReplyAction="http://tempuri.org/IServiceContract/InsertOccuredResponse")]
        void InsertOccured();
    }

    [System.CodeDom.Compiler.GeneratedCodeAttribute("System.ServiceModel", "4.0.0.0")]
    public interface IServiceContractChannel : ServiceClient.WCFServiceReference.IServiceContract, System.ServiceModel.IClientChannel {
    }

    [System.Diagnostics.DebuggerStepThroughAttribute()]
    [System.CodeDom.Compiler.GeneratedCodeAttribute("System.ServiceModel", "4.0.0.0")]
    public partial class ServiceContractClient : System.ServiceModel.ClientBase<ServiceClient.WCFServiceReference.IServiceContract>, ServiceClient.WCFServiceReference.IServiceContract {

        public ServiceContractClient() {
        }

        public ServiceContractClient(string endpointConfigurationName) : 
                base(endpointConfigurationName) {
        }

        public ServiceContractClient(string endpointConfigurationName, string remoteAddress) : 
                base(endpointConfigurationName, remoteAddress) {
        }

        public ServiceContractClient(string endpointConfigurationName, System.ServiceModel.EndpointAddress remoteAddress) : 
                base(endpointConfigurationName, remoteAddress) {
        }

        public ServiceContractClient(System.ServiceModel.Channels.Binding binding, System.ServiceModel.EndpointAddress remoteAddress) : 
                base(binding, remoteAddress) {
        }

        public void UpdateOccured() {
            base.Channel.UpdateOccured();
        }

        public void InsertOccured() {
            base.Channel.InsertOccured();
        }
    }
}

Upvotes: 1

Views: 1307

Answers (2)

Sixto Saez
Sixto Saez

Reputation: 12680

The issue is caused running .NET code (WCF client calling service) in SQL Server under a partial trust code access security (CAS) setting. Look at the this MSDN article on configuring CAS for SQL Server.

EDIT:

As an alternative to enabling full trust, configure a webHttpBinding endpoint on the WCF service. Call the service using the System.Net HttpWebRequest & HttpWebResponse classes to avoid using the WCF ChannelFactory based plumbing.

Upvotes: 2

Coding Flow
Coding Flow

Reputation: 21881

You can't do anything multithreaded in any SQLCLR assemblies and the exception is telling you that you are trying to use threading. Are you calling the WCF service asynchronously?

I would reccomend posting your code from the SQLCLR trigger for more help.

Calling WCF services from SQL CLR is much more difficult that calling asmx web services, are you sure you need wcf?

Upvotes: 1

Related Questions