Licensing component fails to parse connection string for SQL transport when custom 'queue schema' is specified

[itarob]

Describe the bug

Description

if SQL-transport is used and a custom queue schema is configured in the connection string (as described here https://docs.particular.net/servicecontrol/transports#sql), the licensing component fails to use this string with the error:
2024-08-15 01:18:21.9548|17|Error|ServiceControl.Transports.SqlServer.SqlServerQuery|Failed to initialise SqlServerQuery|System.Exception: SQL Connection String could not be parsed.
---> System.ArgumentException: Keyword not supported: 'queue schema'.

Expected behavior

the main connection string can also be used by the licensing component

Actual behavior

fails to work with the error above

Versions

ServiceControl 5.6.1
ServicePulse 1.41.0

Steps to reproduce

• use SQL-transport
• use custom queue-schema in connection string

Relevant log output

No response

Additional Information

Workarounds

Explicitly specify the config key 'LicensingComponent/SqlServer/ConnectionString' with a connection string without the 'queue schema' parameter

Additional information

See #4397 (comment)

[soujay]

@itarob - We are unable to recreate the issue with ServiceControl 5.7.0. You could try upgrading to verify this

[itarob]

Nope, still an issue. I tested it with SC 5.8.0:

2024-09-09 10:31:04.7783|1|Info|LoggingConfiguration|Logging to D:\ServiceControl\Particular.Servicecontrol.SqlTransport\Logs\logfile.2024-09-09.txt with LogLevel 'Info'
2024-09-09 10:31:04.8012|1|Info|ServiceBus.Management.Infrastructure.Settings.Settings|No settings found for error log queue to import, default name will be used
2024-09-09 10:31:05.1092|1|Info|Particular.ServiceControl.HostApplicationBuilderExtensions|
-------------------------------------------------------------
ServiceControl Version:             5.8.0+fdd3c1737b71fc90c852a78f38d53393364ee38e
Audit Retention Period (optional):  
Error Retention Period:             10.00:00:00
Ingest Error Messages:              True
Forwarding Error Messages:          False
ServiceControl Logging Level:       Info
Selected Transport Customization:   SQLServer
-------------------------------------------------------------
2024-09-09 10:31:05.1377|1|Info|ServiceControl.Transports.TransportManifestLibrary|Found transport manifest for AmazonSQS
...
2024-09-09 10:31:05.1377|1|Info|ServiceControl.Transports.TransportManifestLibrary|Found transport manifest for SQL Server
2024-09-09 10:31:05.1810|1|Info|ServiceControl.Persistence.PersistenceManifestLibrary|Found persistence manifest for RavenDB
2024-09-09 10:31:05.1810|1|Info|ServiceControl.Persistence.PersistenceManifestLibrary|Found persistence manifest for RavenDB 3.5 (Legacy)
2024-09-09 10:31:56.4857|13|Info|ServiceControl.RavenDB.EmbeddedDatabase|
-------------------------------------------------------------
Database Path:                      D:\ServiceControl\Particular.Servicecontrol.SqlTransport\DB
Database Size:                      1.07 GB
Database Folder Size:               2.7 GB
RavenDB Logging Level:              Operations
-------------------------------------------------------------
2024-09-09 10:31:56.4857|13|Info|ServiceControl.RavenDB.EmbeddedDatabase|Loading RavenDB license from D:\ServiceControl\Particular.Servicecontrol.SqlTransport\Bin\Persisters\RavenDB\RavenLicense.json
2024-09-09 10:31:56.4857|13|Info|NServiceBus.LicenseManager|Selected active license from C:\ProgramData\ParticularSoftware\license.xml
License Expiration: ##############
2024-09-09 10:31:57.5390|25|Info|Particular.LicensingComponent.AuditThroughput.AuditThroughputCollectorHostedService|Starting AuditThroughputCollectorHostedService
2024-09-09 10:31:57.5390|24|Info|ServiceControl.Operations.ErrorIngestion|Ensure started. Infrastructure started
2024-09-09 10:31:57.5390|25|Info|Particular.LicensingComponent.MonitoringThroughput.MonitoringThroughputHostedService|Starting MonitoringThroughputHostedService
2024-09-09 10:31:57.5835|25|Info|ServiceControl.Recoverability.RetryDocumentManager|Found 0 orphaned retry batches from previous sessions.
2024-09-09 10:31:57.6137|19|Info|ServiceControl.Transports.SqlServer.SqlServerQuery|Using ConnectionString used by instance
2024-09-09 10:31:57.6137|19|Error|ServiceControl.Transports.SqlServer.SqlServerQuery|Failed to initialise SqlServerQuery|System.Exception: SQL Connection String could not be parsed.
 ---> System.ArgumentException: Keyword not supported: 'queue schema'.
   at Microsoft.Data.SqlClient.SqlConnectionStringBuilder.GetIndex(String keyword)
   at Microsoft.Data.SqlClient.SqlConnectionStringBuilder.set_Item(String keyword, Object value)
   at System.Data.Common.DbConnectionStringBuilder.set_ConnectionString(String value)
   at ServiceControl.Transports.SqlServer.DatabaseDetails..ctor(String connectionString) in /_/src/ServiceControl.Transports.SqlServer/DatabaseDetails.cs:line 19
   --- End of inner exception stack trace ---
   at ServiceControl.Transports.SqlServer.DatabaseDetails..ctor(String connectionString) in /_/src/ServiceControl.Transports.SqlServer/DatabaseDetails.cs:line 29
   at ServiceControl.Transports.SqlServer.SqlServerQuery.InitializeCore(ReadOnlyDictionary`2 settings) in /_/src/ServiceControl.Transports.SqlServer/SqlServerQuery.cs:line 39
   at ServiceControl.Transports.BrokerThroughput.BrokerThroughputQuery.Initialize(ReadOnlyDictionary`2 settings) in /_/src/ServiceControl.Transports/BrokerThroughput/BrokerThroughputQuery.cs:line 38
2024-09-09 10:31:57.6357|19|Error|Particular.LicensingComponent.BrokerThroughput.BrokerThroughputCollectorHostedService|Could not start BrokerThroughputCollectorHostedService, due to initialisation errors:
SQL Connection String could not be parsed.
2024-09-09 10:31:57.7088|19|Info|Microsoft.Hosting.Lifetime|Now listening on: http://##############

and this is our config (sensitive parts masked):

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <appSettings>
        <add key="ServiceControl/InstanceName" value="Particular.Servicecontrol.SqlTransport" />
        <add key="ServiceControl/DBPath" value="D:\ServiceControl\Particular.Servicecontrol.SqlTransport\DB" />
        <add key="ServiceControl/TransportType" value="SQLServer" />
        <add key="ServiceControl/HostName" value="xxxxxxxx" />
        <add key="ServiceControl/Port" value="xxxxx" />
        <add key="ServiceControl/DatabaseMaintenancePort" value="33334" />
        <add key="ServiceControl/LogPath" value="D:\ServiceControl\Particular.Servicecontrol.SqlTransport\Logs" />
        <add key="ServiceControl/ForwardErrorMessages" value="False" />
        <add key="ServiceControl/ErrorRetentionPeriod" value="10.00:00:00" />
        <add key="ServiceBus/ErrorQueue" value="error" />
        <add key="ServiceControl/EnableFullTextSearchOnBodies" value="True" />
        <add key="ServiceControl/PersistenceType" value="RavenDB" />
        <add key="ServiceControl/RemoteInstances" value="[]" />
    </appSettings>
    <connectionStrings>
        <add name="NServiceBus/Transport" connectionString="Server=xxxxxxxxxxx\xxxxx;Database=xxxxxxx;Encrypt=False;Uid=xxxxxxx;Pwd=xxxxxx;Queue Schema=nsbtransport;Max Pool Size=600;Connect Timeout=60;" />
    </connectionStrings>
</configuration>

[DavidBoike]

I was able to reproduce this locally.

Root cause

The throughput component is designed to reuse existing transport information to try to query for throughput statistics, and then prompt the user in ServicePulse if it is unsuccessful and needs more information.

ServiceControl "sneaks" additional information (such as the queue schema) into the connection string because there's no other way to set transport-specific optional settings. The main part of ServiceControl reads these properties out, and then removes them from the connection string that's delivered to the SQL client. The throughput component is taking the connection string provided by the user verbatim, so when the SQL client goes to validate the connection string, it runs into properties it doesn't understand and throws.

Workaround

The workaround listed in the issue description is valid. This is how the throughput component was designed, to be able to override the default transport settings with more specific throughput-related values.

Solution

Ideally the throughput component should be able to understand connection strings the same as the rest of ServiceControl. The solution would be to either transfer the connection strings to the licensing component after they have been scrubbed, or to repeat the scrubbing logic in the licensing component code.

Once that's done, the single connection string should be sufficient for both purposes, and no ERROR will appear in the ServiceControl log.

One could also argue that the error should be a WARN or even INFO, as this is not a problem from an operational standpoint, and the feedback is surfaced through ServicePulse.

[DavidBoike]

@itarob we have this added to the backlog but I don't know when it might get prioritized. Since you self-identified the workaround I am guessing this is not negatively impacting you in a major way?