certificate error when upgraded from 4.6.0 to 5.8.0 for .net6

[virtualpeek]

A certificate error is thrown whenever EnsureDatabase step is executed. This is seen after seen after upgrade to .Net6 moving to dbup version 5.8.0 from 4.6.0. The same issue is also seen with dbup-sqlserver-net6(4.6.1). The same connection string works if we append property TrustServerCertificate=true. However the below connection string used to work with dbup verion 4.6.0 and .Net5

ConnectionString: "Data Source=,1433;Initial Catalog=[];User Id=;Password=*;Connect Timeout=60;"

Sample code:
public static bool EnsureExistance(string connectionString, bool ensureDatabase)
{
if (ensureDatabase)
{
EnsureDatabase.For.SqlDatabase(connectionString, 300);
}
var upgrader =
DeployChanges.To
.SqlDatabase(connectionString)
.WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())
.WithTransactionPerScript()
.WithExecutionTimeout(TimeSpan.FromMinutes(5))
.JournalTo(new NullJournal())
.LogToConsole()
.Build();
var result = upgrader.PerformUpgrade();
return result.Successful;
}
Exception:

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.)

Stack Trace:

at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParserStateObject.ThrowExceptionAndWarning(Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParserStateObject.SNIWritePacket(PacketHandle packet, UInt32& sniError, Boolean canAccumulate, Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParserStateObject.WriteSni(Boolean canAccumulate) at Microsoft.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode, Boolean canAccumulate) at Microsoft.Data.SqlClient.TdsParser.TdsLogin(SqlLogin rec, FeatureExtension requestedFeatures, SessionData recoverySessionData, FederatedAuthenticationFeatureExtensionData fedAuthFeatureExtensionData, SqlConnectionEncryptOption encrypt) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.Login(ServerInfo server, TimeoutTimer timeout, String newPassword, SecureString newSecurePassword, SqlConnectionEncryptOption encrypt) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance) at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool) at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)
at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry, SqlConnectionOverrides overrides)
at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
at Microsoft.Data.SqlClient.SqlConnection.Open()
at SqlServerExtensions.SqlDatabase(SupportedDatabasesForEnsureDatabase supported, String connectionString, IUpgradeLog logger, Int32 timeout, AzureDatabaseEdition azureDatabaseEdition, String collation)
at SqlServerExtensions.SqlDatabase(SupportedDatabasesForEnsureDatabase supported, String connectionString, Int32 commandTimeout)
at MC.Database.Database.EnsureExistance(String connectionString, Boolean ensureDatabase) in C:\MC\MC\MC.Database\Database.cs:line 14
at MC.Core.Helpers.DatabaseHelper.EnsureDatabase() in C:\MC\MC\MC.Core\Helpers\DatabaseHelper.cs:line 53

[AdrianJSClark]

Part of the upgrade from dbup-sqlserver version 4.x to version 5.x was that the previous dependency on the .NET Framework library System.Data.Client was replaced by a reference to the newer Microsoft.Data.Client when compiling against .NET 6.0.

The issues you see here are part of the updated security within Microsoft.Data.Client. You can see the discussion about the change in this PR: Update Encrypt property default value to true (#1210 in dotnet/SqlClient

From here you have several options:

1. Ensure that you have a certificate configured in SQL Server that is trusted by your client machines. (Most secure option)
2. Set Encrypt=false manually in your connection string. (Reverts to old and insecure behaviour which allows man-in-the-middle attacks)
3. Set TrustServerCertificate=true manually in your connection string. (Blindly accepts any certificate, trusted or not, which allows man-in-the-middle attacks)
4. Revert to using dbup-sqlserver version 4.6. (Essentially the same as option 2 above)

Unfortunately as this is a change in the underlying SQL Server client libraries there isn't much we can do in DbUp to assist. Making the decision to go into any of the insecure states needs to be a decision you make based on your own application & environment's risk profile.

[virtualpeek]

Thank you Adrian for the explanation. As I understand the new version Microsoft.Data.SQLClient 4.x.0 + versions was packaged along with .Net7 though it was separately published before. So can we still can use the old version Microsoft.Data.SQLClient with dbup say 3.x.0 as I found that entity framework verison 6.x uses microsoft.Data.SQLClient 3.x.0 and we don't see this certificate related issue there.

[AdrianJSClark]

The package as built requires Microsoft.Data.SqlClient version 5.0.1 or above. Perhaps if there was a specific reason to drop the version requirement down we could update that in a future release. However, since there are three ways to get around this certificate error above I'm not sure that this particular issue is reason enough to go backwards on that requirement.

Even if you do downgrade to an earlier version of Microsoft.Data.SqlClient you are simply choosing the same behaviour as "Option 2" above, where you apply Encrypt=false to your connection string. This value was the old default so simply by updating your connection string it is the same as downgrading to an earlier version, as far as this issue is concerned.

You can even do it inline with a couple of lines of code:

	var builder = new SqlConnectionStringBuilder(connectionString);
	builder.Encrypt = false;
	
	var connectionStringForDbUp = builder.ToString();