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:
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.
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.
Other Posts
This is part of a series of posts on the T-SQL endpoint, read more about it:
You can also watch the session I gave for D365UG UK:
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.
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!
With this now, can we create linked server in Sql for Dynamcis?.
If you find a way to do this, I would love to know as I have been trying for weeks to make something like this work.
As far as I can tell the linked server authentication only supports Windows or SQL auth, not the OAuth option that this endpoint requires
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.
Good Stuff!
Is there any documentation about how to add a linked server connection to the database?
I haven’t seen anything on this. As far as I can tell the linked server authentication only supports Windows or SQL auth, not the OAuth option that this endpoint requires.
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
Yes, the TDS endpoint is read-only – see the docs for details and some other limitations.
You can however run an
UPDATEcommand using SQL 4 CDS. If you’re using SSMS to connect then install the SSMS extension and you’ll be able to write & run INSERT/UPDATE/DELETE queries as if the TDS endpoint did natively support them.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?
Hi Mark,
Is it possible to create a TDS connection string to create an SSRS report?
I haven’t touched SSRS for some time, so I can’t be sure. I’d guess you might have some problems with authentication as SSRS won’t be able to handle the MFA prompts.