Dynamics CRM with SQL Server Integration: How to Sync Data

Say finance wants last quarter's renewals by region. Those figures are in Dynamics, of course, and nobody in finance has a Dynamics login. Cue the request to start moving data from Dynamics CRM to SQL Server, because that's where the analysts, the BI crowd and every nightly report job already look. The catch is that Dynamics 365 CRM keeps everything in Microsoft Dataverse, a cloud data store you don't run the way you run your own database.

Getting data out of Dataverse is the first job, and every so often you'll want to push some of it back. No theory lectures here, promise. Five routes follow: SSIS, an ODBC driver, Power Automate, the Dynamics 365 API and Skyvia. Each comes with who usually picks it, what setting it up is like, and the bill (paid in skills, in sync direction, or in actual dollars).

Want the cheat sheet version first? A one-way feed into a reporting database is replication territory, with incremental updates, and edits traveling both ways belong to two-way synchronization. And when someone just wants to fire off a SELECT against live CRM tables, Microsoft's read-only Dataverse SQL endpoint might be all they need (more on that in the section on querying Dataverse with SQL).

What Is Dynamics CRM?

Dynamics CRM, also known as Dynamics 365 CRM, is a sophisticated, compound solution that comprises diverse modules for customer relationship management. This tool is often used by enterprise-level organizations to help them manage client-related interactions and align those with sales, marketing, and project automation activities.

Dynamics CRM

What Is SQL Server?

Microsoft SQL Server is a relational database management system (RDBMS). Similar to other RDBMS systems, this solution uses Structured Query Language (SQL) that enables smooth interaction with relational databases. Since SQL Server has been on the market for 20+ years, there are multiple editions and versions of this system.

SQL Server

Dynamics CRM and SQL Server Integration Options

In this article, we discuss five principal methods to connect Dynamics CRM and SQL Server together:

  1. SQL Server Integration Services (SSIS)
  2. ODBC (Open Database Connectivity) Driver
  3. Power Automate (Flow)
  4. Third-Party Integration Tools: Skyvia
  5. Dynamics 365 API

We have prepared a table to introduce each approach and its principal characteristics briefly. All these methods differ in difficulty and in the use of instruments for integration implementation. 

Evaluation criteriaDirect APISSISODBC DriverPower AutomateSkyvia
Installation and SetupInvolves developers and IT specialists for configuration and setup.Requires basic programming and technical knowledge.Requires some time to install and configure the driver, though programming knowledge isn’t needed.Can be installed as a desktop app or can be accessed via a web browser as a SaaS app.Can be used by both technical and non-technical audiences with no coding experience.
Key benefits– High level of customization
– Cross-platform and multi-language support
– Bi-directional integration
– GUI and no coding
-GUI with no coding
– Advanced security, reporting, and analysis
– Integration monitoring
– Optimizing business workflow
– Human error elimination
– Pre-defined templates
– Web access- Ample list of integration options
– 200+ connectors- GUI and no coding
Limitations– Deep technical knowledge required
– Translation to SQL needed
– Oriented on the Microsoft ecosystem
–  Limitations associated with on-premises systems
– SQL knowledge is required– Limitations on API calls and items for connectors
– Slow performance
– Limited information on run history
– No phone support- Daily data refreshes on a freemium plan
CostDepends on the development and implementation costs.SSIS comes with a SQL Server license; the Dynamics 365 component for SSIS is a paid third-party add-on.From $209.95 a year (Desktop subscription) or $419.95 for a perpetual Desktop license.Needs a Power Automate Premium license: the SQL Server and Microsoft Dataverse connectors are both premium.Paid plans start from $79. There’s also a freemium plan.

SQL Server Integration Services (SSIS)

When thinking of how Dynamics CRM connects to the SQL Server, native integration comes to mind first. Both tools are produced by the same corporation – Microsoft.

SQL Server Integration Services (SSIS) is one of the Microsoft tools that could be a good option for connecting Dynamics CRM and SQL Server. With SSIS, you can design ETL pipelines for data transfer and transformation.

Best for

This Dynamics CRM integration with SQL Server using SSIS suits companies with multiple Microsoft tools and appliances in their data infrastructures.

Prerequisites

  1. Download Microsoft Visual Studio from the Microsoft Store. Make sure that the Data storage and processing toolset option is selected upon installation.
