php - Unable to connect SQLEXPRESS with Laravel 5.6

Question

I'm trying to connect Laravel 5.6 to my SQLEXPRESS.

When I try to migrate tables from Laravel to SQL, I am getting this error:

SQLSTATE[08001]: [Microsoft][ODBC Driver 13 for SQL Server]TCP Provider: No connection could be made because the target machine actively refused it. (SQL: select * from sysobjects where type = 'U' and name = migrations)

I have installed the sqlsrv extionsion to php and tested it:

<?php
$serverName = "MYNAME\SQLEXPRESS"; 

$connectionInfo = array( "Database"=>"Portal", "UID"=>"sa", "PWD"=>"mySecretPwd");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
     echo "Connection established.<br />";
}else{
     echo "Connection could not be established.<br />";
     die( print_r( sqlsrv_errors(), true));
}
?>

The connection is successfully established.

I also see the extension in the phpinfo();

But if switch to Laravel 5.6 with this settings:

my env:

DB_CONNECTION=sqlsrv
DB_HOST=MYNAME\SQLEXPRESS
DB_PORT=1433
DB_DATABASE=Portal
DB_USERNAME=sa
DB_PASSWORD=mySecredPwd

my config/database:

'default' => env('DB_CONNECTION', 'sqlsrv'),

 'connections' => [

        .....

        'sqlsrv' => [
            'driver' => 'sqlsrv',
            'host' => env('DB_HOST', 'MYNAME\SQLEXPRESS'),
            'port' => env('DB_PORT', '1433'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8',
            'prefix' => '',
        ],

    ],

I'm getting an error. My serverconfigurations manager looks like this:

I can also connect with SQL Server Management Studio 2017.

Answer 1

First, find port number of the default instance:

• Check the port number. If you have MS SQL Server Management Studio, execute: xp_readerrorlog 0, 1, N'Server is listening on' in master database

• With command prompt (there will be a lot of rows, but you must analyze): netstat -ano

Possible solutions:

• Stop SQL Server Browser service and test again, always with port number. You can connect to the default instance of SQL Server with TCP/IP if it is running on port 1433
• Set SQL Server not to use dynamic ports and set the port number of default instance.
• Change config/database in Laravel to find a way to skip port number. I don't have experience with Laravel, but I think that you can try with 'port' => env('DB_PORT', null),

How to configure server to listen to specific port:

• Open "Computer ManagementServicesSQL Server Configuration Manager".
• Expand "SQL Server Network ConfigurationProtocols for [instance name]" and double click "TCP/IP". In the "TCP/IP Properties" dialog box, on the IP Addresses tab, go to IPAll. Leave "TCP Dynamci port" empty. Set "TCP Port" to desired port number (could be default 1433).
• In the "SQL Server Services", double-click SQL Server Browser. In "Service" tab set "Start Mode" to "Manual".
• In the "SQL Server Services", double-click SQL Server Browser. In "Log On" stop service.
• In the "SQL Server Services", double-click SQL Server ([instance name]). In "Log On" restart service.
• Set client connection string to "serverinstance,port" format.

Good starting points are: SQL Server Browser Service and Configure a Server to Listen on a Specific TCP Port

I hope this can help you.