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 9.1.0.17437).
- 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.
| Field | Value |
| 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
UNION
JOIN
FILTER
Batch operations
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.
