Authenticating with Azure MSI for Azure SQL

[thomasjost]

Environment:

• Framework: Lumen (7.1.4) (Laravel Components ^7.0)
• PHP version: 7.3
• Database and driver: Azure SQL/sqlsrv

Hello,

I am working on a project which requires I use a system assigned managed identify to authenticate with a production database instance. After reviewing the available documentation for Laravel, and reading through Microsoft's documentation on the PDO_SQLSRV driver, I have been unable to identify how to handle this based on the available configuration options. I've even gone as far as falling in the rabbit hole of reviewing how the PDO connections are generated. However, I gained little clarity on the latter.

I am posting here to ask for insight on how to achieve this, if anyone has done this before me. Furthermore, if a PR would be desired to make this easier for future sufferers of PHP-in-a-Microsoft-shop, I'd be happy to author that code.

Available documentation: https://docs.microsoft.com/en-us/sql/connect/php/azure-active-directory?view=sql-server-ver15

In the above linked documentation, Microsoft provides this code snippet:

$azureServer = 'myazureserver.database.windows.net';
$azureDatabase = 'myazuredatabase';
$connectionInfo = array('Database'=>$azureDatabase,
                        'Authentication'=>'ActiveDirectoryMsi');
$conn = sqlsrv_connect($azureServer, $connectionInfo);

if ($conn === false) {
    echo "Could not connect with Authentication=ActiveDirectoryMsi (system-assigned).\n";
    print_r(sqlsrv_errors());
} else {
    echo "Connected successfully with Authentication=ActiveDirectoryMsi (system-assigned).\n";
    
    ...
    
    sqlsrv_close($conn);
}

Translating that to PDO style would look something like:

$azureServer = 'myazureserver.database.windows.net';
$azureDatabase = 'myazuredatabase';

$db = new PDO('sqlsrv:server=$azureServer;Database=$azureDatabase;Authentication=ActiveDirectoryMsi;');

All that being said, my question (which a code review did not answer) is, would it be as simple as adding a key/value to the sqlsrv array for the authentication parameter? So, something like this in config/database.php:

'sqlsrv' => [
            'driver' => 'sqlsrv',
            'host' => env('DB_HOST', 'localhost'),
            'port' => env('DB_PORT', 1433),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => env('DB_CHARSET', 'utf8'),
            'authentication' => env('DB_AUTHENTICATION', ''),
            'prefix' => env('DB_PREFIX', ''),
        ]

Any guidance on this will be greatly appreciated. Thank you!

[orim181]

Hi, I'm running into the same problem and was wondering if you figured it out. Currently, I'm digging into the framework's code to see how I can accomplish ActiveDirectoryMsi authentication. At present, I can only trigger ActiveDirectoryIntegrated.

[cbutzen-angus]

Hello, not sure if this will help someone, but I was able to figure this out by extending the default database connection classes. I created a new folder under app, and copied the following core files over.

• app
  • Custom
    • Database
      • Connectors
        • ConnectionFactory.php
        • SqlServerConnector.php

These core files can be found in vendor/laravel/framework/src/Illuminate/.
The objective here is to extend the base classes, then load the extended class in the Database Service provider.

After you have these files copied over, lets edit the files.

ConnectionFactory.php

Change the namespace.

namespace App\Custom\Database\Connectors;

Import the original class so we can extend it.

use Illuminate\Database\Connectors\ConnectionFactory as BaseFactory;

class ConnectionFactory extends BaseFactory {

Comment out the original class and replace it with your new extended class.

/*use Illuminate\Database\Connectors\SqlServerConnector;*/
use App\Custom\Database\Connectors\SqlServerConnector;

SqlServerConnector.php

In this file, we need to add some extra attributes to the getSqlSrvDsn() method.

Updated the namespace.

namespace App\Custom\Database\Connectors;

Add required classes.

use Illuminate\Database\Connectors\ConnectorInterface;
use Illuminate\Database\Connectors\Connector;

Update getSqlSrvDsn() by adding the following attributes.

//Added to support ActiveDirectoryMsi if (isset($config['authentication'])) { $arguments['Authentication'] = $config['authentication']; }

After adding the 'Authentication' to the attributes, update you sqlsrv connection settings array in database.php.

'authentication' => env('DB_AUTHENTICATION', 'SqlPassword'),

We set the default to be SqlPassword. In order to use Active Directory Managed Identities, we can set it to 'ActiveDirectoryMsi' in our .env file

DB_AUTHENTICATION = ActiveDirectoryMsi

Next, create a new DatabaseServiceProvider.php to use our custom ConnectionFactory.

php artisan make:provider DatabaseServiceProvider

Copy over the existing DatabaseServiceProvider code located in:

Illuminate\Database\DatabaseServiceProvider

Update the ConnectionFactory class to grab our custom ConnectionnFactory.

/*use Illuminate\Database\Connectors\ConnectionFactory;*/
use App\Custom\Database\Connectors\ConnectionFactory;

Last, in our app/config/app.php file, comment out the existing DatabaseServiceProvider.

/*Illuminate\Database\DatabaseServiceProvider::class,*/

And add our new DatabaseServiceProvider.

App\Providers\DatabaseServiceProvider::class,

This should work. If it doesn't, just try playing around with modifying the Custom folder files. This will give you access to modify the connection string.

[Myassar-AWJ]

it's working thanks

[flashmatt]

Fix for this was merged yesterday on #43757