SSMS 18.9.2 error in connection with D365 online TDS endpoint.

Question

SSMS 18.9.2 error in connection with D365 online TDS endpoint.

Anton Udalov 1

Since v18.9.2, SSMS no longer works properly in connection with my D365 Online TDS endpoint. The connection can be established, but as soon as I want to open the tables in object exprorer, I get the message: Invalid table name extended_properties for schema sys. It still works fine for me with SSMS v.17.9.1.

Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2021-08-02T14:31:25.96+00:00

What is D356 Online TDS endpoint? My googling only found this post. And an ad for Dynamics 365. If your question is related to that product, maybe you should add a tag for Dynamics 365.
Anton Udalov 1 Reputation point

2021-08-03T18:40:28.647+00:00

In my opinion, it has nothing to do with either Power Platform or D365 directly. As I said, it worked flawlessly in earlier SSMS versions, and also now, if you don't use Object Explorer but send queries from the SQL window. Unfortunately, I don't know exactly which SSMS version I have updated, but I think that this Object Explorer bug only arose from v18.8, because the integration of Azure Data Studio installation was introduced there. The TDS protocol has existed since 1984 and can also be found in Wikipedia. MS provides a read-only stream from Power Platform, more precisely, probably from Dataverse, which also does not contain any system databases. Accordingly, the sys.extended_properties view is also missing (extended-properties-catalog-views-sys-extended-properties), which is empty in most trivial databases. Why the current Object Explorer in SSMS absolutely needs it is a mystery to me.
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2021-08-03T19:00:04.227+00:00

Bug? Yes, if SSMS claims to support D365. Do you have any document that states this?

From my corner, I could just as well claim that it is a shortcoming in D365 (whatever that is) of not supporting sys.extended_properties.

I can't say exactly why SSMS wants to look in sys.extended_properties, but then again, I cannot say why it should not. I believe it stores data for data classification for versions earlier than 2019 in extended properties.
Arvind Shyamsundar [MSFT] 1 Reputation point Microsoft Employee

2021-08-03T19:25:01.8+00:00

Thank you Erland for flagging this issue to us.

Anton, Frank, and others affected by this issue: apologies. SSMS support for the TDS endpoint for Dataverse is still in Preview: https://learn.microsoft.com/en-us/powerapps/developer/data-platform/dataverse-sql-query

We will investigate, and determine where the potential fix needs to be.
Anton Udalov 1 Reputation point

2021-08-03T19:39:26.94+00:00

It is supported: tabular-data-stream-tds-protocol-endpoint-for-common-data-service-cds
And well-documented: b46a581a-39de-4745-b076-ec4dbb7d13ec
Arvind Shyamsundar [MSFT] 1 Reputation point Microsoft Employee

2021-08-03T20:35:20.573+00:00

Hello, Anton. We are in touch with the owners of the TDS endpoint in Dataverse and the investigation is ongoing. Thank you for your patience while we figure this out.

Also, to be clear, the SSMS Support for this feature is still in Preview, as mentioned in the doc I linked to earlier: https://learn.microsoft.com/en-us/powerapps/developer/data-platform/dataverse-sql-query
Laurent Debacker 1 Reputation point

2021-08-04T20:12:30.973+00:00

Any luck? I'm having the exact same issue. 17.9.1 works fine
Richard Dickinson 21 Reputation points

2021-08-04T20:49:01.467+00:00

Hi Anton, Thanks for reporting the issue. It turns out that with SSMS 18.9.2 an incompatibility in the Dataverse emulation of the TDS protocol was identified. We are working on a fix from the Dataverse/Dynamics side. And will update this documentation when it is in deployment. https://learn.microsoft.com/en-us/powerapps/developer/data-platform/dataverse-sql-query

At this point the only option is to go back to 18.9.1 version. We are working to get this fixed as quickly as we can.

Sorry for the pain,
Rich PM Microsoft Dataverse
Richard Dickinson 21 Reputation points

2021-08-04T21:49:13.653+00:00

Hi Erland,

The "Dataverse" (Dynamics data platform) Has built a endpoint which emulates the SQL TDS (tabular data stream) protocol over the top of the Dataverse business logic layer. This makes it much easier for applications like Power BI to connect to the Dataverse data stores. Here is a couple articles that might help.

https://learn.microsoft.com/en-us/powerapps/developer/data-platform/dataverse-sql-query
https://learn.microsoft.com/en-us/powerapps/maker/data-platform/view-entity-data-power-bi

Hope this helps,
Rich Dickinson MSFT - PM Microsoft Dataverse

3 answers

Your answer

Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2021-08-02T14:31:25.96+00:00

What is D356 Online TDS endpoint? My googling only found this post. And an ad for Dynamics 365. If your question is related to that product, maybe you should add a tag for Dynamics 365.
Anton Udalov 1 Reputation point

2021-08-03T18:40:28.647+00:00

In my opinion, it has nothing to do with either Power Platform or D365 directly. As I said, it worked flawlessly in earlier SSMS versions, and also now, if you don't use Object Explorer but send queries from the SQL window. Unfortunately, I don't know exactly which SSMS version I have updated, but I think that this Object Explorer bug only arose from v18.8, because the integration of Azure Data Studio installation was introduced there. The TDS protocol has existed since 1984 and can also be found in Wikipedia. MS provides a read-only stream from Power Platform, more precisely, probably from Dataverse, which also does not contain any system databases. Accordingly, the sys.extended_properties view is also missing (extended-properties-catalog-views-sys-extended-properties), which is empty in most trivial databases. Why the current Object Explorer in SSMS absolutely needs it is a mystery to me.
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2021-08-03T19:00:04.227+00:00

