VS2022 connecting to SQLServer 2014 Database

Question

Hi,
We currently have a 2014 SQL Server Database that we currently are connecting to using VS2012. The code we are using comes from the older 4.5 .Net libraries System.Data.SQLClient. The following is the code it's using:

SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=la04sqlpres01;Initial Catalog=FPY;User ID=UserName;Password=password";
conn.Open();

I tried using the same code with the new VS2022 .Net 6.0 library Microsoft.Data.SQLClient. When it tries to execute the conn.Open command it gets the following error:

Error: A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)

Anyone have any idea what the ConnectionString needs to be now or what the new code might look like?

Answer 1

the newer libraries use ssl (in-transit encrypted) by default. The error means the ssl certificate on the Sqlserver is not trusted by your machine.

the correct fix is to use a trusted ssl certificate on the Sqlserver

you have two hack options in the connection string:

1) disable ssl: Encrypt=False;

2) disable certificate validation: TrustServerCertificate=False;

microsoft recommends the second over the first