sqlsrv_connect works fine in php but fails in laravel on the same server(AWS EC2 Instance)

Question

I am trying to connect MSSQL database as my second database in Laravel, In the core PHP file It works fine but when I try to connect in the laravel It throws an error.

Here is my .env
MSSQL_CONNECTION=sqlsrv
MSSQL_DATABASE_URL='/EC2AMAZ-61LPEGK\SQLEXPRESS'
MSSQL_HOST=XX.XX.X.XXX
MSSQL_PORT=1433
MSSQL_DATABASE="/dbcrm"
MSSQL_USERNAME="sauser"
MSSQL_PASSWORD='dbpassword123'
MSSQL_TRUSTSERVER='yes'
MSSQL_ENCRYPT=False
MSSQL_STRICT=false

and here is my laravel database config:
    'sqlsrv' => [
        'driver' => 'sqlsrv',
        'url' => env('MSSQL_DATABASE', '/EC2AMAZ-61LPEGK\SQLEXPRESS'),
        'host' => env('MSSQL_HOST', 'XX.XX.X.XXX'),
        'port' => env('MSSQL_HOSTDB_PORT', '1433'), //I had tried null
        'database' => env('MSSQL_DATABASE', '/dbcrm'), //tried without '/'
        'username' => env('MSSQL_USERNAME', 'sauser'),
        'password' => env('MSSQL_PASSWORD', 'dbpassword123'),
        'trust_server_certificate' => env('MSSQL_TRUSTSERVER', 'yes'), //tried 'no'
        'encrypt' => env('MSSQL_ENCRYPT', False), //tried true
        'charset' => 'utf8',
        'prefix' => '',
        'prefix_indexes' => true,
    ],

Here is the PHP file code which works fine and connect the database as well as execute the query,

$host ="XX.XX.X.XXX";
$username ="sauser";
$password ="dbpassword123";
$database ="dbcrm";
$params = [
"UID" => $username,
"PWD" => $password,
"Database" => $database,
"TrustServerCertificate" => "yes",
];
if(sqlsrv_connect($host, $params)) {
echo "connected..";
} else {
echo "Connection could not be established.<br />";
echo "<pre>";
die( print_r( sqlsrv_errors(), true));
echo "</pre>";
}

I am using the same server for both cases and I have installed/enabled SQLSRV. Please find the phpinfo screenshots for reference.

Why it is working in PHP file but not in the Laravel. Please help. Note : MSSQL database is installed on another AWS windows Instance and TCP is enabled, and port number is 1433

Here is the error what I am getting,

"It throws an error" - If you mean the screen gets blank, that means that PHP is not configured to display error messages and you probably need to check for them in the log files. — Álvaro González, Apr 12 '22 at 11:34
@ÁlvaroGonzález The error is "could not find driver (SQL: select * from [dbo].[p_quotes])", added the screenshot as well — Mahak Choudhary, Apr 12 '22 at 12:27
Are you using windows? If not you may need to get some additional packages up and running. Source: https://laracasts.com/discuss/channels/laravel/sql-server-could-not-find-driver — geertjanknapen, Apr 12 '22 at 13:12
@geertjanknapen I am using ubuntu 20.04 and I have already installed the additional packages but no luck. And as I said in the normal PHP, It is connecting to the database using sqlsrv_connect but having an issue with laravel. — Mahak Choudhary, Apr 12 '22 at 14:12

Mahak Choudhary · Accepted Answer · 2022-04-17T15:59:12.567

0

Finally, able to figure out the issue, Posting the same so it will help someone who has struck for days and ripping their hair out to find the solution.

Change the below .env variables and database config as below,

MSSQL_ENCRYPT=yes

MSSQL_TRUSTSERVER=True

config/database.php

'trust_server_certificate' => env('MSSQL_TRUSTSERVER', true), //Some libraries accessing ODBC/JBDC require Yes/No settings, others True/False, 
'encrypt' => env('MSSQL_ENCRYPT', 'yes'), //same here try Yes/No or True/False

edited Apr 17 '22 at 15:59

answered Apr 14 '22 at 03:12

Mahak Choudhary

1,286
1
16
13

Please note that `mssql` is different from `sqlsrv`. The former is a legacy extension which I believe is no longer supported (it's no even listed in the [PHP manual](https://www.php.net/manual/en/refs.database.php) any more). The latter is a current extension, maintained by Microsoft. – Álvaro González Apr 14 '22 at 09:45

sqlsrv_connect works fine in php but fails in laravel on the same server(AWS EC2 Instance)

1 Answers1