Validating Alexa Skill Web Requests in c#

Amazon went a little OCD on the security verification for Alexa skill requests to custom https endpoints. During testing it will work fine if you don’t validate, but they check for this as part of the skill submission process.

I have been developing a custom connector for Alexa to work with the Microsoft Bot Framework, and just recently discovered the security requirements. Now I may be reinventing the wheel here a little bit, but I decided to build my own model classes. Maybe I’m a little OCD.

Anyway, to validate the alexa skill requests you must do the following:
1: Validate that the url supplied in a header for a certificate chain is valid
2: Validate the certificate and its chain
3: Use the certificate to verify the digital signature (which is supplied in another header) against the request body
4: Make sure the timestamp in the request body is within 150 seconds of now

Amazon’s instructions with regard to step 3 are a little misleading. They suggest you “decrypt” the digital signature using the public key. From what I can gather, RSA public keys do not decrypt; they only encrypt. Thankfully the digital signature validation process is something that is already implemented in the .NET Framework, so it’s not terribly difficult.

The two “tricky” parts are getting .NET to read certificates from a PEM container, and checking the signing certificate’s SAN list.

So hopefully this will be helpful to somebody.

First we have a helper class for parsing the PEM and a couple other little things

using System.Net.Http;
using System.Security.Cryptography;
using System.Security.Cryptography.X509Certificates;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Web;

namespace AlexaCustomChannel
{
    public static class PemHelper
    {
        static string CertHeader = "-----BEGIN CERTIFICATE-----";
        static string CertFooter = "-----END CERTIFICATE-----";

        static HttpClient _client = new HttpClient();


        public static IEnumerable<string> ParseSujectAlternativeNames(X509Certificate2 cert)
        {
            Regex sanRex = new Regex(@"^DNS Name=(.*)", RegexOptions.Compiled | RegexOptions.CultureInvariant);

            var sanList = from X509Extension ext in cert.Extensions
                          where ext.Oid.FriendlyName.Equals("Subject Alternative Name", StringComparison.Ordinal)
                          let data = new AsnEncodedData(ext.Oid, ext.RawData)
                          let text = data.Format(true)
                          from line in text.Split(new char[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries)
                          let match = sanRex.Match(line)
                          where match.Success && match.Groups.Count > 0 && !string.IsNullOrEmpty(match.Groups[1].Value)
                          select match.Groups[1].Value;

            return sanList;
        }


        public static bool ValidateCertificateChain(X509Certificate2 certificate, IEnumerable<X509Certificate2> chain)
        {
            using (var verifier = new X509Chain())
            {
                verifier.ChainPolicy.ExtraStore.AddRange(chain.ToArray());
                var result = verifier.Build(certificate);
                return result;
            }
        }

        public static X509Certificate2 ParseCertificate(string base64CertificateText)
        {
            var bytes = Convert.FromBase64String(base64CertificateText);
            X509Certificate2 cert = new X509Certificate2(bytes);
            return cert;
        }

        public static async Task<X509Certificate2[]> DownloadPemCertificatesAsync(string pemUri)
        {
            var pemText = await _client.GetStringAsync(pemUri);
            if (string.IsNullOrEmpty(pemText)) return null;
            return ReadPemCertificates(pemText);
        }


        public static X509Certificate2[] ReadPemCertificates(string pemString)
        {
            var lines = pemString.Split(new char[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries);
            List<string> certList = new List<string>();
            StringBuilder grouper = null;
            for (int i = 0; i < lines.Length; i++)
            {
                var curLine = lines[i];
                if (curLine.Equals(CertHeader, StringComparison.Ordinal))
                {
                    grouper = new StringBuilder();
                }
                else if (curLine.Equals(CertFooter, StringComparison.Ordinal))
                {
                    certList.Add(grouper.ToString());
                    grouper = null;
                }
                else
                {
                    if (grouper != null)
                    {
                        grouper.Append(curLine);
                    }
                }
            }

            List<X509Certificate2> collection = new List<X509Certificate2>();

            foreach (var certText in certList)
            {
                var cert = ParseCertificate(certText);
                collection.Add(cert);
            }

            return collection.ToArray();
        }
    }
}

You will need to change the signature of your controller to accept something that gives you access to the raw request body – such as an HttpRequestMessage.

Then you can call the following method with your request to validate per Amazon’s requirements
(Note the AlexaRequestBody is my custom request model. You just need to get the timestamp from the request)

