<img src="https://secure.leadforensics.com/133892.png" alt="" style="display:none;">

SQL query to Dynamics 365 online

In this blog, we will dive into how we can use our local sql server managment studio (18.6 or later) to connect to common data service endpoints to query records. The connected SQL database is read-only but this will provide us with a lot of flexibility to visualize our data in SQL the way we want. This is the most demanding feature were missing in the past as we use it on-premises dynamics servers for report building and other scenarios. Well, note, this is a preview feature and not recommended for production environments just recently enabled in all Azure regions. You can read about this here.

This feature enables you to use Power BI desktop to view tables in Common Data Service. The table row data that you can access from your environment is read-only. Data access uses the Common Data Service security model that is the same used to access table row data using a Power Apps app. More information here.


To utilize this feature we need to enable Tabular Data Stream (TDS) endpoint for Common Data Service in an environment.

  • Select your environment in the power platform admin center and make sure the environment has at least version
  • Now navigate to: Settings -> Features
  • SQL Server Management Studio (SSMS) version 18.4 or later.

How to Connect to CDS

Open SSMS and click connect:

  1. Server Name: {Orgurl},{5558}

  2. Authentication: Azure Active Directory - Password (only supported for now)

  3. CRM username and password

Example entity data queries

select top 5 a.name as [VIP customer], a.address1_postalcode as [ZIP code] from account a order by a.address1_postalcode desc

Supported operations and data types

The list of supported SQL operations includes:

  • Batch operations
  • Aggregation functions (i.e., Count() and Max() functions)
  • UNIONs and JOINs
  • Filtering

Any DML operations like (i.e., INSERT, UPDATE) will not work as this is a read-only SQL data connection. Common Data Service option sets are represented as <OptionSet>Name and <OptionSet>Label in a result set.

The following Common Data Service datatypes are not supported with the SQL connection: binary, image, text, sql_variant, varbinary, virtual, HierarchyId, managed property, file, xml, party list, timestamp.

Partylist attributes can instead be queried by joining the activity party table as shown below.


Querying data using SQL does not trigger any plug-ins registered on the RetrieveMultipleRequest or RetrieveRequest messages. Any re-writing of the query or results that would normally be performed by such a plug-in will therefore not take effect for a SQL query.

Analyze in Power BI

You can also use the Analyze in Power BI option (Data > Entities > Analyze in Power BI) in Power Apps (https://make.powerapps.com) to use the SQL connection feature to analyze data in Power BI Desktop.

If you do not see this button, your environment does not yet have the feature. More information can be read here.

I hope the CRM community will like this feature and can utilize it to their needs. I also like this feature because unlike the Data Export service you can directly query the live CRM database for records, but be careful and do not run heavy bulk queries on the database as this will impact the performance of the main database.

Take care and stay safe! 😊

Les også "Guide til et vellykket CRM-prosjekt"

Diskuter dette innlegget

Tips til lesing

Landskapet markedsførere opererer i gjennomgår dramatiske forandringer som en konsekvens av AI-revolusjonen vi opplever i dag. Samtidig er kundenes forventninger stadig i endring. Hvordan kan kunstig intelligens og maskinlæring hjelpe deg som markedsfører med å navigere i denne nye virkeligheten og effektivt nå dine kommunikasjons- og konverteringsmål?
We are constantly maturing as consumers in digital sales channels and place higher demands on good user experiences. Therefore, we have developed a health check where we map the quality of user experiences and the user-friendliness of e-commerce solutions.
It is always great to find ways to enhance our productivity and creativity. One such way is by integrating ourselves with advanced technologies like GPT (Generative Pre-trained Transformer). I am going to share my journey of working alongside GPT as a team, focusing on how it has added and increased my abilities and helped me deliver on various work tasks. I am sharing examples of how I use GPT throughout the day and the process I follow to become one with GPT. I have built my own GPT co-pilot setup with various Python applications and integrations, bringing automation to the next level. But all the examples below can be executed without any such extra automation setup.
Er du klar for å la kunstig intelligens skrive møtereferatene og epostene dine? Det kan du nemlig gjøre nå.
Welcome to the exhilarating world of the AI revolution! In this blog series we will dive deep into the most relevant and fascinating topics to help you achieve great results with AI for your business. Lately, AI has been dominating the headlines, creating a whirlwind of buzz that's hard to keep up with. In this first blog we are giving a quick insight to the current status!
right-arrow share search phone phone-filled menu filter envelope envelope-filled close checkmark caret-down arrow-up arrow-right arrow-left arrow-down