Microsoft.Data.SqlClient not backward compatible with System.Data.Client, Error: Keyword not supported: 'trust server certificate'.

[ashutoshsuman99]

Describe the bug

A clear and concise description of what the bug is.

We have recently moved to Microsoft.Data.SqlClient from System.Data.SqlClient to build our connection strings and use it to connect to our backend database as well as to the customer database. But we are getting the following error.

Exception message: The connection string provided is invalid. Keyword not supported: 'trust server certificate'

Looks like the library Microsoft.Data.SqlClient adds spaces to TrustServerCertificate and makes the final string as:

Where as System.Data.SqlClient creates the connection string in the following format:

We have to use Microsoft.Data.SqlClient to support few other types of authentications as well like and work with services which that use this library, and we also work with services that use System.Data.SqlClient. And this interservice communication with connection string having spaces in TrustServerCertificate causes error when used with System.Data.SqlClient.

Is there any way for us to have a connection string without spaces in TrustServerCertificate using Microsoft.Data.SqlClient library?

Code to reproduce the error

    static void SqlConnectionCheck_TrustServerCertificate()
    {
        var mictosoftConnectionbuilder = new Microsoft.Data.SqlClient.SqlConnectionStringBuilder
        {
            DataSource = "localhost",
            InitialCatalog = "master",
            TrustServerCertificate = true,
            IntegratedSecurity = false,
            UserID = "testUser",
            Password = Guid.NewGuid().ToString()
        };

        var connectionString = mictosoftConnectionbuilder.ConnectionString;

        // Error occurs in this line - which is implemented in other service (not is our code)
        var systemConnectionBuidler = new System.Data.SqlClient.SqlConnectionStringBuilder(connectionString);

        using (var connection = new System.Data.SqlClient.SqlConnection(systemConnectionBuidler.ConnectionString))
        {
            connection.Open();
            Console.WriteLine("Connected to the database successfully.");
        }

    }

Further technical details

Microsoft.Data.SqlClient version: (found on the nuget or Microsoft.Data.SqlClient.dll): 5.2.1
.NET target: (e.g. Framework 4.7.1, Core 2.2.2): .NET Framework 4.7.2
Operating system: (e.g. Windows 2019, Ubuntu 18.04, macOS 10.13, Docker container): Windows

Additional context
Add any other context about the problem here.

[arellegue]

This is a duplicate of #1407

[arellegue]

Thank you for reporting this issue. As we are unable to reproduce the issue you reported, could you provide a source code in a Visual Studio Solution that reproduces the issue, please?

Once we received the repro code, we would be able to identify the root cause of the issue.

[ashutoshsuman99]

Hi @arellegue, added in description how to reproduce the error. Please take a look and let me know if any other information is required.

[JRahnama]

@ashutoshsuman99 this breaking change was introduced by PR #534 and announced in the release note for version 2.0.0-preview4.

[ashutoshsuman99]

@JRahnama Thanks for the reply, but since our dependencies (for which we don't control the code) are using System.Data.SqlClient we have to find a way to have TrustServerCertificate without spaces. For now, I am planning to do a simple string replace of Trust Server Certificate with TrustServerCertificate. I was looking for any other approach you might want to suggest for us to do this. It will be a great help.

[JRahnama]

our dependencies (for which we don't control the code) are using System.Data.SqlClient

Be aware that mixing M.D.SqlClient and S.D.SqlClient is not advisable, as it could lead to various exceptions.

[JRahnama]

I am closing this issue as it is by design, but you can keep commenting here and get responses back.

[ashutoshsuman99]

our dependencies (for which we don't control the code) are using System.Data.SqlClient

Be aware that mixing M.D.SqlClient and S.D.SqlClient is not advisable, as it could lead to various exceptions.

Yepp, but we don't have much choice on the piece of code used by our partners. Any suggestions how to tackle the above mentioned problem?

[JRahnama]

Any suggestions how to tackle the above mentioned problem?

Not really. System.Data.SqlClient is in servicing mode and will not receive any updates or fixes except for security-related issues. Therefore, it is recommended that all users migrate to Microsoft.Data.SqlClient, which is the actively maintained ADO.NET library and receives regular updates.

[ErikEJ]

Connection string manipulation.. (seen in some MS products)

[ajcvickers]

Connection string manipulation.. (seen in some MS products)

Pretending the two libraries work the same is a bad idea. If your customers don't want to upgrade, then you can't either.

[ErikEJ]

@ajcvickers Maybe you should address that with the MS products doing this? (Looking at you, EF6 Tools 😄 )

[ajcvickers]

@ErikEJ Do you really think I didn't make my concerns well-known at the time? 😆

[ErikEJ]

@ajcvickers No! 😆