Connect SQL Server to the Dynamics 365 Online database.
- Najm-us-saher Farooque
- Sep 12, 2023
- 2 min read
Yes, You can now establish a connection between the Dynamics 365 Online database and SQL Server, marking a significant shift from the previous limitations. In the past, accessing the Dynamics 365 Online database was not possible, but Microsoft has introduced a groundbreaking feature called the "Tabular Data Stream" (TDS) protocol, which now enables you to execute SQL queries on Dynamics Online, also known as the Common Data Model (CDM). It's important to note that the TDS endpoint is currently in a preview phase and permits only read-only operations. However, Microsoft has plans to expand its capabilities in the future.
To get more in-depth information about the TDS protocol, you can follow this link:
Here's a step-by-step guide on how to connect to the database:
Validate Environment Version and TDS Settings:
Open your environment and click on "Settings" -> "About."

Verify that your environment version is 9.1 or higher.

2. Access the Admin Center:
Select your environment

3. Activate TDS Endpoint:
Navigate to "Settings" -> "Products" -> "Features."
Look for the section labeled 'TDS endpoint.' If it's not already activated, enable it.

4. Access the Database:
Launch the SQL Server Management Studio. If you don't have it installed, you can download the latest version from here.
Connect to the server, choosing the "Database Engine" as the server type.
For the server name, use the URL of your environment without the 'https://' prefix, including ',5558' as well. For instance, in this case, the server name would be 'crm939092.crm4.dynamics.com,5558.'

For authentication, note that Azure Active Directory is supported, while SQL authentication and Windows authentication are not.
Enter your username and password for the environment.

5. Connect to the Database:
After configuring the settings, connect to the database. You will have read-only access to the environment's database. Data can be retrieved from all Dataverse tables accessible to the user.

Available Operations include:
Select
Filter
Union
Join
Aggregate Operations like COUNT() or SUM()
Below is the snapshot of the top 5 contact entity:

In a subsequent blog post, we will provide a detailed guide on executing queries on lookup and option sets. Stay tuned for more valuable insights!
Comments