Invalid value for key 'Encrypt'

[qizu0302]

I'm using Microsoft.Data.SqlClient 5.1.0, set SqlConnectionStringBuilder.Encrypt to SqlConnectionEncryptOption.Strict, it always pop up "Invalid value for key 'Encrypt' " error, I tried other values as well, like true/false/SqlConnectionEncryptOption.Optional, all failed.

Do you know the right value for 'Encrypt' value? And if I want to set up a TLS 1.3 connection to SQL server, do I need to use the latest SQL Server release?

[ErikEJ]

@qizu0302 Sure you are not referencing the System.data.SqlClient SqlConnectionStringBuilder?

[qizu0302]

@ErikEJ Pretty sure I use M.D.S lib

[JRahnama]

@qizu0302 not sure on your setup, I just tested with that version and it worked fine. Can you provide a repro please?

[Wraith2]

could this be the same issue as https://github.com/dotnet/SqlClient/issues/2015 ?

[qizu0302]

Here's my part of code:

configDatabaseConnection = new SqlConnectionStringBuilder(); configDatabaseConnection.DataSource = server; configDatabaseConnection.InitialCatalog = configDatabase; configDatabaseConnection.Encrypt = SqlConnectionEncryptOption.Strict; configDatabaseConnection.TrustServerCertificate = true; configDatabaseConnection.IntegratedSecurity = true;

configDatabaseConnection is used to set up the connection between SharePoint server and SQL Server, the output is below:

Exception: System.ArgumentException: Invalid value for key 'Encrypt'. at Microsoft.Data.Common.DbConnectionStringBuilderUtil.ConvertToSqlConnectionEncryptOption(String keyword, Object value) at Microsoft.Data.SqlClient.SqlConnectionStringBuilder.set_Item(String keyword, Object value) at Microsoft.SharePoint.Utilities.SafeSqlConnectionStringBuilder..ctor(SqlConnectionStringBuilder builder, Boolean copy) at Microsoft.SharePoint.Administration.SPConfigurationDatabase.Provision(SqlConnectionStringBuilder connectionString) at Microsoft.SharePoint.Administration.SPFarmFactory.CreateConfigurationDatabase() at Microsoft.SharePoint.Administration.SPFarmFactory.Create() at Microsoft.SharePoint.Administration.SPFarm.Create(SqlConnectionStringBuilder configurationDatabase, SqlConnectionStringBuilder administrationContentDatabase, SqlConnectionStringBuilder siteMapDatabase, SqlConnectionStringBuilder timerServiceDatabase, IdentityType identityType, String farmUser, SecureString farmPassword, SecureString masterPassphrase) at Microsoft.SharePoint.Administration.SPFarm.Create(SqlConnectionStringBuilder configurationDatabase, SqlConnectionStringBuilder administrationContentDatabase, String farmUser, SecureString farmPassword, SecureString masterPassphrase) at Microsoft.SharePoint.PostSetupConfiguration.ConfigurationDatabaseTask.CreateOrConnectConfigDb() at Microsoft.SharePoint.PostSetupConfiguration.ConfigurationDatabaseTask.Run() at Microsoft.SharePoint.PostSetupConfiguration.TaskThread.ExecuteTask()

[qizu0302]

@JRahnama does your PR aim at this issue? After your PR check in, how can I get the latest library? I mean which NuGet package version I need to use.

[Wraith2]

I think it's this set of artifacts from the CI build https://sqlclientdrivers.visualstudio.com/904996cc-6198-4d39-8540-eca72bdf0b7b/_apis/build/builds/64525/artifacts?artifactName=Artifacts&api-version=7.0&%24format=zip

[qizu0302]

This is 5.10.0 version, I don't see it on https://www.nuget.org/packages?q=Microsoft.Data.SqlClient. Only using this package in my lab also has some errors, so I need to clarify its dependency versions

[qizu0302]

BTW, in order to use TLS 1.3, do I have to use SQL Server 2022 and SSMS 19.0?

[JRahnama]

for TLS 1.3 it has to be SQL Server 2022 and Encrypt type Strict. SSMS 19 yet to add support for it. You can use M.D.SqlClient to achieve that.

[qizu0302]

I have a basic question, if I do following steps:

1. Set 'Force Strict Encryption' to yes in Sql Server Configuration Manager
2. Enable TLS 1.3 in \HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.3\Server(Client)
3. Set 'Encrypt connection' and 'Trust server certificate' in SSMS 19

