'sqlsrv_connect works fine but laravel fails with : TCP Provider: No connection could be made because the target machine actively refused it

unfortuantely i have to make laravel talk to sql server .. i normally use mysql.. and have developed a swift hatred for sql server..

i'm running xampp on localhost for development. I can connect and query to local sql server fine with navicat and with sql server management studio.

sqlsrv_connect works fine in php, and i can run queries manually with no problem. mssql_connect works fine in php, and i can run queries manually with no problem.

but laravel fails.

in env: 

DB_CONNECTION=sqlsrv
DB_HOST=localhost
DB_DATABASE=handheld2
DB_USERNAME=handheld
DB_PASSWORD=thepasswordhere
DB_PORT=1433

and in laravel database config:

'sqlsrv' => [
            'driver' => 'sqlsrv',
            'url' => env('DATABASE_URL', 'localhost'),
            'host' => env('DB_HOST', 'localhost'),
            'port' => env('DB_PORT', '1433'),
            'database' => env('DB_DATABASE', 'handheld2'),
            'username' => env('DB_USERNAME', ''),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8',
            'prefix' => '',
            'prefix_indexes' => true,
]

i'm not clear what 'url' is for? how is this different to db_host ?

i receive

Illuminate\Database\QueryException SQLSTATE[08001]: [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: No connection could be made because the target machine actively refused it.

both php_sqlsrv_72_ts_x64.dll and php_pdo_sqlsrc_72_x64.dll are enabled in php

am i missing something simple and obvious?

edit: note that this code works

$connectionInfo = array(
    "Database"=>"handheld2",
    "UID" => "handheld",
    "PWD" => "thepasswordhere",
);
$conn = sqlsrv_connect("localhost", $connectionInfo);
if ($conn === false) {
    echo "Could not connect.\n";
    die(print_r(sqlsrv_errors(), true));
}
$stmt = sqlsrv_query($conn, 'SELECT top 1 * from users where users.id = 1');
if($stmt === false) {
    die( print_r( sqlsrv_errors(), true) );
}

while( $row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC) ) {
    print_r($row);
}

and prints out record from db.. so why does it work here but not though laravel ???

phplaravelsqlsrv


Solution 1:[1]

debugging in laravels files in vendor.. turns out it was trying to connect to 'andheld2' instead of 'handheld2'

changing

'url' => env('DATABASE_URL', 'handheld2'),

to

'url' => env('DATABASE_URL', '/handheld2'),

in the database config makes it work.. it apparently ignores the first character.. for some reason? whatever.

Solution 2:[2]

It’s an old question but we had the same problem with the sqlsrv php driver. This was caused by the dynamic ports of the sql server - switching to specific port solved the problem for us.

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-a-server-to-listen-on-a-specific-tcp-port

Solution 3:[3]

If anyone else finds themselves here maybe this will help.

After hours of banging my head against the wall, the fix for me in the end was extremely simple. I was working with a fresh install of everything (including Sql Server) and I had to open Sql Server Configuration Manager (which itself is a pain in the butt in Windows 10...I ended up having to just search in the windows folder for SQLServerManager because I didn't know the exact version number, which was 15).

Then > SQL Server Network Configuation > Protocols for MSSQLSERVER > TCP/IP was disabled. Enabled this, restart SQLSERVER in Windows Services and viola, laravel works.

Solution 4:[4]

For me the solution was to remove the port in the .env file It has something to do with dynamic ports.

same as mentioned in other comments.

My code in .env

DB_CONNECTION=sqlsrv
DB_HOST=USERNAMESERVE\SQLEXPRESS
DB_PORT=
DB_DATABASE=DB_NAME
DB_USERNAME=sa
DB_PASSWORD=sa

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 user2334227
Solution 2 Puschie
Solution 3 Michael Aaron Wilson
Solution 4 FcoCandelario

Related Questions