Bug? Yes, if SSMS claims to support D365. Do you have any document that states this?

From my corner, I could just as well claim that it is a shortcoming in D365 (whatever that is) of not supporting sys.extended_properties.

I can't say exactly why SSMS wants to look in sys.extended_properties, but then again, I cannot say why it should not. I believe it stores data for data classification for versions earlier than 2019 in extended properties.
Arvind Shyamsundar [MSFT] 1 Reputation point Microsoft Employee

2021-08-03T19:25:01.8+00:00

Thank you Erland for flagging this issue to us.

Anton, Frank, and others affected by this issue: apologies. SSMS support for the TDS endpoint for Dataverse is still in Preview: https://learn.microsoft.com/en-us/powerapps/developer/data-platform/dataverse-sql-query

We will investigate, and determine where the potential fix needs to be.
Anton Udalov 1 Reputation point

2021-08-03T19:39:26.94+00:00

It is supported: tabular-data-stream-tds-protocol-endpoint-for-common-data-service-cds
And well-documented: b46a581a-39de-4745-b076-ec4dbb7d13ec
Arvind Shyamsundar [MSFT] 1 Reputation point Microsoft Employee

2021-08-03T20:35:20.573+00:00

Hello, Anton. We are in touch with the owners of the TDS endpoint in Dataverse and the investigation is ongoing. Thank you for your patience while we figure this out.

Also, to be clear, the SSMS Support for this feature is still in Preview, as mentioned in the doc I linked to earlier: https://learn.microsoft.com/en-us/powerapps/developer/data-platform/dataverse-sql-query
Laurent Debacker 1 Reputation point

2021-08-04T20:12:30.973+00:00

Any luck? I'm having the exact same issue. 17.9.1 works fine
Richard Dickinson 21 Reputation points

2021-08-04T20:49:01.467+00:00

Hi Anton, Thanks for reporting the issue. It turns out that with SSMS 18.9.2 an incompatibility in the Dataverse emulation of the TDS protocol was identified. We are working on a fix from the Dataverse/Dynamics side. And will update this documentation when it is in deployment. https://learn.microsoft.com/en-us/powerapps/developer/data-platform/dataverse-sql-query

At this point the only option is to go back to 18.9.1 version. We are working to get this fixed as quickly as we can.

Sorry for the pain,
Rich PM Microsoft Dataverse
Richard Dickinson 21 Reputation points

2021-08-04T21:49:13.653+00:00

Hi Erland,

The "Dataverse" (Dynamics data platform) Has built a endpoint which emulates the SQL TDS (tabular data stream) protocol over the top of the Dataverse business logic layer. This makes it much easier for applications like Power BI to connect to the Dataverse data stores. Here is a couple articles that might help.

https://learn.microsoft.com/en-us/powerapps/developer/data-platform/dataverse-sql-query
https://learn.microsoft.com/en-us/powerapps/maker/data-platform/view-entity-data-power-bi

Hope this helps,
Rich Dickinson MSFT - PM Microsoft Dataverse

Answer 1

Richard Dickinson 21

Hi Anton, Thanks for reporting the issue. It turns out that with SSMS 18.9.2 an incompatibility in the Dataverse emulation of the TDS protocol was identified. We are working on a fix from the Dataverse/Dynamics side. And will update this documentation when it is in deployment. https://learn.microsoft.com/en-us/powerapps/developer/data-platform/dataverse-sql-query

At this point the only option is to go back to 18.9.1 version. We are working to get this fixed as quickly as we can.

Sorry for the pain,
Rich PM Microsoft Dataverse

Frank van der Steen 1 Reputation point

2021-08-05T06:24:29.557+00:00

Thanks for this tip. I reverted to SSMS 18.9.1 (had to search for the downloaded) and now object explorer in SSMS works as expected.
As an application manager for me a tool like SSMS is vital in supporting an ERP or CRM system.
As longs as Dynamics-365 doesn't offer an alternative, I will stick to SSMS.
Mike McGranaghan 1 Reputation point

2022-03-02T16:08:10.63+00:00

Thank you very much! This worked for me

Answer 2

CathyJi-MSFT 22,396 Microsoft External Staff

Hi @Anton Udalov ,

I am not familiar with D365, I search this from google, I find this blog.

Your issue is related to Microsoft Dynamics CRM, please ask your issue on Microsoft Dynamics CRM forum, people there will give your help.

Answer 3

Frank van der Steen 1

I have the exact same error.
I am new to Dynamics 365 and was looking for a way to view tables with SSMS.
I found this artcicle from Microsoft
https://learn.microsoft.com/en-us/powerapps/developer/data-platform/dataverse-sql-query
I installed SSMS 18.9.2 assuming the lastest version supports this.
Like AntonUdalov-3166 I can connect to the database but get an error when I click on Tables to view the table in the database.

CathyJi-MSFT 22,396 Reputation points Microsoft External Staff

2021-08-03T06:33:16.817+00:00

Hi @Frank van der Steen ,

The classification of this MS document belongs to Power Apps. You may also ask help on Power Apps forum

Share via

SSMS 18.9.2 error in connection with D365 online TDS endpoint.

3 answers

Your answer