Problem/Motivation
Support for setting these connection options in settings.php was added in
#3291464
✨
Support for SQL Server specific DSN connection options
Fixed
, however at present they can only be used by manually editing settings.php.
This leads to a poor experience when using installing Drupal 10 through the web interface in situations where there is a need to control these setting, for example in testing / development environments when using a SQL Server instance with a self-signed certificate: the Microsoft ODBC Driver enables Encrypt by default since version 18, but doesn't enable TrustServerCertificate by default, which results in the installer being unable to connect to the database due to the certificate failing to verify, with no way to resolve from the installer interface - the only way to continue the installation in this scenario is to manually configure settings.php
Steps to reproduce
- Create new Drupal project with the
sqlsrv module and visit site in the browser to launch the installer
- Provide database connection details for an SQL Server instance which uses a self signed certificate (e.g. using the official SQL Server Docker image
Expected: successful connection to the database and continuation to step 5 of the installer.
Actual: An error message saying Failed to connect to your database server. The server reports the following message: SQLSTATE[08001]: [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: [error:0A000086:SSL routines::certificate verify failed:self-signed certificate].
Proposed resolution
Add support for the Encrypt, TrustServerCertificate, and MultiSubnetFailover connection options in the 'Advanced options' dropdown of the database connection form. The MultiSubnetFailover is not related to hte encryption scenario outlined here, but is important when connecting to an SQL Server availability group (see SQL Server Native Client Support for High Availability, Disaster Recovery.
Remaining tasks
Add form elements for these settings in Drupal\sqlsrv\Driver\Database\sqlsrv\Install::getFormOptions()
User interface changes
Three new checkbox form elements in the install form 'Advanced options' fieldset, one for each connection option. The Encrypt option should be enabled by default with the other two options disabled by default.