CDS T-SQL Endpoint pt 1 – Connecting

The biggest announcement of MBAS for me by a long way was the new T-SQL endpoint. For those who missed it, go and check out the recordings.

In short, Microsoft have added a (preview) way of querying your CDS data using T-SQL, which anyone familiar with SQL Server will already know. It was originally done to support live querying from Power BI, but because they used the same TDS protocol that SQL Server uses, immediately the entire ecosystem of SQL Server tools is opened up. So you can now use SQL Server Management Studio amongst others to query your CDS / Dynamics 365 data. Cool!

I can’t deny I didn’t feel a bit put-out by this announcement coming only a few days after I’d released the latest version of my own SQL 4 CDS tool, but I’m sure I’ll get over it eventually…

Connecting

Obviously the first thing I did was carefully read the documentation 😜 before moving on to try and connect to my own instances.

The key bit of information you need when connecting is the server name (the same as you see in the address bar of your browser when you’re in your app) and the port number 5558. Put these together with a comma, select the Active Directory - Password authentication option and you should be away.

The result was a bit of a let-down:

TDS protocol endpoint is disabled for this organization

I assumed at this point that this new preview feature just wasn’t available for me yet. A few people suggested creating new instances in the Canada (crm3) region as they seem to get new features earlier, but I got the same error again and eventually gave up for a few days.

Update 2020-05-24: This error message has been updated to give some more help on fixing it now:

Enabling the TDS protocol endpoint

2020-06-25: This process is now much easier, see below!

Andrew Bibby then helpfully pointed me to some more documentation on how to enable the new endpoint. Unfortunately, even when I got the correct command line command to run it still didn’t work. Instead it gave me the error:

Error occurred in OrgDBOrgSettings and the error details are GDS resource provider is unable to get instances for tenantId: '<guid>', response status:'NotFound' and reason 'Not Found'

Instead I ended up actually using SQL 4 CDS to update the orgdborgsettings attribute of my organization entity:

select orgdborgsettings, organizationid from organization

I then added <EnableTDSEndpoint>true</EnableTDSEndpoint> at the end of the list of settings and updated it using:

update organization set orgdborgsettings = '<OrgSettings><IsCommandingModifiedOnEnabled>true</IsCommandingModifiedOnEnabled><EnableActivitiesTimeLinePerfImprovement>1</EnableActivitiesTimeLinePerfImprovement><EnableActivitiesFeatures>1</EnableActivitiesFeatures><CanCreateApplicationStubUser>false</CanCreateApplicationStubUser><AllowRoleAssignmentOnDisabledUsers>false</AllowRoleAssignmentOnDisabledUsers><EnableTDSEndpoint>true</EnableTDSEndpoint></OrgSettings>' where organizationid = '<guid>'

I could then immediately connect using SQL Server Management Studio, so now I’m off to play with it! More updates to come…

Update 2020-05-24

The official instructions appear to work for other people, but not for me. Hopefully you’ll have more luck than I did, but I’ve also included an update in SQL 4 CDS 2.1.0 to simplify this process too.

Update 2020-06-25

You can now enable the new endpoint in the Power Platform Admin Center. Navigate to your environment and click Settings in the ribbon. Under the Product section, click Features to see the page below:

In the bottom right, change “Enable TDS endpoint” to “On” and click Save.

Join the conversation

5 Comments

  1. Thanks for doing the legwork on getting this working, it may also be worth mentioning that it works for SSMS 18 but not for SSMS 17, you get the following error.

    The HTTP request is unauthorized with client authentication scheme ‘Anonymous’. The authentication header received from the server was ‘Bearer authorization_uri=https://login.microsoftonline.com/xxxxxxxxxxxxx.

    1. Good point Malcolm, upgrading to SSMS 18 was one of the bits of the docs that I followed before trying it so I never saw what the error was if you skipped this step!

Leave a comment

Your e-mail address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.