Blog

Connect Dynamics 365 CRM Database from SSMS - Step-by-Step Guide

16 Dec 2024
Connect D365 CRM CDS Database from SQL Server

In today’s digital age, data has become an invaluable asset for businesses, organizations and individuals. In order examining, cleaning, transforming, and interpreting data to extract meaningful insights, it involves a systematic approach to examining data sets, utilizing statistical and computational techniques to reveal hidden relationships and draw valuable conclusions. Data Analytics serves as the bridge between raw data and actionable information, helping individuals and organizations make informed decisions, predict future outcomes, and solve complex problems.

However, raw data is just the beginning; to harness its true power, we need to delve into the realm of Data Analytics.

To analyze D365 CRM database using SQL Server Management Studio, below are the steps to connect to MS SQL Server.

  1. Login to https://admin.powerplatform.microsoft.com/ using administrator credentials.
    Connect D365 CRM CDS Database from SQL Server
  2. In Environments section, click on the environment for which you want to enable D365 CRM CDS for MS SQL Server.
    Connect D365 CRM CDS Database from SQL Server
  3. Click on “Settings” in header section
    Connect D365 CRM CDS Database from SQL Server
  4. On the Settings page, Click on Product and then click on Features
    Connect D365 CRM CDS Database from SQL Server
  5. Enable TDS Endpoint and click “Save”.
    Connect D365 CRM CDS Database from SQL Server
    Connect D365 CRM CDS Database from SQL Server
  6. Now D365 CRM CDS to connect it from MS SQL Server have been successfully enabled.

Steps to connect CDS Database from MS SQL Server

  1. Open SQL Server Management Studio.
  2. In connect to SQL Server Window enter Server name (It will be your D365 CRM URL) e.g. of server name yourdomain.crm.dynamics.com.
  3. Select Authentication as Azure Active Directory – Universal with MFA
  4. Enter Username: Your user id e.g. admin@xyz.com
  5. Click Connect. You will be redirected to a browser for password authentication
    Connect D365 CRM CDS Database from SQL Server
  6. Now you have successfully connected to D365 CRM database
    Connect D365 CRM CDS Database from SQL Server
  7. Now you can write query and execute it to test if its working.

This can save lots of time to analyze, reconcile data to its grain level.

FAQs

Follow the steps to enable the TDS endpoint in your D365 environment, then use SSMS with Azure Active Directory authentication to connect.

Administrator access to Dynamics 365 CRM and appropriate permissions within SSMS are required for a successful connection.

Use Azure Active Directory – Universal with MFA for secure authentication.

The TDS endpoint allows SSMS to access D365 CRM data directly, facilitating easier data analysis and management.

Yes, after establishing the connection, you can run read-only SQL queries on the Dynamics 365 data.

Only specific tables and entities are accessible, with read-only permissions, ensuring data security within the CRM environment.