We will learn to access the Dynamics 365 data in SQL Database. Before we start, make sure to subscribe to CRM Crate so that you stay up-to-date in the field of Dynamics 365.
Prerequisites for accessing Dynamics 365 data from SQL Server Database
Below are the key prerequisites for accessing D365 data in SQL Server Database.
- Dynamics 365 CE.
- Environment with latest version (More than 126.96.36.19937).
- Microsoft SQL Server Management Studio.
Validate the version & TDS settings
Let us now validate the environment version and TDS settings.
- Open the Dynamics 365 CE and click on Settings >> About as shown below.
- Verify if your environment version is above 9.1 as shown below.
- Now, open the https://admin.powerplatform.microsoft.com/environments and navigate to Environment (Which needs to be accessed in SQL Studio) >> Settings >> Products >> Features.
- Here, make sure the TDS endpoint is enabled as shown below.
Connect the Dynamics 365 in SQL Management Studio
- Open your Microsoft SQL Server Management studio and connect to your environment with the below given details.
|Server Type||Database Engine|
|Server Name||<Dynamics 365 URL>,5558|
For example: – crmcrateindia.crm8.dynamics.com, 5558
|Authentication||Azure Active Directory – Password|
|User Name||Your administrator user name|
|Password||Your administrator password|
- Once connected, you can access the database in read-only mode.
- Due to limited access, you can only perform the below given operations.
List of operations –SELECT
Aggregate operations like COUNT() and MIN() or MAX()
- Below are the Dynamics 365 & SQL Server snapshot of data present in the “Sample Entity”
Thus we learned to access the Dynamics 365 data in SQL Server Database.