Support a custom DSN

Problem/Motivation

Using Drupal Flake → , I am spinning up local dev environments, as well as shell environments, using nixpkgs -- a cross-platform software repository. This allows running different environments without them being in Docker containers.

With this approach, having a system /etc/odbcinst.ini is less than ideal -- it needs to be created by root, updated as the software is updated, and cannot be different per project on the same server.

However, I have not been able to get the pdo_sqlsrv driver to read the config from another location. If I override the DSN, sqlsrv itself is connecting just fine.

I don't know if there's a better approach to solving this -- we only use MS SQL as a migration data source, but we're pulling from it every 15 minutes. So I'm not sure if there are other PDO features necessary for other scenarios, but the fix we've put in seems to be working for us.

Steps to reproduce

1. Set up a local site using Drupal Flake → - nix flake init -t git+https://git.drupalcode.org/project/drupal_flake , edit .env file
2. Create a nix/local-extensions.nix file with the following contents:

{ pkgs, lib, system }:

let
  # Create a nixpkgs instance that allows unfree packages
  pkgsUnfree = import pkgs.path {
    inherit system;
    config.allowUnfree = true;
  };
in
{
  # Additional PHP extensions to include in PHP-FPM
  extraPhpExtensions = [
    pkgsUnfree.php83Extensions.pdo_sqlsrv
    pkgsUnfree.php83Extensions.sqlsrv
  ];

  # Additional Nix packages to include in the dev shell
  extraNixPackages = with pkgs; [
    # Required for sqlsrv extension
    unixODBC
    pkgsUnfree.unixODBCDrivers.msodbcsql18
    pkgsUnfree.sqlcmd
    # Required for ODBC SSL connections
    openssl
  ];

  # Additional shell hook configuration for SQL Server ODBC setup
  extraShellHook = ''
    # Set up local ODBC configuration for SQL Server
    mkdir -p "$PWD/data/odbc"

    # Export the ODBC driver path for use in Drupal configuration
    export MSSQL_ODBC_DRIVER="${pkgsUnfree.unixODBCDrivers.msodbcsql18}/lib/libmsodbcsql-18.1.so.1.1"

    # Create odbcinst.ini in the ODBC directory using nix package path
    cat > "$PWD/data/odbc/odbcinst.ini" << 'ODBC_EOF'
[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=${pkgsUnfree.unixODBCDrivers.msodbcsql18}/lib/libmsodbcsql-18.so
UsageCount=1
ODBC_EOF

    # Set ODBC environment variables
    export ODBCSYSINI="$PWD/data/odbc"
    export ODBCINSTINI="$PWD/data/odbc/odbcinst.ini"

    # Add OpenSSL libraries to LD_LIBRARY_PATH for ODBC driver
    export LD_LIBRARY_PATH="${pkgs.openssl}/lib:''${LD_LIBRARY_PATH:-}"
  '';

  # Additional PHP configuration to add to php.ini
  extraPhpConfig = ''
    pdo_sqlsrv.client_buffer_max_kb_size = 90240
    sqlsrv.ClientBufferMaxKBSize = 90240
  '';
}

3. Set a database stanza in a settings.php for your sql server:

'sqlserver' =>
  array (
    'default' => array (
      'database' => 'db_name',
      'username' => 'username',
      'password' => 'xxx',
      'prefix' => '',
      'host' => 'hostname\DBNAME',
      'namespace' => 'Drupal\sqlsrv\Driver\Database\sqlsrv',
      'driver' => 'pdo_odbc',
    ),
  ),

4. Add the nix/local-extensions.nix, .env file and the rest of the flake services to git, and then load up the dev shell with nix develop (or direnv allow, if you have direnv available).

... and then try to load data from the sqlserver database.

Proposed resolution

Add support for a custom DSN.

The change to the database stanza in settings.php would look like this:

      'driver' => 'sqlsrv',
      'custom_dsn' => 'odbc:Driver={' . getenv('MSSQL_ODBC_DRIVER') . '};Server=hostname\\CLUSTER;Database=DBNAME',

... basically switch the driver and then you can use your custom_dsn. This example gets the environment variable that is set up in the flake's devShell as well as php-fpm if you start the services.

Remaining tasks

Apply code change.

User interface changes

None.

API changes

Adds a "custom_dsn" database array key to the settings.php, for the 'sqlsrv' driver.

Data model changes

None.