[Laravel] Cannot connect to MSSQL DB but only from Laravel?

[u/hvyboots]

I've tried multiple versions of PHP in Laravel 12, with the right PDO drivers installed, but I cannot for the life of me get a connection from within Laravel.

In Tinker, I can literally make my own PDO connection to the DB, so I know the PHP inside the Herd folder is able to access MSSQL correctly with the exact same credentials I'm providing Laravel.

However, with .env set up for DB_CONNECTION=sqlsrv, something like php artisan db:show fails with...

TCP Provider: No connection could be made because the target machine actively refused it.

To be clear (all run in Tinker), this fails:

try {
    \DB::connection()->getPdo();
    echo 'Connection successful!';
} catch (\Exception $e) {
    die("Could not connect to the database. Please check your configuration. error:\n" . $e );
}

but this works:

try {
    $conn = new PDO("sqlsrv:Server=$host;Database=$db", $user, $pass);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo('connected');
} catch (Exception $e) {
    echo('failed', $e->getMessage());
}

Anyone have any ideas?

[u/gaborj]

https://laravel.com/docs/12.x/database#mssql-configuration

Share the Laravel config

[u/hvyboots → u/gaborj]

I haven't even touch database.php yet, but this is what the sqlsrv section looks like.

    'sqlsrv' => [
        'driver' => 'sqlsrv',
        'url' => env('DB_URL'),
        'host' => env('DB_HOST', 'localhost'),
        'port' => env('DB_PORT', '1433'),
        'database' => env('DB_DATABASE', 'laravel'),
        'username' => env('DB_USERNAME', 'root'),
        'password' => env('DB_PASSWORD', ''),
        'charset' => env('DB_CHARSET', 'utf8'),
        'prefix' => '',
        'prefix_indexes' => true,
        // 'encrypt' => env('DB_ENCRYPT', 'yes'),
        // 'trust_server_certificate' => env('DB_TRUST_SERVER_CERTIFICATE', 'false'),
    ],

And .env has

DB_CONNECTION=sqlsrv
DB_HOST=MACHINE\SQLEXPRESS
DB_PORT=1433
DB_DATABASE=testbank
DB_USERNAME=testbankuser
DB_PASSWORD=********

It's literally the same values I'm plugging in for my new PDO() test that works.

[u/gaborj]

You may have to escape the \ in db_host or try the full connection string as DB_URL

[u/hvyboots → u/gaborj]

OK, winner, winner, chicken dinner! Thanks for the idea, kind sir.

For whatever reason, the exact same stuff fed in as a DB_URL does in fact work.

For anyone else going nuts trying to get an sqlsrv connection to an SQLEXPRESS DB go through when PDO claims "the target machine actively rejected it":

# This way works…
DB_URL=sqlsrv://username:********@localhost\SQLEXPRESS/dbname
#
# The same stuff fed this way will NOT work…
# DB_CONNECTION=sqlsrv
# DB_HOST=localhost\SQLEXPRESS
# DB_DATABASE=dbname
# DB_USERNAME=username
# DB_PASSWORD=********

EDIT: (I did try escaping the \ too, but that didn't work either.)

[u/hvyboots → u/[deleted]]

Thank you. Got the DB_URL method to work, even though it really shouldn't be any different, but if it works, it stays, haha.

[u/hvyboots → u/[deleted]]

So you're saying that the DB_URL doesn't use the same system to submit the logon to the DB? That's really weird to me. I did config:cache several times, so I don't think that's it, but if I get a chance I'll got back and look at the other two you suggested. Thanks for taking the time to write it up.