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.

This is part of a series of posts on the T-SQL endpoint, read more about it:

  1. Connecting
  2. First Thoughts
  3. SqlClient
  4. Performance
  5. EntityFramework / ORM
  6. Aggregates
  7. Extensibility
  8. Security

You can also watch the session I gave for D365UG UK:

17 thoughts on “CDS T-SQL Endpoint pt 1 – Connecting”

  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!

  2. Seems like this is currently disabled but it should be back soon according to MS:
    Warning

    A problem has been identified with the Tabular Data Stream (TDS) endpoint. This feature is presently globally disabled as we work to address a security issue. A fix for the issue has been developed. Deployment of the fix and feature re-enablement to all public regions is planned for the first week of November 2020. A safe deployment practice is being followed so the feature may be available in your region earlier. Thank you for your patience on this matter.

  3. When I connect it is showing the DB as read-only and when I try to run an update statement I get the following:

    Msg 40000, Level 16, State 1, Line 1
    ‘UPDATE’ statement​ is not supported. Line:1, Position:1
    RequestId: TDS;a856a645-500a-4f2f-9662-23b6adc80b82;8
    Time: 2021-07-23T21:22:48.7323450Z

      1. I had run the installer before my initial post, and just ran it again today (and restarted both SSMS and my computer) but it doesn’t look like it’s working since it’s not showing up in the options screen like you have shown on the install page. Is there something I am missing?

Leave a Reply

Your email address will not be published. Required fields are marked *

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