Adopt `Microsoft.Data.SqlClient` instead of `System.Data.SqlClient`

[WeihanLi]

Summary of the new feature / enhancement

Maybe we should consider using Microsoft.Data.SqlClient instead of System.Data.SqlClient

• https://devblogs.microsoft.com/dotnet/introducing-the-new-microsoftdatasqlclient/

Proposed technical implementation details (optional)

No response

[iSazonov]

We can not remove System.Data.SqlClient, it will be a huge breaking change. Nevertheless we could add Microsoft.Data.SqlClient.
System.Data.SqlClient.dll is ~1Mb. I guess Microsoft.Data.SqlClient too. We need to see important scenarios to add so large dll in distribution.

[ajansveld]

I believe we now have a compelling reason. Bugs are no longer being fixed in System.Data.SqlClient, only in Microsoft.Data.SqlClient.

Case in point: #16567 was fixed (dotnet/SqlClient#1411) in Microsoft.Data.SqlClient, but this doesn't help PowerShell 7.2 users since it contains System.Data.SqlClient which will not be fixed.

Adding Microsoft.Data.SqlClient and its dependencies to PowerShell will still require people to update their affected scripts, but that is much easier than trying to use it as a standalone package (because #6724).

[daxian-dbw]

Quoted from Introducing the new Microsoft.Data.SqlClient

It means the development focus has changed. We have no intention of dropping support for System.Data.SqlClient any time soon. It will remain as-is and we will fix important bugs and security issues as they arise. If you have a typical application that doesn’t use any of the newest SQL features, then you will still be well served by a stable and reliable System.Data.SqlClient for many years.

That means the System.Data.SqlClient will still be patched with security fixes, though not all bug fixes. So I guess it's fine to keep it in PowerShell, and add the new Microsoft.Data.SqlClient. Thoughts? @TravisEz13 @adityapatwardhan @PaulHigin

[iSazonov]

Maybe move System.Data.SqlClient to WinCompat and now start migration to Microsoft.Data.SqlClient since we are on new dev cycle (vNext is not LTS).

[kerbou]

We would absolutely love to have Microsoft.Data.SqlClient available in Powershell - I'm part of a platform team and want to get rid of SQL Auth. completely in our infrastructure. Not having Microsoft.Data.SqlClient available to us in Powershell is currently blocking us from doing that as we need to authenticate a Powershell process using IAM in our CD pipelines and execute SQL scripts prior to doing database migrations.

Right now, we're forced to stick with SQL Auth which is less than satisfying for us for various reasons - i.e. company compliance policies, just to name one of them

[ronascentes]

I would love to have Microsoft.Data.SqlClient in Powershell but I don't think that adding this .dll to code is the way to go. It would not fit with the open-source philosophy since this addition would only bring benefit to SQL Server, a Microsoft product. Since PowerShell was open sourced, we can see a lot of effort to make it more compatible with other non-Microsoft platforms. Like Python, or any other open-source programming language, we have to use libraries for this job and PowerShell has the modules. The preview v22 version of SqlServer module switches from System.Data.SqlClient to Microsoft.Data.SqlClient and we should use this module for new script developments for SQL Server.

[TravisEz13]

I would love to have Microsoft.Data.SqlClient in PowerShell but I don't think that adding this .dll to code is the way to go. It would not fit with the open-source philosophy since this addition would only bring benefit to SQL Server, a Microsoft product. Since PowerShell was open sourced, we can see a lot of effort to make it more compatible with other non-Microsoft platforms. Like Python, or any other open-source programming language, we have to use libraries for this job and PowerShell has the modules. The preview v22 version of SqlServer module switches from System.Data.SqlClient to Microsoft.Data.SqlClient and we should use this module for new script developments for SQL Server.

I agree with this sentiment. The new Assembly is about 1.5MiB on Windows. These were included to not break existing scenarios. If the the scenario is new, we should move to a module based solution, and it looks like one already exists.

From a security point of view, eventually, I believe we will eventually have to drop System.Data.SqlClient because of certain issues which cannot be fixe (I'm not predicting what they will be.)

[iSazonov]

It is a time to consider the breaking change for 7.4. We have a time to get feedback before release.
/cc @SteveL-MSFT @daxian-dbw