        static Dictionary<string, X509Certificate2> _validatedCertificateChains = new Dictionary<string, X509Certificate2>();
//...
        async Task ValidateRequestSecurity(HttpRequestMessage httpRequest, byte[] requestBytes, AlexaRequestBody requestBody)
        {
            if (requestBody == null || requestBody.Request == null || requestBody.Request.Timestamp == null)
            {
                throw new InvalidOperationException("Alexa Request Invalid: Request Timestamp Missing");
            }

            var ts = requestBody.Request.Timestamp.Value;
            var tsDiff = (DateTimeOffset.UtcNow - ts).TotalSeconds;

            if (System.Math.Abs(tsDiff) >= 150)
            {
                throw new InvalidOperationException("Alexa Request Invalid: Request Timestamp outside valid range");
            }

            httpRequest.Headers.TryGetValues("SignatureCertChainUrl", out var certUrls);
            httpRequest.Headers.TryGetValues("Signature", out var signatures);

            var certChainUrl = certUrls.FirstOrDefault();
            var signature = signatures.FirstOrDefault();

            if (string.IsNullOrEmpty(certChainUrl))
            {
                throw new InvalidOperationException("Alexa Request Invalid: missing SignatureCertChainUrl header");
            }

            if (string.IsNullOrEmpty(signature))
            {
                throw new InvalidOperationException("Alexa Request Invalid: missing Signature header");
            }

            var uri = new Uri(certChainUrl);

            if (uri.Scheme.ToLower() != "https")
            {
                throw new InvalidOperationException("Alexa Request Invalid: SignatureCertChainUrl bad scheme");
            }

            if (uri.Port != 443)
            {
                throw new InvalidOperationException("Alexa Request Invalid: SignatureCertChainUrl bad port");
            }

            if (uri.Host.ToLower() != "s3.amazonaws.com")
            {
                throw new InvalidOperationException("Alexa Request Invalid: SignatureCertChainUrl bad host");
            }

            if (!uri.AbsolutePath.StartsWith("/echo.api/"))
            {
                throw new InvalidOperationException("Alexa Request Invalid: SignatureCertChainUrl bad path");
            }

            X509Certificate2 signingCertificate = null;

            if (!_validatedCertificateChains.ContainsKey(uri.ToString()))
            {
                System.Diagnostics.Trace.WriteLine("Validating cert URL: " + certChainUrl);

                var certList = await PemHelper.DownloadPemCertificatesAsync(uri.ToString());

                if (certList == null || certList.Length < 2)
                {
                    throw new InvalidOperationException("Alexa Request Invalid: SignatureCertChainUrl download failed or too few certificates");
                }

                var primaryCert = certList[0];

                var subjectAlternativeNameList = PemHelper.ParseSujectAlternativeNames(primaryCert);

                if (!subjectAlternativeNameList.Contains("echo-api.amazon.com"))
                {
                    throw new InvalidOperationException("Alexa Request Invalid: SignatureCertChainUrl certificate missing echo-api.amazon.com from Subject Alternative Names");
                }

                List<X509Certificate2> chainCerts = new List<X509Certificate2>();

                for (int i = 1; i < certList.Length; i++)
                {
                    chainCerts.Add(certList[i]);
                }

                if (!PemHelper.ValidateCertificateChain(primaryCert, chainCerts))
                {
                    throw new InvalidOperationException("Alexa Request Invalid: SignatureCertChainUrl certificate chain validation failed");
                }


                signingCertificate = primaryCert;

                lock (_validatedCertificateChains)
                {
                    if (!_validatedCertificateChains.ContainsKey(uri.ToString()))
                    {
                        System.Diagnostics.Trace.WriteLine("Adding validated cert url: " + uri.ToString());
                        _validatedCertificateChains[uri.ToString()] = primaryCert;
                    }
                    else
                    {
                        System.Diagnostics.Trace.WriteLine("Race condition hit while adding validated cert url: " + uri.ToString());
                    }
                }
            }
            else
            {
                signingCertificate = _validatedCertificateChains[uri.ToString()];
            }

            if (signingCertificate == null)
            {
                throw new InvalidOperationException("Alexa Request Invalid: SignatureCertChainUrl certificate generic failure");
            }


            var signatureBytes = Convert.FromBase64String(signature);

            var thing = signingCertificate.GetRSAPublicKey();
            if (!thing.VerifyData(requestBytes, signatureBytes, System.Security.Cryptography.HashAlgorithmName.SHA1, System.Security.Cryptography.RSASignaturePadding.Pkcs1))
            {
                throw new InvalidOperationException("Alexa Request Invalid: Signature verification failed");
            }
        }

Returning custom HTTP Status codes for WCF SOAP Exceptions

When WCF encounters an unhandled exception, the thrown exception is wrapped up in a FaultException and returned to the client. You can of course throw your own FaultException to have better control over the contents of the error, but one thing you cannot control by default is the HTTP response code. It is always 500.

I had a situation where I needed to return a 503 error under certain circumstances in a WCF SOAP service.

The solution is not as simple as I might like, but it isn’t terrible. There are examples out there for changing the http status code for all exceptions (https://msdn.microsoft.com/es-es/library/ee844556(v=vs.95).aspx), but this was not at all what we want.

The solution uses the same basic framework as the mentioned article however. An Endpoint Behavior Extension registers a Dispatch Message Inspector that watches for faults. In the case of a fault. Inside the BeforeSendReply method, you have access to the reply message. For performance reasons it is best to not unwrap the XML, so we use the SOAP Action header to trigger the HTTP Status code update.

I decided to create a simple custom exception class that sets the SOAP Action to a predefined value

    /// <summary>
    /// Custom Fault Exception that when used with the CustomFaultStatusBehavior Endpoint Behavior
    /// allows returning custom HTTP status codes to the client
    /// </summary>
    public class StatusFaultException : FaultException
    {
        /// <summary>
        /// Create new exception
        /// </summary>
        /// <param name="statusCode">HTTP Status code to be returned to the client</param>
        /// <param name="faultReason">SOAP Fault Reason</param>
        /// <param name="faultCode">SOAP Fault Code</param>
        public StatusFaultException(System.Net.HttpStatusCode statusCode, string faultReason, FaultCode faultCode)
            :base(faultReason, faultCode, "CustomFaultStatus" + ((int)statusCode).ToString())
        {
            //StatusCode is placed in the response Action. Action would be "CustomFaultStatus503" to return a 503 error code
        }
    }

Here is the Custom Behavior that consumes the SOAP Action

    /// <summary>
    /// Endpoint Behavior that allows returning custom HTTP response codes for SOAP Faults 
    /// </summary>
    public class CustomFaultStatusBehavior : BehaviorExtensionElement, IEndpointBehavior
    {
        //based on https://msdn.microsoft.com/es-es/library/ee844556(v=vs.95).aspx
        public override Type BehaviorType
        {
            get
            {
                return typeof(CustomFaultStatusBehavior);
            }
        }

        public void AddBindingParameters(ServiceEndpoint endpoint, BindingParameterCollection bindingParameters)
        {
        }

        public void ApplyClientBehavior(ServiceEndpoint endpoint, ClientRuntime clientRuntime)
        {
        }

        public void ApplyDispatchBehavior(ServiceEndpoint endpoint, EndpointDispatcher endpointDispatcher)
        {
            CustomFaultStatusMessageInspector inspector = new CustomFaultStatusMessageInspector();
            endpointDispatcher.DispatchRuntime.MessageInspectors.Add(inspector);
        }

        public void Validate(ServiceEndpoint endpoint)
        {
        }

        protected override object CreateBehavior()
        {
            return new CustomFaultStatusBehavior();
        }
    }

    /// <summary>
    /// Message Inspector that updates the HTTP response code for faulted messages with a CustomFaultStatus action
    /// </summary>
    public class CustomFaultStatusMessageInspector : IDispatchMessageInspector
    {
        public object AfterReceiveRequest(ref Message request, IClientChannel channel, InstanceContext instanceContext)
        {
            return null;
        }

        public void BeforeSendReply(ref Message reply, object correlationState)
        {
            if (!reply.IsFault) return;
            if (!reply.Headers.Action.StartsWith("CustomFaultStatus", StringComparison.Ordinal)) return;
            //get the string value for desired response code
            string statusCodeString = reply.Headers.Action.Substring(17);
            //convert to int
            int statusCodeInt;
            if (!int.TryParse(statusCodeString, out statusCodeInt)) return;

            //cast to HttpStatusCode
            System.Net.HttpStatusCode statusCode = System.Net.HttpStatusCode.InternalServerError;
            try
            {
                statusCode = (System.Net.HttpStatusCode)statusCodeInt;
            }
            catch (Exception ex)
            {
                return;
            }

            // Here the response code is changed
            reply.Properties[HttpResponseMessageProperty.Name] = new HttpResponseMessageProperty() { StatusCode = statusCode };
        }
    }

The CustomFaultStatusBehavior must be registered in your web.config as a behavior extension, then it must be referenced in an endpoint behavior. Finally this behavior should be applied to the endpoint using the behaviorConfiguration attribute.

Free Tool: Dynamics CRM User Language Update Tool

Just had an encounter with a user who needed their language updated. The UI Language was set to Korean – I wasn’t able to walk the user through updating it since I don’t read Korean either.

So I whipped up a little tool to do the job for me.
userupdatetool

You supply a connection string for the target CRM Organization (Should support everything – CRM Online, On-Premise – just paste everything in the CRM url up to (but not including) the “/main.aspx”)

You can search for the user by Guid, Full Name or DomainName (aka User Name) (all the search fields auto add wildcards)

The tool returns a list of matching users – select the user to update, select the desired values for language code and click Update. Pretty basic and there are instructions for doing it, but I didn’t see anything so simple out there.

You can get the binaries here: Dynamics CRM User Language Update Tool

And the source code here: Dynamics CRM User Language Update Tool Source Code

(Note that you may need to have Windows Identity Foundation installed – this is a prerequisite of the CRM SDK)

This tool works with Dynamics CRM 2011, Dynamics CRM 2013, Dynamics CRM 2015, Dynamics CRM Online, and the forthcoming Dynamics CRM 2016 (ie everything 2011 and later)

Dirt simple WCF Routing Service configuration tutorial with authentication

As I’ve said in previous posts, I’m a big fan of WCF in general – recently I have had the opportunity to work with a less popular, but extremely powerful feature: the WCF Routing Service (https://msdn.microsoft.com/en-us/library/ee517423(v=vs.110).aspx)

The MSDN documentation is confusing. The existing blog articles are confusing or broken. Examples of hosting it in IIS using simplified configuration are pretty much missing…

The routing service allows you to create proxy service endpoints. In my case, I needed to expose some internal web services to the internet through a VPN connection in Azure. Also, I needed to implement security on these publicly exposed endpoints.

The internal web services are hosted by a non-microsoft service bus, use HTTP with no authentication.
The external endpoints need to run over HTTPS (SSL) and require authentication.

Yes, you can do this with the Routing Service. No, it does not require a bunch of code.

The routing service is capable of using xpath matching on the message header or message itself to determine the endpoint routing. You could use the SOAP Action for example. Or… a much simpler approach is to use URL-pattern based routing.

In fact, you can do 95% of it in the web.config only. The only place you will need code is to apply authorization (what users can access the proxy), but it is very simple.

Suppose you have three SOAP endpoints you would like to proxy.
http://mailserver/sendservice
http://customermanagement/customerretrieval
http://customermanagement/customerupdate

Suppose you want to serve these up through you service as
https://proxyservice.example.org/sendmail
https://proxyservice.example.org/getcustomer
https://proxyservice.example.org/updatecustomer

You want Basic Authentication over SSL, using Windows for credentials. Finally, you want a means of limiting which authenticated users can access the proxy.

So, step by step.

1. Create a WCF Project

1.1 Setup IIS to host your project
Create a site in IIS pointing to the folder containing Service1.svc. Disable anonymous auth, enable basic auth, set it up for SSL.
(I recommend using a real ssl cert with a hosts entry pointing back at localhost. Easier than getting self signed certs working…)

2. Delete the IService1.cs; expand the Service1.svc and delete the code behind (Service1.svc.cs).

3. Rt click the Service1.svc and edit markup. Replace it with the following:

<%@ ServiceHost Language="C#" Debug="true" Service="System.ServiceModel.Routing.RoutingService,System.ServiceModel.Routing, version=4.0.0.0, Culture=neutral,PublicKeyToken=31bf3856ad364e35" %>

This will cause your Service1.svc to invoke the routing service. No code required (yet) – you do the rest in the web.config.

4. Update your web.config with the following.

<?xml version="1.0"?>
<configuration>
  <configSections>
  </configSections>
  <system.web>
    <compilation debug="true" targetFramework="4.5"/>
  </system.web>
  <system.serviceModel>
    <client>
      <!--Define named internal (private-side) endpoints here. Defined like any client endpoint except the contract doesn't need to be specified. Define binding as needed -->
      <endpoint name="ep_sendmail" address="http://mailserver/sendservice" binding="basicHttpBinding" contract="*"/>
      <endpoint name="ep_getcustomer" address="http://customermanagement/customerretrieval" binding="basicHttpBinding" contract="*"/>
      <endpoint name="ep_updatecustomer" address="http://customermanagement/customerupdate" binding="basicHttpBinding" contract="*"/>
    </client>
    <routing>
      <filters>
        <!--Define named filters that will be applied to requests coming into the (public side of) the router. (hostname is ignored - anything can be used - protocol and path/query strings must match identically)-->
        <filter name="f_sendmail" filterType="EndpointAddress" filterData="https://host/Service1.svc/SendMail"/>
        <filter name="f_getcustomer" filterType="EndpointAddress" filterData="https://host/Service1.svc/GetCustomer"/>
        <filter name="f_updatecustomer" filterType="EndpointAddress" filterData="https://host/Service1.svc/UpdateCustomer"/>
      </filters>
      <filterTables>
        <filterTable name="filterTable1">
          <!--Define the mapping between the filter match and endpoint. I'm using a 1:1:1 mapping-->
          <add filterName="f_sendmail" endpointName="ep_sendmail"/>
          <add filterName="f_getcustomer" endpointName="ep_getcustomer"/>
          <add filterName="f_updatecustomer" endpointName="ep_updatecustomer"/>
        </filterTable>
      </filterTables>
    </routing>
    <services>
      <service behaviorConfiguration="RoutingBehavior" name="System.ServiceModel.Routing.RoutingService">
        <!--Server endpoint must be defined with the appropriate contract (Most likely IRequestReplyRouter) and associated with its own binding-->
        <endpoint address="" binding="basicHttpBinding" bindingConfiguration="externalBinding" name="RouterEndpoint1" contract="System.ServiceModel.Routing.IRequestReplyRouter"/>
      </service>
    </services>
    <bindings>
      <basicHttpBinding>
        <!--For this example, the default or internal binding are just using the defaults-->
        <binding name="internalBinding"/>
        <!--Binding configuration for the router's "endpoint" - configures it to expect Basic Authentication over SSL.-->
        <binding name="externalBinding">
          <security mode="Transport">
            <transport clientCredentialType="Basic"/>
          </security>
        </binding>
      </basicHttpBinding>
    </bindings>
    <behaviors>
      <serviceBehaviors>
        <behavior>
          <!--These behaviors are likely unused-->
          <serviceMetadata httpGetEnabled="true"/>
          <serviceDebug includeExceptionDetailInFaults="true"/>
        </behavior>
        <behavior name="RoutingBehavior">
          <!--The behaviors applied specifically to the routing service - must specify the filtertable name -->
          <routing routeOnHeadersOnly="true" filterTableName="filterTable1"/>
          <serviceDebug includeExceptionDetailInFaults="true"/>
          <!--The router's provided metadata is pretty much useless - it is the IRequestReply contract-->
          <serviceMetadata httpGetEnabled="false"/>
          <!--This tells the router to use Windows for establishing identities, and to use our custom class for determining permission-->
          <serviceAuthorization principalPermissionMode="UseWindowsGroups" serviceAuthorizationManagerType="ExampleNamespace.ExampleAuthorizationManager, ExampleAssemblyName"/>
        </behavior>
      </serviceBehaviors>
    </behaviors>
    <!--Very important - there is a bug of sorts in the router when using basicHttpBinding with asp compatibility. Just disable it.-->
    <serviceHostingEnvironment aspNetCompatibilityEnabled="false" multipleSiteBindingsEnabled="true"/>
  </system.serviceModel>
</configuration>

If you comment out the serviceAuthorization tag, your project should build and run – so long as you have ssl and basic auth working…

You should update the “ExampleNamespace.ExampleAuthorizationManager, ExampleAssemblyName” with the Namespace, class name and assembly name that you actually use
(often the namespace and assembly name are the same)

If you simply want a router – with no auth and no ssl remove the enternalBinding’s security node AND the serviceAuthorization node

5. Implement the ExampleAuthorizationManager

namespace ExampleNamespace
{
    public class ExampleAuthorizationManager : ServiceAuthorizationManager
    {
        protected override bool CheckAccessCore(OperationContext operationContext)
        {
            //check that the user is allowed and return true to allow, false to deny
            return true;
            //return base.CheckAccessCore(operationContext);
        }
    }
}

6. Update clients

Since there is no WSDL, you will need build your clients against the internal endpoints’ WSDLs, then update them to use the external endpoint. Additionally you will need to update the binding configuration to use Transport security with Basic Authentication (unless you have turned this off).

You now have a secure proxy for WCF services without having to reimplement the services in WCF – adding new services requires 3 lines of configuration. I’m not sure if Microsoft could have made this any easier… except perhaps by documenting it better 😛

Get the SQL Server and database name from Dynamics CRM

As far as I can tell, there is no way to retrieve the SQL Server name using the Organization Service. If you have the Organization Service URL, there really is no good way to get the database connection string.

(You can use the deployment service to get it, but that only works if you happen to be a deployment administrator)

No good way… but there is a way. When you download a dynamic spreadsheet, the database connection string is embedded in the xml. And you can programmatically download that spreadsheet using an entity that all CRM orgs have.

Using some fiddler experimentation, I was able to make a .NET method that will extract the database connection string, given the organization base url (https://crmserver/orgnam)

        /// <summary>
        /// Given a Dynamics CRM Org URL, retrieve the Database Connection string
        /// </summary>
        /// <param name="crmOrgUrlBase"></param>
        /// <returns></returns>
        private static string GetCrmDatabaseConnectionString(string crmOrgUrlBase)
        {
            string cleanurl = crmOrgUrlBase.ToLowerInvariant().Trim().Replace("/xrmservices/2011/organization.svc", "");
            {
                int mainPos = cleanurl.IndexOf("/main.aspx");
                if (mainPos > 0)
                {
                    cleanurl = cleanurl.Substring(0, mainPos);
                }
            }

            string requestPayload = @"xdpi=96&exportType=list&useSqlQuery=1&fetchXml=%3Cfetch+distinct%3D%22false%22+no-lock%3D%22false%22+mapping%3D%22logical%22+page%3D%221%22+count%3D%2250%22+returntotalrecordcount%3D%22true%22%3E%3Centity+name%3D%22systemuser%22%3E%3Cattribute+name%3D%22systemuserid%22%2F%3E%3Cattribute+name%3D%22fullname%22%2F%3E%3Cattribute+name%3D%22fullname%22%2F%3E%3Corder+attribute%3D%22fullname%22+descending%3D%22false%22%2F%3E%3C%2Fentity%3E%3C%2Ffetch%3E%0D%0A&layoutXml=%3Cgrid+name%3D%22excelGrid%22+select%3D%220%22+icon%3D%220%22+preview%3D%220%22%3E%3Crow+name%3D%22result%22+id%3D%22systemuserid%22%3E%3Ccell+name%3D%22fullname%22+width%3D%22100%22%2F%3E%3C%2Frow%3E%3C%2Fgrid%3E%0D%0A";
            string url = cleanurl + "/_grid/print/export_live.aspx";

            string response = null;


            using (var wc = new System.Net.WebClient())
            {
                wc.UseDefaultCredentials = true;
                wc.Headers.Add("Accept-Encoding", "gzip, deflate");
                wc.Headers.Add("Content-Type", "application/x-www-form-urlencoded");
                wc.Headers.Add("DNT", "1");
                response = wc.UploadString(url, requestPayload);
            }


            var xe = System.Xml.Linq.XElement.Parse(response);


            System.Xml.Linq.XNamespace nn = "urn:schemas-microsoft-com:office:spreadsheet";
            System.Xml.Linq.XNamespace nn2 = "urn:schemas-microsoft-com:office:excel";


            var connectionEl = xe.Element(nn + "Worksheet").Element(nn2 + "QueryTable").Element(nn2 + "QuerySource").Element(nn2 + "Connection");

            string rawString = connectionEl.Value;

            Regex rex = new Regex("SERVER=([^;]+);DATABASE=([^;]+)", RegexOptions.Compiled);

            var m = rex.Match(rawString);

            string server = m.Groups[1].Value;
            string database = m.Groups[2].Value;

            return "DATA SOURCE=" + server +";INITIAL CATALOG=" + database + ";Integrated Security=SSPI";
        }

It is technically unsupported to access export_live.aspx directly, so this may stop working in future releases of CRM.
I have tested this on CRM 2011 UR 11 and UR 17.

Dirt simple method of limiting System.Threading.Task concurrency (max concurrent threads)

Microsoft has an article about creating a Task Scheduler that limits maximum concurrency:
How to: Create a Task Scheduler That Limits the Degree of Concurrency

The provided example class is clunky and kinda hard to grasp. I’ve been searching for a simple way to create a “thread pool” with limited concurrency. I previously experimented with ConcurrentBags of BackgroundWorkers – this worked, but it was also fairy complex and cumbersome.

The solution I came up with uses a Semaphore (or rather the lighter weight SemaphoreSlim type) to manage concurrency.

Simply put – you create a semaphore with a maxCount (and initialCount) equal to the max concurrency you desire. Then when you want to fire off a Task, first call semaphore.Wait(), then call semaphore.Relase() in a ContinueWith().

Contrived example:

    public class LimitedAsync
    {
        private SemaphoreSlim _semaphore;

        public LimitedAsync(int maxConcurrency)
        {
            // Create semaphore with maxConcurrency slots
            _semaphore = new SemaphoreSlim(maxConcurrency, maxConcurrency);
        }

        public void DoSomethingAsync(string param)
        {
            //Wait for semaphore to have availablilty (blocks if semaphore is full)
            _semaphore.Wait();
            //Run DoSomething in a task, then release slot in semaphore
            //ContinueWith is called even if DoSomething faults
            Task.Factory.StartNew(() => DoSomething(param)).ContinueWith((x) => _semaphore.Release());
        }

        public void DoSomething(string param)
        {
            System.Threading.Thread.Sleep(500);
        }
    }

Generating a single flattened WSDL from an existing WCF Service

UPDATE 4/12/2013: Tool included
Download WSDL Flattener Tool
I’m not including source because it’s messy (I prefer to cleanup my code before sharing…) but feel free to decompile with ILSpy if you don’t trust me.

UPDATE 8/11/2015: Command line tool included
WSDL Flattener Command Line Utility
Somehow I forgot to save my update – thanks Luke for pointing it out!

WCF is great – its flexible, standards compliant, super easy to work with, and just altogether nice. That is, until you try to use it with, say, Oracle products. WCF generates so-called modular wsdl files, with separate xsd files for types, and even sometimes multiple wsdl files. Not technically in violation of any standards, but kind-of pushing the limits.

There are plenty of articles out there about modifying a WCF service to generate a flat WSDL. Also if you upgrade to .NET 4.5, you can use the URL parameter ?singleWsdl to get a flattened WSDL.

But what if you don’t have control of the web service? What if you cannot upgrade or modify the code?

For some odd reason, Microsoft added the internal ability to generate a single WSDL but only exposed it through the WCF Service implementation. They didn’t add it to svcutil.exe, and they didn’t expose it in the Framework… publicly that is *evil grin*

Turns out, in the System.ServiceModel.Description namespace, there is an internal static class called “WsdlHelper”, and this class has a public static Method called “GetSingleWsdl” that takes a MetadataSet for a parameter, and returns a System.Web.Services.Description.ServiceDescription object representing the Wsdl as a single entity. All you have to do is call .Write(filename) and you have your file.

“But I don’t see any WsdlHelper class!!!!!!”

It’s internal – you have to use reflection to invoke it.
First, you have to load the metadata from the existing WCF service into a MetadataSet using a MetadataExchangeClient. (Hint: you will probably need to use a custom binding). Then you run that set into something like this:

            var asy = System.Reflection.Assembly.GetAssembly(typeof(WsdlExporter));
            Type t = asy.GetType("System.ServiceModel.Description.WsdlHelper", true);
            var method = t.GetMethod("GetSingleWsdl", System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Static);

            object retVal = method.Invoke(null, new object[] { metadataSet });
            //cast retVal into System.Web.Services.Description.ServiceDescription, and call the .Write() method. All done

Using this, I was able to create a tool that takes a WCF url, and allows you to save a single .wsdl file. You want the tool? Later perhaps. For now put your thinking cap and Google gloves on, and write your own.

CRM 2011 Runtime calculated fields –retrieval behavior notes

In previous versions of CRM, creating realtime calculated fields involved a plugin that listened to the global Execute message (and optionally RetrieveMultiple if you wanted to handle filter criteria) – to handle FetchXml requests. The remaining messages (Retrieve, RetrieveMultiple, Update, Create) can be registered for the specific Entity, so you are not gumming up CRM with global plugins.

Some of the neat new features of CRM 2011 greatly complicate the process of creating a calculated field – your Plugin now must be registered globally for Retrieve, RetrieveMultiple and Execute; and there are three different query types to handle.

These are my research notes from building a plugin that allows for functionality similar to the “Customer” lookup in the built-in entities. It would be easy enough to sit on the Post-Stage event, look through the Result and use a retrieve for each record to get the calculated value. This would quickly degrade system performance – instead my solution modifies the query in the Pre-stage to include all necessary columns, then uses the data in the result to update the calculated field. It requires significantly more code, but is far more scalable.

1. Execute Message

I haven’t yet determined all the cases that Execute is called, but it is for sure called when the legacy webservice is used to perform an ExecuteFetch (such as when using Stunnware tools). It is also fired now and again by the UI (but not for normal grids – almost everything uses RetrieveMultiple now)

In the Pre-Stage, you must look for the “FetchXml” value in the InputParameters. If it is there, use your Xml parser / modifier of choice to look through it for any instances of your entity, then for each entity instance, check for your calculated field (these can be nested arbitrarily deep). If it contains the field, add your required source columns to Xml, and put the FetchXml back into the InputParameters.

The ResultXml on the Post-Stage is flat, and columns from Linked Entities are given a prefix. The prefix comes from the link-entity – it is the alias if one is specified, or the “to” attribute. (Caveat: If aliases are not used in a fetch, and there are multiple link-entities off attributes with the same name, the result will have duplicate column names. The UI always adds aliases.)

2. RetrieveMultiple Message

RetrieveMultiple is powered by a Query, and that query can be one of QueryExpression, QueryByAttribute, or FetchExpression. The query is in the InputParameter[“Query”], and is of type QueryBase.

QueryExpression

In the pre-stage, check that query.EntityName is the correct entity; check query.ColumnSet for your calculated field, adding any required fields to the ColumnSet.
Next, recurse through the LinkEntities, checking the LinkToEntityName and the Columns for each.

In the post-stage, the result is in OutputParameters[“BusinessEntityCollection”] which is of type EntityCollection. Each record is an Entity, and all fields from linked entities are in the Attributes collection (similar to Fetch), prefixed for links. The prefix is either the alias of the LinkEntity, or if the alias is blank it is the entityname followed by a sequence number (eg “contact1.fullname”, “contact1.firstname” “account2.name”. The sequence number is assigned to each LinkEntity based on the order it occurs in the LinkEntity Tree.

(Note: to be complete, something similar should be done for the Orders, so that you can sort by your calculated column)

  • Account (no prefix)
    • Contact (contact1.<attribute>)
    • Contact [alias shipper] (shipper.<attribute>)
    • User (systemuser3.<attribute>)
      • BusinessUnit (businessunit4.<attribute>)

This is better and worse than the old fetch method. It guarantees that prefixes are unique, but it requires you to traverse the entire LinkEntity tree in order to know what the resulting column name will be.

In your result, you simply do Entity[“calculatedFieldName”] = Entity[“sourceFieldName”].

 

QueryByAttribute

The simplest of all – there is no nesting, no links, no related fields. You only have to check the ColumnSet (and optionally Orders).

FetchExpression

All the same complexity as the QueryExpression, except you have to process FetchXml in the Pre-Stage. The Result works the same as with QueryExpression.

 

3. Retrieve Message

This is by far the most complicated message thanks to the RelatedEntitiesQuery property of the RetrieveRequest. I haven’t determined if / when this is used by the UI. In most cases it will be null, and you only need to worry about InputParameters[“ColumnSet”]. If InputParameters[“RelatedEntitiesQuery”] is populated, it is a keyed value collection keyed on a “Relationship” – the root of a related entity query must be part of an existing relationship. However, each query in the bag can have LinkEntities – so essentially the RelatedEntitiesQuery is a collection of multiple RetrieveMultiples attached to the record. It is a mess.

In the output, the returned Entity contains a field called RelatedEntities – this is a collection of entities again keyed on the relationship. Each of the relationship queries must be handled separately as the column aliases are specific to the query.

 

—-

I’ve encountered several discussion and blog postings about this topic, and they are generally vague or incomplete. Suffice it to say handling all ways in which a field can be “hit” in CRM 2011 is daunting.

CRM 4.0: Programmatically Upload a Mail Merge Template

image

 

The Mail Merge Facility in Microsoft Dynamics CRM 4.0 works fairly well in most cases. It is at the same time a bit to complicated for casual users, and a bit too simplistic for power users, but at least in our environment it has been tweakable enough to be useful.

Like everything in CRM the Mail Merge Template is an Entity, and thankfully Microsoft permits it’s customization (without unsupported tweaking).

image

You can add custom attributes and relationships; you can customize the form and the views; you can trigger workflows when attributes change (although you cannot register a plugin against it without unsupported tweaking). The tricky fields – body, filename, documentformat – they are all read/write through web services (http://msdn.microsoft.com/en-us/library/dd903944.aspx). Meaning you can programmatically upload new templates… but how?

 

After Google failed me, I initially chose the reverse engineer approach. The contents of the body field looked like Base64 (ended with equals signs), and indeed was. I create a tool to download the existing templated, modify them slightly, then add them back.  I had a working solution, but as we approached golive I got cold feet about messing things up in production- what if I’m doing it wrong and something breaks?

A bit of digging with reflector led to the solution: internally, the attachment on a mail merge template is treated the same as the annotation entity, and Microsoft has an article explaining the correct way to upload an attachment here : Upload an Attachment

The paraphrased money line: “updateTemplate.body = System.Convert.ToBase64String(byteData)”;

Microsoft .NET Framework 4.5 final is available; avoid the RC

For some reason searching for .Net 4.5 on  Google brings up the link to the .NET 4.5 Release Candidate download, when the final is available.

You can grab it here: http://www.microsoft.com/en-us/download/details.aspx?id=30653

(I seem to recall this happening back when .NET 4 was released – you would think MS would put a big note that the Final is out, but instead I found it in the “What other’s are downloading” below.

While you’re at it, check out the new features: http://msdn.microsoft.com/en-us/library/ms171868.aspx

Probably my favorite new functionality is the async / await pattern for asynchronous coding. It is so ridiculously much better than the delegate / callback pattern you have NO idea. Seriously, we’re talking an order of magnitude reduction in code complexity.