Symfony + mssql. Error tls_process_server_certificate:certificate verify failed:self signed certificate

Question

I try to connect mssql to my symfony project, but get error 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]" So I want to set "TrustServerCertificate=yes" on pdo conception but can't figure out how it does on symfony
I tried to write in .env

DATABASE_MSSQL_URL="mssql://user:password@ip:port/db_name?Trusted_Connection=yes&Encrypt=yes&TrustServerCertificate=yes"

in config/packages/doctrine.yaml:

   dbal:
     jtl:
        url: '%env(resolve:DATABASE_MSSQL_URL)%'
        driver: 'pdo_sqlsrv'
        server_version: '13'
        charset: utf8mb4

It did not help

Some driver interfaces are a bit flaky. Instead of `TrustServerCertificate=yes;` have you tried `TrustServerCertificate=true;` to see if it works? — AlwaysLearning, Apr 15 '22 at 12:56
Also, given the snake_case parameters in the doctrine file you could try `trust_server_certificate: true` there. — AlwaysLearning, Apr 15 '22 at 13:08
"mssql://user:password@ip:port/db_name;TrustServerCertificate=1" - it worked for me — Nastanja, Apr 15 '22 at 16:22

Tomanhez · Answer 1 · 2023-01-04T11:39:59.990

4

For me this options work well(project with sf 5.4):

dbal:
  connections:
    ...
    my_ms_sql_db:
      ...
      options:
        TrustServerCertificate: 1

edited Jan 04 '23 at 11:39

answered Jan 03 '23 at 13:09

Tomanhez

59
4

it worked with sf6.2.9 too – Zoltán Süle Jul 05 '23 at 08:20

mac · Answer 2 · 2022-05-19T16:22:02.977

2

There is a connection option you can add. However, in my project it only worked with a explicit "url" and with the "TrustServerCertificate" option added to both:

dbal:
  connections:
    ...
    my_ms_sql_db:
      url: "sqlsrv://my_user:my_password@192.168.0.2:1434/my_db?TrustServerCertificate=yes"
      driver: pdo_sqlsrv
      charset: UTF-8
      host: ...
      port: ...
      ...
      options:
        TrustServerCertificate: yes

edited May 19 '22 at 16:22

answered May 19 '22 at 16:12

mac

21
3

which Symfony and Doctrine did you use? It worked for me with `TrustServerCertificate: 1` using php 8.1.20, sf6.2.9 and doctrine 2.7 – Zoltán Süle Jul 05 '23 at 08:50
1

@ZoltánSüle currently it's PHP 7.4.29, Symfony 4.4, Doctrine 2.13. Looks like in the `url`, both "yes" and "1" will work. Haven't tested the "1" value in the `yml` syntax. – mch Jul 06 '23 at 09:42

score 1 · Answer 3 · edited Jun 07 '22 at 02:02

1

In mssql connection url, properties can only be delimited by using the semicolon (';')

mssql://user:pass[serverName[\instanceName][:portNumber]];property=value[;property=value]]

Example:

DATABASE_URL=mssql://user:pass@serverName:port/db;**TrustServerCertificate=1**

Although it is jdbc, it also applies in this case.

edited Jun 07 '22 at 02:02

lemon

14,875
6
18
38

answered May 31 '22 at 16:36

Laerte Mercier Junior

11
3

score 1 · Answer 4 · answered Jun 23 '23 at 06:59

IDK with "mssql", but using sqlsrv driver you can try:

DATABASE_URL="sqlsrv://user:pass@ip:1433/dbName?charset=UTF-8&driverOptions[TrustServerCertificate]=1"

The "key" is driverOptions parameter.

If you check https://www.doctrine-project.org/projects/doctrine-dbal/en/current/reference/configuration.html#custom-driver-options and the Doctrine file vendor/doctrine/dbal/src/Driver/SQLSrv/Driver.php, you have some clues about how to pass extra options to the driver.

Symfony + mssql. Error tls_process_server_certificate:certificate verify failed:self signed certificate

4 Answers4