4

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 enter image description here

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?

tks.tman
  • 414
  • 6
  • 16
  • 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 Dec 15 '17 at 16:51
  • @DanielGale Yes I can connect with the user – tks.tman Dec 15 '17 at 16:58
  • 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? https://www.youtube.com/watch?v=28dc3edU2Uc – Daniel Gale Dec 15 '17 at 18:23
  • @DanielGale Yes I already watched that video and followed the steps. – tks.tman Dec 15 '17 at 18:38
  • Proper 64bit instances of everything? https://stackoverflow.com/questions/36472648/sqlsrv-for-php-5-6-on-wamp-server – Daniel Gale Dec 15 '17 at 20:03
  • @DanielGale It shows in phpinfo() page and works with other apps.. just not this laravel app. – tks.tman Dec 15 '17 at 20:50
  • @DanielGale any additional suggestions? – tks.tman Dec 18 '17 at 19:52
  • Sorry, I am out of ideas. It looks like you are on the right track, but still seems like something on the server side. Can you create a simple PHP script that connects? – Daniel Gale Dec 18 '17 at 20:32
  • @DanielGale I did create one and it connected without issue – tks.tman Jan 02 '18 at 19:59
  • @tks.tman Hey, I am currently having the same issue, and was wondering if you had ever resolved the problem. Thanks a lot! – dark_knight94 Apr 08 '20 at 06:47
  • @dark_knight94 I did get it to work. – tks.tman Apr 09 '20 at 15:27
  • @dark_knight94 I did get it to work. I changed 'port' => '', under the 'sqlsrv' connection in the database.php file. I also changed how authentication was done to this : $serverName = "server\name"; $connectionInfo = array( "Database"=>"dbname", "UID"=>$username, "PWD"=>$password); $db_connx= sqlsrv_connect($serverName, $connectionInfo); if ($db_connx) { $user_id= DB::table('users')->where('user_name', $username)->value('user_id'); $user = User::find($user_id); if($user){ Auth::login($user); return true; } return false; } else { return false; } – tks.tman Apr 09 '20 at 15:42

1 Answers1

1

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

Med.ZAIRI
  • 154
  • 5