Laravel Microsoft Sql Server connection error

Question

I get the following error and I don't know why:

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

.env contains

DB_CONNECTION=sqlsrv
DB_HOST=server\name
DB_PORT=1433
DB_DATABASE=mydatabasename
DB_USERNAME=dbusername
DB_PASSWORD=dbuserpassword

database.php

'default' => env('DB_CONNECTION', 'sqlsrv'), 
    'connections' => [ 
    'sqlsrv' => [
            'driver' => 'sqlsrv',
            'host' => env('DB_HOST', 'server\name'),
            'port' => env('DB_PORT', '1433'),
            'database' => env('DB_DATABASE', 'mydatabasename'),
            'username' => env('DB_USERNAME', 'dbusername'),
            'password' => env('DB_PASSWORD', 'dbuserpassword'),
            'charset' => 'utf8',
            'prefix' => '',
            'pooling' => false,
        ],

    ],

The error is thrown in the UserController.php on this line in the submitLogin function:

if (Auth::attempt(['user_name'=>$request['user_name'], 'password'=>$request['password']])) {
            return redirect()->route('dashboard');
        }

and the User Model has these fields defined:

public $timestamps = false;
protected $table = 'USERS';
protected $primaryKey = 'USER_ID';

Also: I am using php 7.0.23, wamp64bit 3.1.0 with extensions:

extension=php_sqlsrv_7_ts_x86.dll
extension=php_sqlsrv_7_ts_x64.dll

extension=php_sqlsrv_7_nts_x86.dll
extension=php_sqlsrv_7_nts_x64.dll

extension=php_pdo_sqlsrv_7_ts_x86.dll
extension=php_pdo_sqlsrv_7_ts_x64.dll

extension=php_pdo_sqlsrv_7_nts_x86.dll
extension=php_pdo_sqlsrv_7_nts_x64.dll

enabled

I should also note that the database is pre existing.

What can I do or change or fix to get the connection to the server established without error?

Sounds like a firewall issue on the server or user account does not have remote server access in sql server. Can you connect to SQL Server using the user with SSMS? — Daniel Gale
After you edit the .env file, you will need to restart the web server for those settings to take effect. Have you followed steps like here? youtube.com/watch?v=28dc3edU2Uc — Daniel Gale
@DanielGale Yes I already watched that video and followed the steps. — tks.tman
Proper 64bit instances of everything? stackoverflow.com/questions/36472648/… — Daniel Gale

Med.ZAIRI Med.ZAIRI · Accepted Answer · 2020-05-15T16:19:16

When using SQLServer as DB server with Laravel, Ifound that the config shouldbe done as following:

1- if the server is configured with default instance like (server01\SQL01) then we should set the DB_PORT to null or empty string ('')

DB_GRH_HOSTNAME=127.0.0.1\SQLExpress01
DB_GRH_PORTNUMB=null

2- If the server is configured without default instance and should use the TCP/IP port, then we should set the DB_PORT to the tcp/ip port configured on the server

DB_HOST=127.0.0.1
DB_PORT=1433

Hope that help someone

Laravel Microsoft Sql Server connection error

1 Answers