search

snipe / snipe-it

A free open source IT asset/license management system
https://snipeitapp.com
GNU Affero General Public License v3.0
10.47k stars 3.07k forks source link

MySQL - Insecure Transports are Prohibited error #11785

Open AnestisSi opened 1 year ago

AnestisSi commented 1 year ago

Debug mode

Describe the bug

Hello,

I have an Azure MySQL database configured to enforce TLS1.2(require_secure_transport). When that option is on the application cannot connect. If I turn it off snipe-it connects successfully. Is snipe-it not using ssl to connect to mysql? Or do I need to add specific options on the .env in order to do it?

Reproduction steps

  1. Create MySQL database
  2. Enable secure transport (Azure enables require_secure_transport by default)

Expected behavior

Allow secure connections to MySQL

Screenshots

No response

Snipe-IT Version

v6.0.10

Operating System

Linux

Web Server

not sure using docker

PHP Version

7.4.26

Operating System

No response

Browser

No response

Version

No response

Device

No response

Operating System

No response

Browser

No response

Version

No response

Error messages

Illuminate\Database\QueryException: SQLSTATE[HY000] [3159] Connections using insecure transport are prohibited while --require_secure_transport=ON. (SQL: select * from `users` where `id` = 1 and `users`.`deleted_at` is null limit 1) in file /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php on line 712

#0 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php(672): Illuminate\Database\Connection->runQueryCallback()
#1 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php(376): Illuminate\Database\Connection->run()
#2 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2414): Illuminate\Database\Connection->select()
#3 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2402): Illuminate\Database\Query\Builder->runSelect()
#4 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2936): Illuminate\Database\Query\Builder->Illuminate\Database\Query\{closure}()
#5 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2403): Illuminate\Database\Query\Builder->onceWithColumns()
#6 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(625): Illuminate\Database\Query\Builder->get()
#7 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(609): Illuminate\Database\Eloquent\Builder->getModels()
#8 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Concerns/BuildsQueries.php(294): Illuminate\Database\Eloquent\Builder->get()
#9 /var/www/html/vendor/laravel/framework/src/Illuminate/Auth/EloquentUserProvider.php(53): Illuminate\Database\Eloquent\Builder->first()
#10 /var/www/html/vendor/laravel/framework/src/Illuminate/Auth/SessionGuard.php(148): Illuminate\Auth\EloquentUserProvider->retrieveById()
#11 /var/www/html/vendor/laravel/framework/src/Illuminate/Auth/GuardHelpers.php(60): Illuminate\Auth\SessionGuard->user()
#12 /var/www/html/vendor/laravel/framework/src/Illuminate/Auth/AuthManager.php(336): Illuminate\Auth\SessionGuard->check()
#13 /var/www/html/vendor/laravel/framework/src/Illuminate/Support/Facades/Facade.php(261): Illuminate\Auth\AuthManager->__call()
#14 /var/www/html/app/Http/Middleware/CheckForDebug.php(21): Illuminate\Support\Facades\Facade::__callStatic()
#15 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(167): App\Http\Middleware\CheckForDebug->handle()
#16 /var/www/html/app/Http/Middleware/CheckForSetup.php(32): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#17 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(167): App\Http\Middleware\CheckForSetup->handle()
#18 /var/www/html/vendor/fideloper/proxy/src/TrustProxies.php(57): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#19 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(167): Fideloper\Proxy\TrustProxies->handle()
#20 /var/www/html/vendor/laravel/framework/src/Illuminate/View/Middleware/ShareErrorsFromSession.php(49): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#21 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(167): Illuminate\View\Middleware\ShareErrorsFromSession->handle()
#22 /var/www/html/vendor/laravel/framework/src/Illuminate/Session/Middleware/StartSession.php(121): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#23 /var/www/html/vendor/laravel/framework/src/Illuminate/Session/Middleware/StartSession.php(64): Illuminate\Session\Middleware\StartSession->handleStatefulRequest()
#24 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(167): Illuminate\Session\Middleware\StartSession->handle()
#25 /var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/PreventRequestsDuringMaintenance.php(86): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#26 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(167): Illuminate\Foundation\Http\Middleware\PreventRequestsDuringMaintenance->handle()
#27 /var/www/html/app/Http/Middleware/NoSessionStore.php(28): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#28 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(167): App\Http\Middleware\NoSessionStore->handle()
#29 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(103): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#30 /var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(142): Illuminate\Pipeline\Pipeline->then()
#31 /var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(111): Illuminate\Foundation\Http\Kernel->sendRequestThroughRouter()
#32 /var/www/html/public/index.php(52): Illuminate\Foundation\Http\Kernel->handle()
#33 {main}

Additional context

Is this a fresh install or an upgrade? fresh install

What OS and web server you're running Snipe-IT on

What method you used to install Snipe-IT (install.sh, manual installation, docker, etc) docker

Include what you've done so far in the installation, and if you got any error messages along the way. normal installation. Run into a small issue with the url of the install because Azure was using proxy which I resolved. And only the Database issue

Indicate whether or not you've manually edited any data directly in the database no

Add any other context about the problem here.

welcome[bot] commented 1 year ago

👋 Thanks for opening your first issue here! If you're reporting a 🐞 bug, please make sure you include steps to reproduce it. We get a lot of issues on this repo, so please be patient and we will get back to you as soon as we can.

n3s0 commented 1 year ago

Something that may assist with this. You may need to set .env variables manually in the .env file.

These environment variables are located in the following lines from the config/database.php file.

'options' => (env('DB_SSL')) ? ((env('DB_SSL_IS_PAAS')) ? [
                PDO::MYSQL_ATTR_SSL_CA                  => env('DB_SSL_CA_PATH'),   // /path/to/ca.pem
            ] : [
                PDO::MYSQL_ATTR_SSL_KEY                 => env('DB_SSL_KEY_PATH'),  // /path/to/key.pem
                PDO::MYSQL_ATTR_SSL_CERT                => env('DB_SSL_CERT_PATH'), // /path/to/cert.pem
                PDO::MYSQL_ATTR_SSL_CA                  => env('DB_SSL_CA_PATH'),   // /path/to/ca.pem
                PDO::MYSQL_ATTR_SSL_CIPHER              => env('DB_SSL_CIPHER'),
            ]) : [],
        ],

Try updating your .env file in the database configuration with the following and see? Note. You may need to make necessary changes to your configuration that reflect configuration needed for your database platform.

DB_SSL=True DB_SSL_IS_PAAS=True DB_SSL_CA_PATH='/path/to/ca.pem' DB_SSL_KEY_PATH='/path/to/key.pem' DB_SSL_CERT_PATH='/path/to/cert.pem' DB_SSL_CIPHER='Cipher Name'

Hope this helps!

n3s0 commented 1 year ago

Hi, did this configuration for the application help solve your issue?

doug-fitzmaurice-rowden commented 1 year ago

I ran into a similar problem with Azure's MySQL Flexible servers. When I enabled the DB_SSL_IS_PAAS=True option I was getting a different error:

SQLSTATE[HY000] [2002]  (SQL: select * from information_schema.tables where table_schema = snipeit and table_name = migrations and table_type = 'BASE TABLE')

The actual issue was only present in the full laravel log (/var/www/html/storage/logs/laravel.log):

Peer certificate CN=`abcdef123456.database.azure.com' did not match expected CN=`snipeit.snipeit.private.mysql.database.azure.com'

This was because I was trying to connect to the private DNS zone entry as above. If you hit a similar error make sure you're using the public DNS name, like snipeit.mysql.database.azure.com