Querying Dynamics 365 databases directly with SQL

 

Querying Dynamics 365 databases directly with SQL

Querying Dynamics 365 databases directly with SQL

In a recent version of Dynamics 365, we got handed the ability to directly query the database via SQL Management Studio. In this post i’ll walk you through the steps to do this yourself.

We can do this on any CRM that is of version 9.1.0.17437 or later. You can check your CRMs version by clicking on the Tools cog on the top right > About and here you will see your current server version.

Check neccessary settings

We’ll have to make sure to set a neccessary flag, before connecting to the database. You can find it in the Power Platform Admin Center. Navigate to Environments > Your environment > Settings > Product > Features, where you will find a flag called Enable TDS endpoint. Set it to active and then click on the Save button.

The flag we need to set.

Connect to your D365 with SQL Management Studio

As server name we have to use the URL of our CRM so that it looks like this: “<name>.crm<number>.dynamics.com,5558”. The port needs to be specified after the comma, and in this case we want the 5558 port.

For authentication we will use Azure Active Directory – Password and just login with our user.

This should be your connect input.

Query data

That’s it, you should see all you tables and be able to make select statements on them. Please remember that this database is readonly, so you can’t make any changes.