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.

a) Batch operations
b) SELECT
c) Aggregation functions (i.e., Count() and Max() functions)
d) UNIONs and JOINs
e) 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?

Open SSMS
Give Server name as the organization address URL followed by a comma and the port value of 5558.
Authentication – Azure Active Directory-Password
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

One thought on “Query CDS/Dynamics 365 data using SQL Query

Leave a Reply

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