2
min read
A- A+
read
Tue, 01/12/2021 - 22:21 By admin.generic Contributor Lloyd Sebag
21 comments
How to access the Dynamics 365 online SQL Server database

Connect to Dynamics 365 database

EDIT : You can also query Dynamics 365 / CDS SQL Server by using C# with this article. 

Presentation

A new feature is coming this year and is only available as preview for the moment. You are now able to connect your Dynamics 365 database. This SQL connection provides a read-only access to the CDS environment. You can see each column type of each entity and you can execute SQL request directly in the databases.

Prerequisites

Check your environment version

As I said in the prerequisites, your environment you want to access must have at least version 9.1.0.17437.

To verify that, go to your environment (https://********.crm4.dynamics.com), Settings -> About

1

Here you can see the server version : 9.1.0000.18647

If the version is higher than 9.1.0.17437, we can go to the next step.

Enables Tabular Data Stream (TDS) endpoint 

This option is only available for environments enabled for this public preview and with a recent version.

I advise to use Internet Explorer to do it.

To enable it, go to your environment list in the Power Platform admin center -> https://admin.powerplatform.microsoft.com/environments

From there, select your environment and open its settings.

3

In the Settings page, open up the Product section and click on Features. 

4

If the link doesn't work, you can make it yourself.

First, take the id of your environment in the url:

5

Then generate the Features link: https://admin.powerplatform.microsoft.com/environments/yourenvironmentid/settings/Features

You can easily activate the TDS from this page:

6

Now you can access your datas as read-only.

We will see how to connect using Microsoft Sql Server Management studio or Power BI Desktop, depending what you prefer.

Using Microsoft Sql Server Management studio

Open your Microsoft Sql Server Management studio and connect to your environment.

  • Server Type: Database Engine
  • Server name: your crm url and the port 5558 separated with a comma -> ********.crm4.dynamics.com,5558
  • Authentication: Azure Active Directory - Password
  • User name: your admin username
  • Password: your admin password

7

Click on Connect.

8

Now you are connected, you can access the database in read-only mode.

Following the user you are connected with, you will be limited to request the entities you have the read right set in the CRM.

You can use SQL to request the database.

Be aware that not all operation are supported within this feature. You can only use the following:

  • SELECT
  • UNION
  • JOIN
  • FILTER
  • Batch operations 
  • Aggregate operations like COUNT() and MIN() or MAX()

 

Performance

On the following link, a performance comparison has been done between the FetchXML Builder add-on for the XrmToolBox and the T-SQL.

The performance will really depend on the query you are running, the number of column you are requesting.

Read the Joe D365 article which really well explains the tests and gives great conclusions about them.

 

Using Power BI Desktop

To open your database through Power BI Desktop, we will generate a pbids file in Power Apps.

Connect to Power Apps, then go to Data -> Entities

Then download the pbids file clicking on Analyse in Power BI

9

Now, double click on the downloaded file. it should be opened with Power BI Desktop.

You will be asked to sign in with your credentials:

10

Once signed-in, you have now access in read-only to all your tables. 

11

As you can notice in the application, the storage mode used is Direct Query. It means that data are loaded in real-time!

Following the user you are connected with, you will be limited to request the entities you have the read right set in the CRM.

You can start working with them.

Limitations

  • This is a preview feature and you're not supposed to use it for a production environment.
  • The databases are only accessible in read-only mode.
  • Not every operation are available in SQL within this feature.

 

Tags
BI
Data Manipulation
Integration
Power BI
Tutorial

Comments

Very good explanation. However, I don't understand the difference between an oData access via Powerbi and a Tabular Data Stream access.

Moreover, I don't have Use Azure Active Directory authentication under Sql

Have a nice day!

Thu, 01/07/2021 - 08:16
François (not verified)

Hi, I loved your post this is well explained and useful
I was unable to connect Microsoft Sql Server Management studio by appending port number to the org url
where as when i tried only by org url it worked, Also can you please help me that from where did you get port number?

Fri, 04/09/2021 - 08:19
Meghna Kharera (not verified)

Hi Meghna, this port are coming from the official MS documentation wherein they say : 

Ports 1433 and/or 5558 need to be enabled to use the TDS endpoint from a client application such as SSMS. If you only enable port 5558, the user must append that port number to the server name in the Connect to Server dialog of SSMS - for example: myorgname.crm.dynamics.com;5558.

https://docs.microsoft.com/en-us/powerapps/developer/data-platform/dataverse-sql-query

Fri, 04/09/2021 - 17:45

Hello,
This may not always work, probably.

TITLE: Connect to Server
------------------------------
Cannot connect to https://www.xxxx-e.xxx.dynamics.com,5558.
------------------------------
ADDITIONAL INFORMATION:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - Hôte inconnu.) (Microsoft SQL Server, Error: 11001)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc…

Mon, 02/21/2022 - 09:02
Francky (not verified)

I am despairing :-o. The TDS connection in SSMS works fine. Not in my code (.Net Core)
var sqlConnectionString = @"
Data Source=xxxxx.crm4.dynamics.com;
Initial Catalog=xxxxx;
User ID=;
Password=;
Authentication=ActiveDirectoryServicePrincipal;";
using var cn = new SqlConnection(sqlConnectionString);
cn.Open();

cn.Open() says: "Microsoft.Data.SqlClient.SqlException: 'Login failed: The HTTP request was forbidden with client authentication scheme 'Anonymous'." Can everyone help me? Thanks! ;-)

Thu, 02/24/2022 - 08:38
Karsten Krieger (not verified)

Hi, thanks for the help. Have you checked connect it as Linked Server? If so, can you share the settings?
Im receiving the "unknown" error

SE [master]
EXEC master.dbo.sp_dropserver @server=N'AZURE', @droplogins='droplogins'
GO
EXEC master.dbo.sp_addlinkedserver @server = N'AZURE', @srvproduct=N'', @provider=N'MSOLEDBSQL', @datasrc=N'XXXXXXXXXX.api.crm4.dynamics.com', @provstr=N'Authentication=ActiveDirectoryPassword'
--[SQLNCLI11] MSOLEDBSQL
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'collation compatible', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'AZURE', @locallogin = NULL , @useself = N'False', @rmtuser = N'XXXXXXX@XXXXXX.com', @rmtpassword = N'XXXXXX
GO

Tue, 04/12/2022 - 07:56
Pavel Kohout (not verified)

Dear, is it also possible to connect to the TDS endpoint using an SSIS package or linked server (remote query) ?
Thanks,
Tim

Tue, 04/19/2022 - 23:24
Tim (not verified)

Hi, would you know if you can access this feature via SSRS? If so, how do you connect to it (what data source type and connection string)?

Wed, 07/06/2022 - 02:44
Kelvin (not verified)

Hi,

Great feature. Besides readonly, does it support writable as well, like a normal SQL Server database?

Mon, 10/17/2022 - 11:46
Wilson (not verified)

No, not possible at all to write on it. That's the legacy of the on premise version, in fact, it's a security for the integrity of the application to not allow writing on DB and avoid mistakes. 

Wed, 10/19/2022 - 14:01

Hi,This document contains how CRM is connected.
I want to know F&O whether or not connect to the database in this way,thanks.

Mon, 11/27/2023 - 09:37
Crisp Qin (not verified)

Is it possible to use the backup or export functionality to get all the data out of the D365 server and dump it to another location such as Azure DB?

Mon, 03/18/2024 - 16:18
Chad (not verified)

Add new comment

Image CAPTCHA
Enter the characters shown in the image.
Get new captcha!