Now you can query CDS / Dynamics 365 using SQL (SSMS)

Much awaited, the ability to query the CDS / Dynamics 365 using SQL is now finally in Preview. We can open SQL Server Management Studio and create and test our queries against our favorite data source. What a joy!

NOTE that this feature only allows read-only queries. You can not perform any other operations.

But just like with any preview features, a little bit of leg work is required to enable it. Let’s look at the steps.

#1

First off, check the version for your target environment. It must be at least 9.1.0.17437 but chances are, if you’re trying this, you are toying in a new trial instance. Anyway, you can check that by going to the little cog icon at the top-right side of the screen, and select About, as shown below.

#2

Once you confirmed you have that version or a newer one, you now have to enable this feature. You do this by changing a flag on the OrgDBOrgSettings. You have various tools available to do that, but as of the time when I write this, you don’t have to use tools and commands any more, this has been added to the settings in the Power Platform Admin Center. You can find it by going to the Admin Center and selecting Environments > [your environment] > Settings > Features

The following screenshots shows it.

Make sure you hit Save on that screen, the Save button is under the fold on some screens.

I know some people in the community have reported issues using Microsoft’s OrgDBOrgSettings Tool, and I am not a fan of installing solutions like Sean McNellis’s managed OrgDBOrgSettings solution into environments just to modify settings (don’t get me wrong, it’s a good tool, but I prefer as little clutter as possible in my environments).

#3

Now, fire up SQL Server Management Studio and give it a whack. Connect to your CDS by putting in the server name field the URL of your instance, on port 5558. Your URL will look like this:

[org_name].crm[x].dynamics.com,5558

NOTE in the connection string you separate the port with a comma [,]

For Authentication select to use Azure Active Directory – Password, and plug in your user name and password.

Now hit Connect and voila!

And the floodgates are open!

Have you tried this yet? Are you excited about this new feature? Leave a comment below.