Microsoft Visual Studio
  1. In Visual Studio, download the SQL Server Integration Services Projects extension.
  2. Install a Dynamics 365 component for SSIS. SSIS has no built-in Dynamics CRM connector: the Dynamics CRM connection manager and the CRM destination used below come from third-party toolkits, such as the KingswaySoft SSIS Integration Toolkit.
Microsoft Visual Studio

Step-by-step instructions for integration using SSIS

  1. Open Visual Studio and click Create a New Project.
  2. Select the Integration Services Project type and click Next.
SSIS new project
  1. Configure the project settings and click Create.
  2. Select the Data Flow task.
SSIS data flow
  1. Click Source Assistant and select SQL Server. Apply other configuration settings if needed.
SSIS source assistant
  1. From the left panel of the project, under Sources, select SQL Server and drag it to the central task area.
  2. In the Solution Explorer panel on the right of the main project window, right-click on the Connection Manager and select New Connection.
  3. Select Dynamics CRM and click Add.
  4. Click Test Connection.
  5. From the left panel of the project, under Destinations, select Microsoft CRM and drag it to the central task area.
  6. Connect source and destination blocks as follows. If there’s a need to transform data, select them from the SSIS Toolbox panel and put them between the source and destination.
SSIS Toolbox
  1. In the Solution Explorer panel, find the task and right-click on it. Select Execute Package.
Solution expert

A similar procedure can be done reversely – with Dynamics CRM as a source and SQL Server as a destination.

Pros

  • There is a possibility of executing integration in both directions: from SQL Server to Dynamics CRM and vice versa.
  • A user-friendly graphical interface that doesn’t require any coding.

Cons

  • Suitable only for tools within the Microsoft ecosystem.
  • Limitations associated with integration include systems deployed on-premises.

ODBC (Open Database Connectivity) Driver

Another way to connect both instances is to use the Devart ODBC Driver for Dynamics 365. It allows the creation of a linked server in SQL Server Management Studio that provides permission to execute SQL commands and read and write Dynamics 365 data: the driver translates INSERT, UPDATE and DELETE statements into Dynamics 365 API calls.

Best For

This method would be suitable for IT specialists who interact with databases on a daily basis and are proficient in SQL.

Prerequisites

Step-by-step instructions for integration with the ODBC driver

To create a linked server for Dynamics 365 in SQL Server Management Studio, proceed with the following steps:

  1. Launch SSMS and select the required SQL Server instance.
  2. In Object Explorer, expand the Server Objects section. Then, right-click on Linked Server and select New Linked Server.
Objest Explorer
  1. Configure a newly created linked server:
    • In the Linked server field, type the name of the server.
    • Select Other data source from the Server type drop-down list.
    • Select Microsoft OLE DB Provider for ODBC drivers from the Provider drop-down list.
    • In the Data source field, type the DSN name (e.g., Devart ODBC Driver for Dynamics 365). Alternatively, it’s possible to type the ODBC Driver connection string in the Provider field.

The linked server appears under Linked Servers in Object Explorer. Executing distributed queries and accessing Dynamics 365 databases via the SQL Server is now possible.

Pros

  • An intuitive GUI for managing and interacting with SQL Server databases and objects.
  • Advanced security, reporting, and analysis services.
  • Integration monitoring.

Cons

  • Extensive knowledge of SQL language is required.
  • Available only on Windows.

Power Automate (Flow)

Power Automate is another Microsoft-based product of the new generation. It extensively uses AI to optimize your business processes and automate repetitive tasks.

This tool primarily works with other cloud-based sources. However, it also supports desktop-based applications and systems, such as SQL Server.

Power Automate Flow

Best for

Power Automate mostly includes connectors for cloud-based tools, so it would be suitable for companies that heavily implement SaaS applications within their data infrastructures. This solution also supports a number of on-premises tools, including SQL Server.

Prerequisites

Create an account on the official Power Automate platform and explore the list of supported browsers if you plan to use this tool only for cloud flows.

In case you need to create a desktop flow, download Power Automate from the Microsoft Store or download the .msi file and perform installation based on it.

Step-by-step guide

In this example, we show how to create a cloud flow using the web-based version of Power Automate. To start the SQL Server to Dynamics CRM integration using this tool, follow these steps:

  1. On the Power Automate (Flow) main page, log in with your Microsoft credentials.
  2. Click Create on the left panel and select the type of flow that interests you. In our case, we use the Scheduled cloud flow option.
