The Danger of SQL Operations in Microsoft Dynamics CRM

Roshan Mehta, 26 June 2017

You may have heard that performing SQL operations on your Microsoft Dynamics CRM databases are dangerous or unsupported. But what does that mean? What is so dangerous about it? Why is it unsupported?

To clear the air, I’m talking specifically about anything that will alter data in your system and I’m also talking about on-premise deployments since you cannot access the database if you’re in the cloud.

Running SELECT queries on base tables is fine because you’re just consuming data, but the INSERT, UPDATE and DELETE statements are dangerous. I won’t be able to cover every single scenario in this article, but I can provide some examples where seemingly innocent data updates can cause chaos.

Firstly, why are some integrations built to create or update records in CRM using SQL? The short answer is performance. Direct SQL inserts or updates are a lot faster than going through the CRM SDK, but performance is less important than data accuracy and a stable system.

I have seen integrations that create and update Accounts and Contacts using SQL. The integrations only set the core data on these entities, such as names, phone numbers, email addresses, and physical and postal addresses. What are the impacts?

System fields don’t get set

System fields such as Created By, Created On, Modified By, Modified On don’t get set. This is difficult to maintain if you altering data with SQL. It also messes up data analytics (Advanced Find, Reports) if you want to run queries against these fields.

Nothing gets audited

If you’re using Auditing in CRM, no Audit records will get created.

Customer Address records don’t get created

This is an interesting one. Addresses for Accounts and Contacts are stored in a separate table in the database called customeraddress. Creating an Account or Contact using SQL means that the customeraddress record will be missing. If the user manually adds addresses to Accounts or Contacts on the form and clicks “Save”, the address data will disappear because there is no customeraddress record to hold the information.

Email Matching doesn’t work

Another interesting one. CRM contains a table in the database called EmailSearchBase. This table is responsible for matching email addresses for incoming emails to email-enabled entities in CRM. If you have email-enabled records with email addresses in CRM that have been created or updated using SQL, the EmailSearchBase table will be empty and incoming emails won’t be matched to the right records.

Again, there are many other scenarios where SQL inserts and updates are a bad idea. My advice to you is to avoid it. The CRM SDK exists for a reason and ensures that platform business logic is executed to provide accurate data throughout the system.

If you have concerns that your data may have been altered with SQL or you have experienced some of the problems I’ve listed above, leave a comment below.