CakePHP + Azure SQL / System Managed Identity Connections

Need Help
1

Has anyone managed to get Cake working with Azure’s managed identities?

I can connect directly from the app service in a test.php file using:

<?php

$azureServer = 'my-server.database.windows.net';
$azureDatabase = 'sqldb-mydb-001';
$connectionInfo = array(
    'Database' => $azureDatabase,
    'Authentication' => 'ActiveDirectoryMsi'
);
$conn = sqlsrv_connect($azureServer, $connectionInfo);

this works fine so I know it’s configured OK.

I tried creating a DSN but for whatever reason it kills my app and no errors get logged:

'url' => 'Driver={ODBC Driver 17 for SQL Server};Server=my-server.database.windows.net,1433;Database=sqldb-mydb-001;Authentication=ActiveDirectoryMsi;UID=a;'

Any ideas what I may be missing?

2

OK I think I “solved” this by adding

Authentication=ActiveDirectoryMsi

to the DSN in

/vendor/cakephp/cakephp/src/Database/Driver/Sqlserver.php

$dsn = "sqlsrv:Server={$config['host']}{$port};Database={$config['database']};MultipleActiveResultSets=false;Authentication=ActiveDirectoryMsi";

can suggest a pull request to add Authentication as part of the config – there are various other connection methods (Service Principal, AD Password etc) with Sql Server.

3

Not an expert in this, but I simply extended the namespace Sqlserver and override the connect() method, and I call it APP/Database/Driver/CustomSqlserver.php

<?php
declare(strict_types=1);

namespace App\Database\Driver;

use Cake\Database\Driver\Sqlserver;
use PDO;

class CustomSqlserver extends Sqlserver
{
    /**
     * Override the connect() method
     * The main reason to override the connect() method is to add the authentication parameter,
     * this is backward compatitable with existing code
     *
     * @return void
     */
    public function connect(): void
    {
        if ($this->pdo !== null) {
            return;
        }
        $config = $this->_config;

        if (isset($config['persistent']) && $config['persistent']) {
            throw new InvalidArgumentException(
                'Config setting "persistent" cannot be set to true, '
                . 'as the Sqlserver PDO driver does not support PDO::ATTR_PERSISTENT',
            );
        }

        $config['flags'] += [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        ];

        if (!empty($config['encoding'])) {
            $config['flags'][PDO::SQLSRV_ATTR_ENCODING] = $config['encoding'];
        }
        $port = '';
        if ($config['port']) {
            $port = ',' . $config['port'];
        }

        $fqdn = '';
        if (count(explode('.', $config['host'])) == 1) {
            $fqdn = $config['host'] . '.database.windows.net';
        } else {
            $fqdn = $config['host'];
        }

        $dsn = "sqlsrv:Server={$fqdn}{$port};Database={$config['database']};MultipleActiveResultSets=false";
        if ($config['app'] !== null) {
            $dsn .= ";APP={$config['app']}";
        }
        if ($config['connectionPooling'] !== null) {
            $dsn .= ";ConnectionPooling={$config['connectionPooling']}";
        }
        if ($config['failoverPartner'] !== null) {
            $dsn .= ";Failover_Partner={$config['failoverPartner']}";
        }
        if ($config['loginTimeout'] !== null) {
            $dsn .= ";LoginTimeout={$config['loginTimeout']}";
        }
        if ($config['multiSubnetFailover'] !== null) {
            $dsn .= ";MultiSubnetFailover={$config['multiSubnetFailover']}";
        }
        if ($config['encrypt'] !== null) {
            $dsn .= ";Encrypt={$config['encrypt']}";
        }
        if ($config['trustServerCertificate'] !== null) {
            $dsn .= ";TrustServerCertificate={$config['trustServerCertificate']}";
        }

        // Custom add this Authentication method
        if ($config['authentication'] !== null) {
            $dsn .= ";Authentication={$config['authentication']}";
        }

        $this->pdo = $this->createPdo($dsn, $config);
        if (!empty($config['init'])) {
            foreach ((array)$config['init'] as $command) {
                $this->pdo->exec($command);
            }
        }
        if (!empty($config['settings']) && is_array($config['settings'])) {
            foreach ($config['settings'] as $key => $value) {
                $this->pdo->exec("SET {$key} {$value}");
            }
        }
        if (!empty($config['attributes']) && is_array($config['attributes'])) {
            foreach ($config['attributes'] as $key => $value) {
                $this->pdo->setAttribute($key, $value);
            }
        }
    }
}

In app.php

use App\Database\Driver\CustomSqlserver;

    'Datasources' => [
        'default' => [
            'className' => Connection::class,
            'driver' => CustomSqlserver::class,
            'authentication' => env('DB_AUTHENTICATION', null),

And finally, set DB_AUTHENTICATION in my local environment for testing with a Service Principle, along with other stuff such as hostname

export DB_HOST='<server_name>'
export DB_NAME='<database_name>'
export DB_AUTHENTICATION='ActiveDirectoryServicePrincipal'

In Azure > App Settings > Configuration, you set DB_AUTHENTICATION to ActiveDirectoryMsi