Setting up – Using SQL to query data in Dynamics 365 (Preview)


Recently we were trying out the preview feature of using SQL to query CDS data.

Below are the steps à

To get started,

Download or open SQL Server Management Studio (18.4 or later)

Use Azure Active Directory authentication to connect.

Specify the organization address URL followed by port 5558 in the server name.

If you get the error “TDS protocol endpoint is disabled for this organization”, follow the below steps to enable the TDS i.e. Tabular Data Stream.

Download the OrgDBOrgSettingsTool i.e. CRM2016-Tools-KB4046795-ENU-amd64

https://www.microsoft.com/en-us/download/details.aspx?id=56131

Make the following changes in the configuration file of the tool

Highlighted in green

Run the below command (& enter the password for the admin account specified)

Microsoft.Crm.SE.OrgDBOrgSettingsTool Update /u <org-unique-name> EnableTDSEndpoint true

Get the organization unique name from Customization à Developer Resources

After making the above changes, we were able to connect successfully.

This is a definitely one of the most pleasing additions to the product !

Also check out the wonderful XrmToolBox plugin SQL 4 CDS

and

Skyvia Query 

for running SQL query against Dynamics 365.

Hope it helps..

Author: Nishant Rana

I love working in and sharing everything about Microsoft.NET technology !

9 thoughts on “Setting up – Using SQL to query data in Dynamics 365 (Preview)”

  1. I tried the above to enable TDS but I get the following:
    Error occurred in OrgDBOrgSettings and the error details are Invalid Organization Setting passed in: EnableTDSEndpoint. Please check the datatype and pass in an appropriate value.

    I’ve double checked my values in the config file and appears to connect just won’t update the value.

    Using this at command with unique org name: Microsoft.Crm.SE.OrgDBOrgSettingsTool Update /u XXXXXXXXXX EnableTDSEndpoint true

    Any suggestions?

    Like

      1. Thanks for the quick response. Still no luck. I am able to open the SQL 4 CDS tool but when I go to update it gives me this:
        “Error updating Organizations”
        Not sure what it could be. I’ve double checked the Query and made sure to copy the orgId and ordgdborgsettings, adding the line. I have sys admin rights in CE and Global Admin in D365 tenant.

        Like

  2. It is not working for me, I am getting an error while connecting to SQL:

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 – A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) (Microsoft SQL Server, Error: 10060)

    In Server name, before 5558 port we need to use comms (,) ? I tried both with comma and full stop but no luck.

    Like

Please share your thoughts

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.