1

I am having trouble to add options for laravael config/database.php;

  • TrustServerCertificate
  • Encryption

I am getting below error when trying to connect to MSSQL database;

SQLSTATE[08001]: [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: [error:1416F086:SSL routines:tls_process_server_certificate:certificate verify failed:self signed certificate]

database information is below without TrustServerCertificate&Encryption

    '000002' => [
        'driver'        => 'sqlsrv',
        'odbc_driver'   => '{ODBC Driver 18 for SQL Server}',
        'host' => 'WWW.XXX.YYY.ZZZ',
        'database' => 'database_name',
        'username' => 'user_name',
        'password' => 'pass_word',
        'port' => '1433',
        'prefix'   => '',
        'charset' => 'utf8',
        ]

but when I try with RAW php with below code including TrustServerCertificate it works;

<?php
    $host = "WWW.XXX.YYY.ZZZ";
    $user = "user_name";
    $password = "pass_word";
    $dbname="database_name";
    $options = [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_CASE => PDO::CASE_NATURAL,
        PDO::ATTR_ORACLE_NULLS => PDO::NULL_EMPTY_STRING
    ];

    try {
        $connection = new PDO("sqlsrv:Server=$host,1433; Database=$dbname;TrustServerCertificate=1", $user, $password);


    } catch(PDOException $e) {
        die("Database connection failed: " . $e->getMessage());
        exit;
    }
    echo"Connection Successful";
?>

How can I add this to Laravels database configuration information? I tried multiple ways as below but didn't help;

  • TrustServerCertificate = 1,
  • TrustServerCertificate = '1',
  • TrustServerCertificate = 'true',
  • TrustServerCertificate = true,
  • trust_server_certificate = 1,
  • trust_server_certificate = '1',
  • trust_server_certificate = 'true',
  • trust_server_certificate = true,

Should it be under an options array? What should be the setting for ODBC18 driver?

Thanks

zehyr
  • 41
  • 5
  • 1
    The `Encrypt=1/True/Yes` breaking change was covered in [ODBC Driver 18.0 for SQL Server Released](https://techcommunity.microsoft.com/t5/sql-server-blog/odbc-driver-18-0-for-sql-server-released/ba-p/3169228). Does this previous SO question/answer help, [Laravel SQL Server connection with ENCRYPT=yes trustServerCertificate=true](https://stackoverflow.com/questions/43359009)? – AlwaysLearning Jun 23 '22 at 21:01
  • ENCRYPT=yes trustServerCertificate=true options didn't work either – zehyr Jun 24 '22 at 08:43

1 Answers1

0

If anyone bumps into this issue, just rollback to ODBC driver 17. ODBC driver 18 comes with SSL/Certificate and Encryption requirement as default. If you are able to acquire ssl licences for your servers, do use version 18.

Happy coding :)

zehyr
  • 41
  • 5