How to Access Microsoft Dynamics 365 CRM Data with SQL (and Why You Can’t Insert Directly)

Microsoft Dynamics 365 CRM is one of the most powerful business platforms available today. With the underlying Dataverse (formerly known as Common Data Service), it stores all business entities such as Accounts, Contacts, Opportunities, and custom tables in a secure, scalable cloud database.

One of the most frequently asked questions is:

“Can I connect to Dynamics 365 with Microsoft SQL Server (MSSQL) and run queries like a normal database?”

The answer is both yes and no—let’s explore why.

Accessing Dataverse Data with SQL

Microsoft introduced the TDS (Tabular Data Stream) endpoint, sometimes referred to as SQL 4 CDS, which allows you to connect to Dataverse using tools such as SQL Server Management Studio (SSMS) or Power BI.

How to Connect

1. Open SSMS.

2. Connect to:

   <yourenvironment>.crm.dynamics.com,5558
   

   (Port 5558 is required for TDS endpoint connections.)

3. Choose Azure Active Directory Universal with MFA – Password authentication.

4. Enter your Dynamics 365 credentials.

Once connected, you’ll see tables such as dbo.account, dbo.contact, and others—representing entities in Dataverse.

Example Query

SELECT TOP 10
    name,
    accountnumber,
    revenue
FROM
    dbo.account
ORDER BY
    revenue DESC;

This feels exactly like querying a SQL Server database—but there’s an important limitation.

The Limitation: Read-Only Access

The TDS endpoint is read-only.
That means you cannot perform:

• INSERT

• UPDATE

• DELETE

This restriction is by design. Microsoft enforces business logic, workflows, and security layers through the Dataverse platform. Direct SQL writes would bypass these safeguards, potentially breaking automations, security roles, or integrations.

Alternatives for Writing Data (Insert/Update/Delete)

While SQL write operations are not available, you still have several powerful options:

1. Dataverse Web API (OData/REST)

The official API allows full CRUD (Create, Read, Update, Delete) operations.
Example to create a contact:

POST https://<yourenvironment>.crm.dynamics.com/api/data/v9.3/contacts
Authorization: Bearer <token>
Content-Type: application/json

{
  "firstname": "John",
  "lastname": "Smith",
  "emailaddress1": "john.smith@example.com"
}

2. SDK / Client Libraries

Using the official SDK (C#, Python, JavaScript), you can programmatically insert or update records while respecting Dataverse logic.
Example in C#:

var contact = new Entity("contact");
contact["firstname"] = "John";
contact["lastname"] = "Smith";
service.Create(contact);

3. Power Automate

Microsoft’s Power Automate (formerly Flow) provides a low-code way to insert, update, or delete records in Dataverse without writing SQL.

4. Data Import & Dataflows

For bulk operations, you can import Excel/CSV files or use Power Query Dataflows to push data into Dataverse tables.

While SQL 4 CDS provides a familiar way to query Dynamics 365 CRM data. For full database operations such as insert or update, you’ll need to use the Web API, the SDK, or integration tools like Power Automate.