Query CDS/Dynamics 365 data using SQL Query

Hi Everyone,

You know A SQL data connection is available on the Common Data Service endpoint. Although I am a big fan of FetchXML which makes our lives easier as we don't need to worry about backend things and our powerful Advance Find always helps us to frame Fetch XML in a right way. Sometimes(Debates with Salesforce Team as they always mention that they have SQL Workbench) even I felt that there should be some way in Dynamics as well and finally here it is.

Use SQL to query data (Preview)

This is one of most awaiting feature from many years and is in preview now. 

Points to be considered before you start using it.

1) Download and install 18.4 or latest version of SQL Server Management Studio
2) Only Azure Active Directory Authentication is supported. No SQL and Windows authentication is supported )It completely makes sense as well)
3) Below are the supported operations.

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

4) No updates/Inserts are allowed as this is a read-only connection.

The following Common Data Service datatypes are not supported with the SQL connection:

binary, image, ntext, sql_variant, varbinary, virtual, HierarchyId, managedproperty, file, xml, partylist, timestamp.

How to connect?

1. Open SSMS
2. Give Server name as the organization address URL followed by a comma and the port value of 5558.
3. Authentication - Azure Active Directory-Password
4. Give Username and Password

If you receive below error, please follow the steps mentioned here.

And we are good to run SQL queries on CDS.

Hope this helps.

--
Happy 365'ing

Gopinath