Does it use TLS 1.3 when connecting to localDB via SSMS?

If I want to let a client to connect to SQL Server using TLS 1.3, is there any configurations need to do on SQL Server side besides setting specific configurations in ConnectionString and registry on client side?

[qizu0302]

Another issue, set "Force strict Encryption" in Sql Server Configuration Manager, disable legacy SSL version, enable TLS 1.3 in registry, then Sql Server service can't start, unless I enable TLS 1.2 as well.

The cert I use is a self-signed one generated on Sql Server, does this issue relate to the cert?

[ErikEJ]

SSMS 19.0 does not support TLS 1.3

[JRahnama]

I have a basic question, if I do following steps:

1. Set 'Force Strict Encryption' to yes in Sql Server Configuration Manager
2. Enable TLS 1.3 in \HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.3\Server(Client)
3. Set 'Encrypt connection' and 'Trust server certificate' in SSMS 19

Does it use TLS 1.3 when connecting to localDB via SSMS?

If I want to let a client to connect to SQL Server using TLS 1.3, is there any configurations need to do on SQL Server side besides setting specific configurations in ConnectionString and registry on client side?

Encryption types are not supported on LocalDB. Also to add more SSMS 19, as @ErikEJ mentioned, is not supporting Strict and TLS 1.3. From what I know, you can check and confirm with SSMS team, it will be added in next version of SSMS.

[qizu0302]

I create a self-signed cert on Sql Server via New-SelfSignedCertificate -Subject "CN=SQL-2022.contoso.local" -KeyAlgorithm RSA -KeyLength 2048 -CertStoreLocation "cert:\LocalMachine\My" -TextExtension @("2.5.29.17={text}DNS=SQL-2022.contoso.local&IPAddress=127.0.0.1&IPAddress=::1") -HashAlgorithm "SHA256" -Type SSLServerAuthentication -Provider "Microsoft RSA SChannel Cryptographic Provider"

then import this cert on my client side to be its trusted CA, do following settings in my code

configDatabaseConnection.Encrypt = SqlConnectionEncryptOption.Strict;
configDatabaseConnection.TrustServerCertificate = true;
configDatabaseConnection.HostNameInCertificate = "SQL-2022.contoso.local";

I enable TLS 1.3 and disable legacy versions on client server, set "Force strict encryption" to "yes" on Sql Server. But when I try to set up a strict connection between client app and Sql Server, it shows

Microsoft.Data.SqlClient.SqlException: 'A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)''

According to https://learn.microsoft.com/en-us/sql/relational-databases/security/networking/tds-8-and-tls-1-3?view=sql-server-ver16#differences-between-tls-12-and-tls-13, RSA key exchange is removed in TLS 1.3 and my cert uses RSA as KeyAlgorithm, is this the root cause? If so, what else algorithms can I use?

[yuanhe772]

Hi team, any updates on this? Im using Microsoft.Data.SqlClient=v5.2.0 and Microsoft.EntityFrameworkCore=v6.0.26, and Im getting a similar error that SqlConnectionStringBuilder cannot recognize "Strict" as the Encrypt option

"InnerException":{"ClassName":"System.FormatException","Message":"String 'Strict' was not recognized as a valid Boolean.","Data":null,"InnerException":null,"HelpURL":null,"StackTraceString":"
   at System.Boolean.Parse(ReadOnlySpan`1 value)\r\n
   at System.Boolean.Parse(String value)\r\n
   at Microsoft.Data.SqlClient.SqlConnectionStringBuilder.set_Item(String keyword, Object value)\r\n
   at System.Data.Common.DbConnectionStringBuilder.set_ConnectionString(String value)\r\n
   at Microsoft.Data.SqlClient.SqlConnectionStringBuilder..ctor(String connectionString)\r\n
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerConnection.<>c.<get_IsMultipleActiveResultSetsEnabled>b__7_0(String cs)\r\n
   at System.Collections.Concurrent.ConcurrentDictionary`2.GetOrAdd(TKey key, Func`2 valueFactory)\r\n
   at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerCompiledQueryCacheKeyGenerator.GenerateCacheKey(Expression query, Boolean async)\r\n
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)\r\n
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)\r\n
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable`1 source, Expression expression, CancellationToken cancellationToken)\r\n
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable`1 source, CancellationToken cancellationToken)\r\n
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.SingleOrDefaultAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)\r\n

[cheenamalhotra]

Please update EF Core to v7 to use MDS 5.1+. Closing as compatibility error.