How to execute the T-SQL to Query Data from CDS and Dynamics 365 CE from Power Platform

Summary: How to execute the T-SQL to Query Data from CDS and Dynamics 365 CE from Power Platform.

Microsoft recently released Power Platform 2020 wave 1 in that one of the great feature is connecting CDS/Dynamics 365 CE database from SSMS.

Earlier if you want to query the CDS/CE cloud database the only option is dumping the data into our SQL Database using SSIS/Scribe or Data Export Services solution has to be configured but now we are able to directly connect to CDS and execute T-SQL queries from SSMS.

 Prerequisites:

•          The CDS environment version should be 9.1.0.17437 or higher and needs to be enabled in the backend.
•         Enable the TDS endpoint for CDS for your environment from Power Platform Administration.
•         SQL server management studio 18.4 or higher version to connect with the CDS database
•        This only for querying data from a CDS database. You will not be able to do insert or update.
•         Security; note that you read security permissions in CRM carry over to SQL – if you do not read for the account entity for example you cannot query it from the CDS SQL point.

Setup:

1.  Login to Power Platform https://admin.powerplatform.microsoft.com/

2.  Navigate to Settings

3.  Navigate to Product and Features.

4.  Turn on the feature TDS Endpoint.

5. Open the SSMS and connect to your CDS environment.

 

  Server Name: {your crmurl},5558

    Ex: suji88.crm.dynamics.com,5558

    Authentication: Authentication drop-down to be ‘Azure Active Directory – Password’

  If your account enabled the multifactor authentication then you need to select. “Azure Active Directory – Universal with MFA”

Operations are supported: SELECT, UNION, JOIN, FILTER, batch operations, and aggregate operations like COUNT () and MIN () or MAX (). The limitation that Fetch XML has for a max of 50,000 aggregate rows is gone when we use T-SQL here as well, which is great! As mentioned before, this is a read-only database, so any operation that would need write permissions to the database will not work.

Limitations:

v  Not every table from the CDS environment is usable. Tables like audit and plugintracelog are not available for querying here.

v  Not every attribute from the CDS environment tables is usable. Per Microsoft documentation, data types of binary, image, ntext, sql_variant, varbinary, virtual, HierarchyId, managedproperty, file, xml, partylist, and timestamp are not usable within a CDS SQL query at this time

v  Some syntax just doesn’t work yet alias name required. Unaliased queries behave oddly in the CDS SQL query feature – if we were to run this query, it works:

Select

s.fullname,

s.systemuserid,

s.createdon,

a.accountid

from dbo.systemuser s (nolock)

left join dbo.account a (nolock) on s.systemuserid = a.ownerid

v  Common Table Expressions (CTE) are not supported yet. This is a big one, but one we’d expect to see added to the functionality eventually due to how valuable CTEs can be for CRM data.

v  Possible Security/Other Implications for Retrieve and RetrieveMultiple usage. From our testing, if you can login to CRM, you can connect to the read-only CDS SQL database. Your security permissions carry over to CDS SQL for the most part, in that if you can’t read entity records in the CRM UI, you won’t be able to read them from the CDS SQL endpoint.

v  The 2-minute timeout

You’ll be reconnecting in SSMS relatively often. This may be a good security feature ultimately, but SSMS will disconnect you from the CDS SQL endpoint if there’s inactivity for what appears to be 10 minutes.