Dapr SQL Server State Store Fails to Connect When Password Contains Semicolon due to Parsing Issue

[MyMirelHub]

Expected Behavior

The Dapr SQL Server state store should successfully connect to the database when the password in the connection string contains a semicolon (;), as semicolons are valid characters in SQL Server passwords, regardless of whether the SQL Server is hosted on Azure or on-premises.

Actual Behavior

When the password in the SQL Server connection string contains a semicolon (;), the Dapr SQL Server state store fails to connect, resulting in a connection error. This is due to the underlying Microsoft Go MSSQL driver (microsoft/go-mssqldb) interpreting the semicolon as a delimiter in the connection string. Attempted workarounds, such as URL-encoding the semicolon (e.g., replacing ; with %3B), also fail to resolve the issue, leading to errors in the Dapr sidecar logs.

Steps to Reproduce the Problem

1. Configure a Dapr SQL Server state store component with a connection string containing a semicolon in the password. For example:

   apiVersion: dapr.io/v1alpha1
   kind: Component
   metadata:
     name: statestore
   spec:
     type: state.sqlserver
     version: v1
     metadata:
       - name: connectionString
         value: Server=localhost;User Id=sa;Password=Test;Pass123;port=1433;database=mydatabase;

2. Alternatively, attempt to use URL-encoding for the semicolon in the password (e.g., Password=Test%3BPass123).
3. Deploy the Dapr application with this component configuration.
4. Observe the connection failure in the Dapr sidecar logs, with an error such as:

   FATA[0000] Fatal error from runtime: process component working-urlencoded error: [INIT_COMPONENT_FAILURE]: initialization error occurred for working-urlencoded (state.sqlserver/v1): [INIT_COMPONENT_FAILURE]: initialization error occurred for working-urlencoded (state.sqlserver/v1): failed to create db schema: mssql: login error: Login failed for user
   

Additional Context

This issue is likely caused by the microsoft/go-mssqldb driver’s improper handling of semicolons in the password field of the connection string, as the driver treats semicolons as delimiters. A similar issue was reported in Grafana (Grafana Issue #17665).

The alternative sqlserver:// connection string format (e.g., `sqlserver://localhost:1433?database=mydatabase&user) is documented for Azure SQL but does not consistently resolve the issue for on-premises SQL Server or all configurations.

This suggests Dapr’s SQL Server state store component may need to implement additional logic to handle special characters in passwords, such as automatic URL-encoding or a custom parsing mechanism, to ensure compatibility with the go-mssqldb driver.

Release Note

RELEASE NOTE: FIX SQL Server state store connection failure when password contains a semicolon by implementing automatic URL-encoding or custom parsing in the connection string handling for both Azure and on-premises SQL Server.

[Twacqwq]

dapr/components-contrib

components-contrib/common/authentication/sqlserver/metadata.go

Lines 75 to 78 in 0c9dd49

	config, err := msdsn.Parse(m.ConnectionString)
	if err != nil {
	return nil, false, fmt.Errorf("failed to parse connection string: %w", err)
	}

microsoft/go-mssqldb

const (
	DsnTypeURL  = 1
	DsnTypeOdbc = 2
	DsnTypeAdo  = 3
)

func getDsnType(dsn string) int {
	if strings.HasPrefix(dsn, "sqlserver://") {
		return DsnTypeURL
	}
	if strings.HasPrefix(dsn, "odbc:") {
		return DsnTypeOdbc
	}
	return DsnTypeAdo
}

Perhaps we can perform additional processing on the password specifically for the DsnTypeAdo type after calling msdsn.Parse(m.ConnectionString), and then overwrite config.Password.

Would it be feasible to encapsulate this logic into a new Parse function? 🤔

[Twacqwq]

@MyMirelHub I'd be happy to try, if that's possible.