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…
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:
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
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…
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.
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: