Query a CDS database using SQL Server Management Studio (SSMS)

Did you know that you can now query a Common Data Service (CDS) database, including for Dynamics 365 applications, using SQL Server Management Studio (SSMS)?

The catch is that it's in preview and not recommended for Production environments, but even having it for Sandbox environments is awesome (has to be better than Excel), right?

Better still, you can query metadata with it as well, e.g. list tables (yes, terminology changed, it's no longer "entities").

Below are some basic steps to get started, followed by links for further reading. In this example, I'm querying a Dynamics 365 HR CDS database.

1) While logged in as an admin user, go to the Environments page in Power platform admin center:

2) Click on the environment you want, to get to Details, then copy the environment URL, you'll need it later.

3) Click Settings.

4) Open Product --> Features.

5) Enable TDS Endpoint, click Save.

6) Download and install SQL Server Management Studio (SSMS) on the machine you want to connect from, if you don't already have it (version 18.4 or above required).

7) Open SSMS. Enter the connection details:

• Server type: Database Engine
• Server name: the environment URL from step 2, followed by a comma, then 5558
• Authentication: Azure Active Directory - Password.
• User name / password: the admin credentials you logged in with in step 1.

8) Click New Query.

9) Enter the following query for example, which returns a list of tables where the name begins with cdm_ , then press F5 or click Execute (below the New Query button, shown above) to execute it.

select * from sys.tables where name like 'cdm[_]%'

9. Open another query editor window (new query button again), write a query like below, then press F5 or click Execute, to get the contents of a table, for example.

select * from cdm_department

Official Microsoft Docs page:

New to SQL? Why not try a course? There are stacks of good (and free) ones out there to choose from, but given where we are, I'll give this one as an example: