← Back to Blog

Using Azure SQL Connector to Integrate Dynamics CRM 365

Use Azure's Microsoft SQL Connector to integrate your line of business applications to Dynamics CRM 365

We have a task and time management system we built a few years back which is deployed in Azure.  We developed the application with the goal that it would keep us on top of priority work and help us queue up future work and it is great at that, but being a home grown line of business app (lob) it lacks some features like a good comprehensive search of completed tasks.

Side note to the business audience, here is the value of this:

  • No infrastructure investment, pay for what you use
  • Reduces cost and complexity of integrating two systems
  • Design surface serves as the process documentation fostering rapid development while maintaining standards
  • Collaborative nature of Azure allows Customers and Partners transparency into processes that act on or originate data
  • Process runs on a schedule, has great visibility into any errors that occur

Since Dynamics CRM (I mean 365) is great at search, and because I needed to write this blog post (it’s my turn), I decided to write an Azure logic app that will integrate the data from our app’s Azure SQL database into CRM.

To get this working, I needed to setup a Microsoft SQL Connector (in azure click Add and search for Microsoft SQL Connector) that would retrieve the Incomplete Tasks from our app database (for this purpose I only cared about getting Tasks that are incomplete into CRM).  Microsoft has a good article on setting up the connector (https://azure.microsoft.com/en-us/documentation/articles/app-service-logic-connector-sql/)

The SQL Connector is still in preview as of 2016-10-11 so it has some issues. For example, after creating it the first time none of the connection information saved so I had to update the connector again to get it working.  This is where I defined the SQL statement to retrieve the tasks from the lob app database…this is pretty well documented in the link above.

The non-intuitive part of the SQL Connector configuration is that the SQL Connector itself is really the top-level component which exposes a Web API (APIAPP) and has minimal configuration while the Host it sits on has all of the application settings such as the SQL Connection string and SQL Commands.  Following the image below, if you need to configure the Web API settings you would use the Settings command button pictured at the top while if you needed to modify the SQL Connection or SQL Commands then you would follow the link in the Host and then drill into that blade’s application settings (blade is the azure reference to a form).

Azure SQL Connector Settings Blade

The Host needs CORS allowed (* works for this), the Auth App Service Auth turned On and the non-auth action set to allow request no action (you can assume these are for testing purposes and the API can be secured later). Also, while in the Host settings, go to properties and copy the URL as you will need this later.  The Web API (APPAPI) needs the Access Level set to Public (under App Settings).

Now, you can Add a Logic App to Azure to consume the Web API or the SQL Data which is covered in our post titled Using Azure Logic Apps to Integrate Dynamics CRM 365