Creare flow
  1. Specify the scheduling options for automated data extraction. You can skip this step for now and configure it later. Click Create.
Cloud Flow name
  1. Click + and select the Microsoft Dataverse action you need from the list (for example, List rows).
  2. Sign in to the Dataverse connector with the account of your Dynamics 365 environment.
get record
  1. Click + and select the desired action for SQL Server.
  2. Establish a connection with your SQL Server database.
create a new connection
  1. Click Save to save the flow.
Data flow
  1. Go to the list of your flows and start the one that corresponds to the SQL Server to Dynamics CRM integration.

Pros

  • Automation of routine processes and repetitive manual tasks.
  • Elimination of occasional human errors.
  • Pre-defined templates for data flows.

Cons

  • Even though Power Automate has many connectors, most of them offer a small number of items that can be retrieved.
  • A limited number of API calls for most connectors.
  • Slow performance, especially for large flows.
  • Limited information on the integration run history.

Third-Party Integration Tools

To overcome the limitations of all the above-mentioned approaches and decrease the degree of difficulty, we’d like to introduce a third-party data integration solution. Skyvia is a cloud-based platform that offers several ways to integrate Dynamics and SQL Server data.

Best for

Skyvia is seen as a reliable integration platform for any kind of business. In fact, it’s a multifaceted solution that can be used in various use cases.

When it comes to SQL Server Dynamics CRM integration, Skyvia offers several tools for smooth data exchange between the systems.

In this section, we mainly focus on:

However, this platform can also be suitable for complex data flows involving several sources and complex transformations with the Data Flow component.

Prerequisites

Before going through the details of integration scenarios, take some preparatory actions:

  1. Open Skyvia and log in to the system or sign up to create an account.
  2. Connect to Microsoft Dynamics from Skyvia – see detailed step-by-step instructions.
Dynamics connection by Skyvia
  1. Connect to the SQL Server from Skyvia – see detailed step-by-step instructions.
SQL Server connection by Skyvia

NOTE: Select the Agent connection mode for SQL Server to establish a secure channel. Make sure to download the Skyvia Agent, install it, and run it as Administrator beforehand.

Replicate Data (ELT)

Skyvia’s Replication scenario helps users copy data from one source to another. Here, we’ll explore the case of data replication from Microsoft Dynamics 365 to SQL Server. Note that there’s no need to create tables in SQL Server, as Skyvia will do that automatically. Moreover, the replication scenario doesn’t allow any data transformation to be performed.

  1. Click +NEW in the top menu and select Replication in the Integration column.
Skyvia Replication
  1. Select Dynamics connection as a source and SQL Server as a target.
Connections
  1. Select the objects to replicate.
select objects
  1. Select Incremental Updates under Options. That way, Skyvia copies all data only when the replication procedure is executed for the first time. During subsequent replication procedures, the system checks which Dynamics data was updated and then will apply these changes to the SQL Server tables.
Options
  1. To run the task on a regular basis, click Schedule to define parameters for its automatic execution.
schedule
  1. Click Create.
Create scenario

Import Data (ETL)

Skyvia’s Import is a flexible ETL tool for data migration between different platforms. It permits data to be loaded from CSV files, cloud apps, or relational databases to other cloud apps or relational databases.

In contrast to Replication, the Import integration offers data transformation and mapping capabilities along with other advanced features for working with data.

With Skyvia, you can easily configure data migration between Microsoft Dynamics CRM and SQL Server in any direction. Here, we mainly review how to load data from RDBMS to CRM. This case allows complementing the client data with financial or contact details, for instance, which greatly simplifies the work of sales teams.

  1. Click +Create New in the top menu and select Import in the Integration column.
  2. Under the Source type section, select Database or cloud app and find SQL Server in the Connection drop-down list.
  3. Under the Target section, select Microsoft Dynamics CRM from the Connection drop-down list.
Import by Skyvia
  1. Click Add new in the Tasks section on the right.
add new task
  1. In the task editor, it’s possible to define filters, select data import operations (INSERT, UPSERT, UPDATE, or DELETE), and define mapping types (Column, Expression, Lookup, Constant, Relation, External ID, etc.).
  2. After all configuration settings are ready, click Save.
  3. Click Create.
