Add support for Microsoft.Data.SqlClient

Question

Add support for Microsoft.Data.SqlClient

PawelFojt opened this issue a year ago · 15 comments

I encountered an issue while attempting to connect to SQL Server using the TrustServerCertificate=true parameter in the connection string. It appears that the connection string generated by the toolbox contains spaces, and unfortunately, there is no way to remove these spaces.

This issue prevents successful connection to the SQL Server due to the presence of spaces in the connection string. As a result, the TrustServerCertificate=true parameter cannot be used effectively.

It would be beneficial to address this issue by either providing a method to eliminate spaces from the generated connection string or by ensuring that connection strings generated by the toolbox do not contain spaces, especially when using parameters such as TrustServerCertificate.

Thank you for your attention to this matter.

Steps to reproduce:

Right click on Data Connections -> Add SQL Server Connection..

Test connection is okey.

And here Trust Server Certificate has spaces.

If I click OK in the Connection Properties Window, the following error occurs.

Further technical details:

Toolbox/Power Tools version: 4.8.776

Database engine: SQL Server

Visual Studio or SSMS version: Visual Studio 2022 17.8.6, SSMS 19.3

Answer 1 · 2024-03-08T11:03:49.000Z

Try to change data source when creating the connection

Answer 2 · 2024-03-08T11:19:39.000Z

It is caused by differences in connection strings between System.Data.SqlClient and Microsoft.Data.SqlClient

Answer 3 · 2024-03-18T13:06:01.000Z

@PawelFojt Ping?

Answer 4 · 2024-03-18T13:19:04.000Z

I was able to repro, I will get this fixed

Answer 5 · 2024-03-18T16:06:40.000Z

I added a fix for this in the latest daily build from VSIX gallery, could you try it and let me know?

Answer 6 · 2024-03-18T16:06:47.000Z

See bc7eb88

Answer 7 · 2024-03-18T21:48:20.000Z

Now, when connected to the database, there's no issue, and I can even see that database under "Data Connections." However, when I try to right click on my DB -> script database -> Script schema.., the same error pops up. Additionally, I don't see a single table under the icon of my database.

So I think this issue occurs in multiple places within the application.

Answer 8 · 2024-03-18T21:54:35.000Z

Where are you right clicking?

Answer 9 · 2024-03-18T22:02:41.000Z

On the icon of the database I connected to.

Answer 10 · 2024-03-19T07:17:01.000Z

@PawelFojt OK, you are basically asking for support for Microsoft.Data.SqlClient - it was never added. I will look into it,

In the meantime you can create a connection using the legacy Provider:

Answer 11 · 2024-03-19T09:06:55.000Z

@PawelFojt I added a fix for this in the latest daily build from VSIX gallery, could you try it and let me know?

Answer 12 · 2024-03-20T07:53:29.000Z

The database connection is working, and there are no errors popping up when using the 'Script schema..' command. However, I don't see any tables. When I connect to the same database using SSMS, I can see several tables.

Answer 13 · 2024-03-20T08:10:36.000Z

I am not able to repro, can you share a sample schema?

Answer 14 · 2024-03-20T08:27:23.000Z

Right click on Data Connections -> Add SQL Server Connection.. -> fill Server name -> change Authentication to SQL Server Authentication -> fill User name and Password -> check Trust Server Certificate -> Test Connection is OK -> Click OK -> now I see myDb below Data Connections (but I don't see any table) - right click on myDb -> Script Database.. -> Script Schema -> here I also don't see any table

Answer 15 · 2024-03-20T09:11:01.000Z

Data Connections (but I don't see any table)

Correct, the tool does not list any server tables - this is by design

I am unable to repro - I see tables listed.