Just recently, Microsoft announced the release of an exciting new feature, allowing us to use SQL data connections to query the Common Data Service. Up to this point, we’ve always had to connect to CDS environments via the API – while this approach technically worked, it wasn’t always an ideal experience. With this new functionality, we can now hit CDS directly from tools like Power BI and SQL Server Management Studio (SSMS), no longer requiring a staging database when working with tools that are not able to communicate with the API. It should be noted, this feature is currently only available in preview mode, but we’re hoping for GA very soon. I’m super excited about this new functionality and some of the practical uses it provides us, so I decided to take it for a test drive and wanted to share what I learned along the way.
Before I jump in, please note this feature is only available in version 9.1.0.17437 or later. The latest version available in your region can be found here.
My first step was to spin up a new trial environment from within the Power Platform admin center.
I selected Canada as my Region to get a 9.1.0.17XXX environment (latest version not available in the US at the time of this post). Once the environment was provisioned, I validated the version number to ensure I had everything I needed.
The next step was enabling the Tabular Data Stream (TDS) endpoint for the Common Data Service, this is required for viewing entities within CDS. This article explains how to do this in detail, but I’ll walk you through exactly what I did.
First, I downloaded the OrgDBOrgSettingsTool, selecting CRM2016-Tools-KB4046795-ENU-amd64.exe as the download I wanted.
After downloading and installing the tool, I needed to update some config settings to enable the TDS endpoint – I did this by opening the Microsoft.Crm.SE.OrgDBOrgSettingsTool.exe.config file (using Notepad) from the install folder and updated the following settings:
Setting Name: OrgDBOrgSettingsTool_CrmDiscoveryService_CrmDiscoveryService
Setting Value: https://disco.crm3.dynamics.com/XRMServices/2011/Discovery.svc
Setting Name: OrgDBOrgSettingsTool_SKU
Setting Value: Online
Setting Name: OrgDBOrgSettingsTool_UserName
Setting Value:
Setting Name: OrgDBOrgSettingsTool_OrgServiceUri
Setting Value: https://orgf4129e99.crm3.dynamics.com/XrmServices/2011/Organization.svc
Next, I added the following node right before the applicationSettings node in the same file:
"Switch.System.Net.DontEnableSchUseStrongCrypto=false"/>
You can take a look at my final config file in the link below. (Note my org URL is https://orgf4129e99.crm3.dynamics.com).
With the config file updated, it was now time to update TDS. To do so, I needed one last piece of information about my environment – the instance Unique Name. I grabbed the Unique Name in the Developer Resources area of Settings –> Customization within my Dynamics environment.
Next, I open a command prompt, navigating to the folder where the tool exists and ran the following command (replace org14cc0cab with your Unique Name):
Microsoft.Crm.SE.OrgDBOrgSettingsTool.exe Update /u org14cc0cab EnableTDSEndpoint true
I was prompted for the password associated with the User ID I entered in the config file. After entering the password – voila – my Organization Service Proxy was created and I’m now able to query my CDS data directly.
Now, its onto the fun part, the querying. For the sake of this blog, I’m going to show you two ways in which I was able to query data; PowerBI and SQL Server Management Studio.
Query CDI data directly from Power BI
The easiest way to connect to Power BI is from the Power Apps admin center. On the left navigation pane expand Data, select Entities, and then select Analyze in Power BI on the command bar.
This will download a pbids file to access through Power BI Desktop. Open the file and enter your Microsoft Account credentials to sign in to the SQL Server Database.
Note: You will see the Server Name when you are prompted. In my case, the server name is orgf4129e99.crm3.dynamics.com,5558. We will use this server name shortly.
Next, in the navigator you’ll see all entities from the CDS environment available from the SQL Connection. You can leverage this data like any other SQL connection through Power BI. It’s wonderful to be able to treat this like any other SQL dataset in Power BI rather than using a workaround.
Query CDI data directly from SQL Server Management Studio (SSMS)
We can also directly connect to the database from SSMS. To do so, use the Server Name from above.
Currently, only Azure AD auth is supported. The server name is the organization address URL followed by a comma and the port value of 5558. Once the server and auth information is entered, boom, you can start querying directly through SSMS.
That’s it. While I’m sure it looks like there are quite a few steps to follow, it’s not too bad to get going (this took me about 20 mins). I’m sure Microsoft will make this process easier over time as the feature matures. Some quick tests verified that you could do the typical queries you’d expect when working with SQL tables, like joining tables, aggregates and unioning results sets together. (Some of which, you cannot do with fetch XML).
A couple of additional tidbits I learned along the way:
- The entity record data you access is read-only (nothing shocking here)
- Data access uses the Common Data Service security model, which is the same used when accessing entity record data in Power Apps (you can’t see anything you shouldn’t)
Closing Thoughts
All of us at Root16 are super excited about this new feature. SQL is a broadly understood language and having this option available will make the platform more approachable to a larger audience. Fetch had its limits (union support and limited to 50,000 rows on aggregates are the big ones that come to mind) and this approach does not have those same restrictions.
Personally, I’ve been waiting for this day for a long time. As a long time “data person”, I’m just more naturally comfortable working in tools like SQL Server Management Studio and Power BI. This removes barriers for me when I want to do some quick data analysis from a Microsoft hosted instance. Things like evaluating a clients data usage patterns and cleanliness become a lot easier when we have direct query access.