4

I am looking for a robust method for checking in C# if a given SQL connection string explicitly specifies a certain parameter, e.g. "Encrypt=...". I tried parsing the string using SqlConnectionStringBuilder, expecting that ContainsKey() would tell me whether a key was specified but it's not working:

System.Data.SqlClient.SqlConnectionStringBuilder x = 
    new("Data Source=.;Initial Catalog=myDb;Integrated Security=True");

bool encryptSpecified = x.ContainsKey("Encrypt"); // returns true :(

Clarification

I should have clarified why I need to know whether the Encrypt parameter was specified explicitly. In the current version of Microsoft.Data.SqlClient, the default value of Encrypt is true, but before (in Sql.Data.SqlClient) it was false. Therefore, to ensure backwards compatibility in an application after upgrading to Microsoft.Data.SqlClient, I want to set the Encrypt parameter to false unless the user explicitly specified a value for it.

Solution

[Based on discussion with @Charlieface]

// important: *not* Microsoft.Data.SqlClient.SqlConnectionStringBuilder!
System.Data.SqlClient.SqlConnectionStringBuilder scsb = 
    new(connectionString); 
if (!scsb.Encrypted) scsb.Encrypted = false; // this will explicitly set Encrypt
connectionString = scsb.ConnectionString;
Mo B.
  • 5,307
  • 3
  • 25
  • 42
  • As far as setting it explicitly to `false`, there is a *reason* why the breaking change was made. You should get a proper certificate and ensure encryption works, rather than just turning it off. – Charlieface Aug 30 '22 at 11:12
  • @Charlieface I know. But it's a tradeoff between better default security and pestering all the existing customers with a breaking change. – Mo B. Aug 30 '22 at 11:14

2 Answers2

3

ContainsKey is essentially useless, as it only tells you whether a key is supported, not whether the string actually contains it. If the string does not have that key then the default is used.

You can just use the Encrypt property to tell you what the value is. This will return false in cases when the default is false (this was changed to true in the new version of Microsoft.Data.SqlClient).

Moreover, this is more useful than just checking to see whether the string contains the key, as it tells you what value would actually be used at runtime.

var x = new SqlConnectionStringBuilder("Data Source=.;Initial Catalog=myDb;Integrated Security=True");
Console.WriteLine(x.ContainsKey("Encrypt"));

dotnetfiddle

Charlieface
  • 52,284
  • 6
  • 19
  • 43
  • My problem is that `Encrypt` does not distinguish whether the parameter was explicitly specified as `false` or whether it was just the implicit default value. – Mo B. Aug 30 '22 at 10:51
  • I know, I pointed out that it shouldn't matter. What should matter is what value is used at runtime, and that is what `Encrypt` tells you – Charlieface Aug 30 '22 at 10:52
  • Hmm, but `SqlConnectionStringBuilder` always returns `false` as default for `Encrypt` even though, as you pointed out, `Microsoft.Data.SqlClient` actually uses `true` at runtime. – Mo B. Aug 30 '22 at 10:58
  • No I get `True` with the new version https://dotnetfiddle.net/7qHiOY Make sure `Microsoft.Data.SqlConnectionStringBuilder` not `System.Data.SqlConnectionStringBuilder` – Charlieface Aug 30 '22 at 11:00
  • Right, I used the old version. I added some background to my question. Actually the solution for me then is to use the old version to get the previous default value. Thanks. – Mo B. Aug 30 '22 at 11:08
1

I ran into the same problem for the same reason. We have lots of SQL Server installs on small internal networks, so it's much preferred for us to keep the old default behavior of Encrypt=false. It's not worth the certificate hassles on all these machines just because Microsoft changed a default. In the rare cases where we have cloud-hosted databases, we'll explicitly configure the certificate on the server and set Encrypt=true.

I didn't want to keep using the old System.Data.SqlClient.SqlConnectionStringBuilder type (as the question's Solution suggests) since my Directory.Packages.props file no longer allows a reference to the System.Data.SqlClient package.

My workaround uses a simple regex:

using Microsoft.Data.SqlClient;
...

SqlConnectionStringBuilder builder = new(connectionString);
Regex containsEncrypt = CreateContainsEncryptRegex();
if (!containsEncrypt.IsMatch(connectionString))
{
    builder.Encrypt = false;
}
connectionString = builder.ConnectionString;

...
[GeneratedRegex(@"(?i)(^|;)\s*Encrypt\s*=", RegexOptions.Compiled)]
private static partial Regex CreateContainsEncryptRegex();
Bill Menees
  • 2,124
  • 24
  • 25
  • 1
    Good idea, you could make the regex a bit more robust though (case insensitive + ignoring whitespaces around the `=`)? See https://learn.microsoft.com/en-us/openspecs/sql_server_protocols/ms-oledbstr/880aa5bf-33c8-4a90-9dbf-e738880cad98?redirectedfrom=MSDN – Mo B. Jun 21 '23 at 08:42
  • Good point about the whitespace. I've updated the regex to allow that. The regex is already case insensitive due to the inline [(?i)](https://learn.microsoft.com/en-us/dotnet/standard/base-types/regular-expression-options) option. – Bill Menees Jun 21 '23 at 12:14
  • 1
    Don't you also need to add a whitespace pattern in front of `Encrypt`? – Mo B. Jun 27 '23 at 09:45
  • Yes! Good catch. Thanks! – Bill Menees Jun 27 '23 at 12:30