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.