create scenario
  1. To run the task on a regular basis, click Schedule to define parameters for its automatic execution.
schedule

Note that it’s also possible to transfer data in the opposite direction. Moreover, after uploading/transferring data with the help of Skyvia to SQL Server, users can then load the prepared data into BI/AI, etc., tools.

Synchronize Data

Skyvia’s Synchronization component aims to sync data between cloud applications (Microsoft Dynamics 365) and relational databases (SQL Server) in both directions. It allows synchronizing data even of a different structure, preserving all data relations and providing powerful mapping settings configuration.

​​When the synchronization task runs for the first time, the system doesn’t check whether the records in data sources are identical, but simply copies data from one side to another. This may cause duplicate records in the destination system, so it’s crucial to ensure it has no records yet. During subsequent sync operations, if a record is modified or deleted in one source, the system modifies or deletes the data mapped to this record in another source, and vice versa.

  1. Click +NEW in the top menu and select Synchronization in the Integration column.
  2. Select the Dynamics connection as a source and SQL Server as a target.
  3. Click Add new in the Tasks section on the right. In the task editor, select an object from the Source drop-down list and the corresponding data field on the target side.
add new task
  1. Define data mapping settings in the Column Definition tab for both directions and save the task.
task editor
  1. Click Schedule to define the parameters for its automatic execution.
  2. Click Create.

To start synching right away, click Run and go to the Monitor tab to check the progress.

Pros

  • Web-based access with no extra downloads and desktop installations required.
  • Support of 200+ data sources, including SaaS apps, flat files, databases, storage systems, and data warehouses.
  • An ample set of integration scenarios and data processing capabilities.
  • Possibility to start with a freemium plan with all basic features included.

Cons

  • No phone support is available.
  • The maximum data update frequency on a freemium plan is once a day.

Dynamics 365 API

Dynamics 365 CRM stores its data in Microsoft Dataverse, and Microsoft provides the Dataverse Web API for connecting it with other applications and services. It is a standard REST API built on OData v4, so any language that can send HTTP requests can work with it. For .NET there is also the Dataverse SDK.

Best for

Integration via API is suitable for seasoned developers with multi-year experience in coding and with a solid technical background. Such professionals are capable of creating complex solutions to connect Dynamics CRM and SQL Server. Even though this approach is rather demanding, it offers unlimited customization, so it will be suitable for companies that need specific settings in their CRM system.

Prerequisites

Before starting integration with the method, register an application in Microsoft Entra ID and give it access to your Dynamics 365 environment, so your code can authenticate against the Dataverse Web API.

Step-by-step guide

One possible solution to connecting Microsoft Dynamics 365 CRM with SQL Server is to write an application that reads records through the Dataverse Web API (endpoints under /api/data/v9.2/) and writes them to SQL Server, or the other way around.

Pros

Here are the main benefits of using APIs:

  • High level of customization
  • Cross-platform and multi-language support

Cons

  • Deep technical knowledge is required.
  • An additional step is needed to translate API calls to SQL.

Is There a Way to Query Dynamics 365 CRM Data Straight From SQL?

Sort of. Dynamics 365 tables sit in Microsoft Dataverse, and that isn't a SQL Server box you control. Microsoft has built three SQL-flavored doors into Dataverse anyway (none of which writes a single row into your own SQL Server).

OptionWhat you getLimits to know
Dataverse SQL endpoint (TDS)Read-only SQL queries against live Dataverse tables from SQL Server Management Studio 18.12.1 or later, or from Power BINo INSERT or UPDATE. Microsoft Entra ID sign-in only. Ports 1433 or 5558 must be open. A 5-minute timeout, cut to 2 minutes for SELECT *, joins and nested queries
Azure Synapse Link for DataverseContinuous export of standard and custom tables, deletes included, to Azure Synapse Analytics or Azure Data Lake Storage Gen2You provide the Azure storage or Synapse workspace. The target isn't SQL Server
Link to Microsoft FabricA Fabric lakehouse and SQL endpoint over your Dataverse tables in OneLake, refreshed as records changeUses extra Dataverse storage. One Fabric workspace per environment for now

Search around and you'll still find forum answers telling people to use the Data Export Service, an add-on that copied CRM data into Azure SQL. We'd ignore those threads. Microsoft deprecated the add-on in November 2021, support ran out in November 2022, and Microsoft's own pages send customers to Azure Synapse Link now.

We like the SQL endpoint for the quick, one-off question, the kind where an analyst opens SSMS to count this month's new accounts. It's switched on by default, so there's nothing to enable first. What it isn't is a bulk export tool. Wide, open-ended reads are the ones that time out, so we pick a few columns and put a TOP on the query. Expect lookups in two parts, <lookup>id and <lookup>name, and choice columns with the stored value printed next to the label; it's odd at first and fine after a day.

Once stored procedures, scheduled reports or other apps want CRM data in tables on your own SQL Server, though, Microsoft's three options run out, since none of them writes there. For that you'll want one of the methods earlier on, meaning an SSIS package, an ODBC linked server, or Skyvia replicating or syncing Dynamics data into SQL Server whenever you schedule it.

Benefits of Dynamics CRM and SQL Server Connection

Businesses need to consider Dynamics 365 & SQL Server integration because of its tangible and intangible benefits. Some of the most significant ones are provided below.

  1. Data visibility. As SQL Server is usually seen as an enterprise ERP system, companies can take data from there to complement Dynamics CRM. Thus, Dynamics contains all the data needed for departments working with customers, which enhances the overall data visibility.
  2. Enhanced collaboration. Having integration tools in place makes the collaboration between departments more fruitful.
  3. Informed decision-making. Loading data from one source to another and keeping it up-to-date provides a strong basis for analysis. As SQL Server as well as Dynamics easily connect to another Microsoft product named Power BI, driving analytics becomes easier, and thus makes decisions weighted.

Conclusion

In this article, we have reviewed five fundamental approaches to connecting the SQL Server to Microsoft Dynamics CRM. Some offer bidirectional data transfer, while others can only execute unidirectional integration.

The most complex to implement is the API-based connection since it requires deep technical expertise, but offers the highest level of customization at the same time. Integration with SSIS also requires technical knowledge and SQL language skills. ODBC driver, Power Automate, and Skyvia rely heavily on the user interface, which makes them really convenient and fast to use.

Of course, each way of connecting SQL Server to Dynamics CRM is suitable for a set of use cases. However, among all of them, Skyvia provides a universal approach to integration. It allows businesses to select both simple and complex scenarios for data exchange between a database and a CRM.

Don’t hesitate to try Skyvia today!

FAQ for Dynamics CRM with SQL Server

Loader image

Not if it's an application user. We'd register the app in Microsoft Entra ID, add it to the environment as an application user with a custom security role, and that account costs nothing in license fees. It isn't unlimited, though, because Microsoft still caps how many requests it can send, so a heavy nightly load may get slowed down.

Standard. It's the plan with hourly scheduling, and if you need fresher numbers, Professional runs as often as once a minute (Basic, once a day). Incremental runs only work on objects that have an UpdatedDate or CreatedDate field, so check yours first. Deletes surprise people, since Skyvia removes them from the copy only if Dynamics 365 has auditing on.

Yes, and nobody has to switch them off for the whole company. Dataverse accepts an optional BypassBusinessLogicExecution parameter on each request, which skips your custom synchronous logic, asynchronous logic or both (Power Automate flows still run). The account doing the load needs the prvBypassCustomBusinessLogic privilege, though, and out of the box only system administrators get it.

Yes, through the Skyvia Agent, a small Windows app you install inside your network. The Agent only dials out, over HTTPS on port 443, so your firewall team doesn't have to open a single inbound port. And the SQL Server login? It stays in the Agent's config file on that machine and never gets stored on Skyvia's servers.

Skyvia's Free plan covers 10,000 records a month and two scheduled integrations, and those schedules expire after 30 days. That's plenty for trying a few Dynamics 365 tables, but not for feeding the whole CRM every night. Basic, at $79 a month billed annually or $99 billed monthly, lifts the ceiling to 5M records and five scheduled integrations.

Share

Liliia Levkо

With nearly a decade of experience in technical writing, Liliia specializes in ETL/ELT tools and data management and integration. With a keen eye for detail and a passion for simplifying intricate concepts, she excels at translating technical jargon into accessible content for diverse audiences.

One platform for all your data work

One platform for all your data work

Integration, automation, live data access, and backup. No code, 200+ connectors.

Start free