Level 102
Could you try adding 'trust_server_certificate' => true to your sqlsrv config in database.php?
3 likes
Apr 11, 2022
9
Level 27
SQL Server self-signed certificate after Valet restart
This is quite a ways beyond my knowledge of database drivers and schemes, so I thought I’d better ask here.
I’m developing a project on my localhost using Valet on macOS 10.15.7; PHP and other things are installed via Homebrew. I connect to an SQL Server database on a remote production server. All this has been working well.
Just now, php-fpm starting hogging a lot of CPU and making my fans go crazy. A quick Google search indicated this was a Valet issue, so I did a valet restart – that did indeed fix the CPU issue.
After restarting Valet, though, any page in my Laravel app that I visit gives me the following error:
// Illuminate\Database\QueryException
SQLSTATE[08001]: [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: [error:16000069:STORE routines::unregistered scheme:scheme=file][error:80000002:system library::No such file or directory:calling stat(/usr/local/etc/openssl@3/certs)][error:16000069:STORE routines::unregistered scheme:scheme=file]
When I look in /usr/local/etc/openssl@3/, there is indeed no certs folder, while there is an empty folder with this name inside /usr/local/etc/[email protected]/. If I add the folder manually, I get this error instead:
// Illuminate \ Database \ QueryException
SQLSTATE[08001]: [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: [error:0A000086:SSL routines::certificate verify failed:self-signed certificate]
So apparently I’m now using a self-signed certificate to connect, and that’s a problem? Or the remote server is using a self-signed certificate for the database connection (though I don’t think it is), and that’s a problem? I can still connect through Azure Data Studio, so I would presume it’s the local certificate used by PHP that’s the issue, not the remote server’s (which hasn’t changed, anyway).
In order to test a different issue, I recently manually installed the sqlsrv and pdo_sqlsrv drivers on my Mac, and doing so also auto-updated a whole bunch of other stuff in Homebrew. I suspect that has something to do with the current error, but I have no idea what or why.
Indeed, I don’t know whether this is a Laravel/Valet issue, or a Homebrew/PHP issue … or a something-else-entirely issue.
Can anyone help me figure out what’s going on here, and how I can fix it?
Level 27
@Sinnbeck Aha! That fixed it so I can continue working, at least.
Still not sure how anything about my certificate changed, though, or why. Explicitly trusting the certificate feels like a bandaid more than a solution.
Level 102
@kokoshneta Oh yeah it is. Just a "just accept the certificate". But if its only in dev, it might not be that big an issue? Sadly I dont have a mac, so I cannot really test it out myself :)
Level 27
@Sinnbeck True – once I deploy to the production server, hopefully the certificate should be properly signed.
Level 102
@kokoshneta I assume the live server is ubunutu and similar, so if it gives any errors, it should be alot easier to find a fix (like adding the correct cert to the correct path or similar)
Level 27
@Sinnbeck Well, it’s a Windows Server (hence the SQL Server database) managed through Plesk. Not ideal, but too much legacy stuff there to make it worth anyone’s while to change it. And I assume (hope) the same applies there, as regards fixing certificate errors – though I have to admit my experience with certificates in Plesk and Windows Server very much sets the opposite precedent.
Level 102
@kokoshneta It isnt possible to move the website to another server? I worked on a similar set up, though the app was coded in Java. I rewrote it in laravel and allowed the new php server access to the Sql server (Still windows). I of course build a staging server first and made a dev database I could test on. And when everything worked I changed the DNS
Level 27
@Sinnbeck It’s probably possible, but not financially or practically feasible. The entire admin backend for the site is classic VBScript ASP, and I don’t have time (nor does the company have the money) to rebuild that, so I’m only redoing the frontend as a Laravel app. The site is nearly 20 years old and has been accumulating bloat code for almost as long, so it’s pretty much a hot mess. I’m more or less the only one who uses the admin backend, so I can live with that being quirky and user-hostile; the customer-facing frontend is what really needs to be improved.
Level 102
@kokoshneta But as I understand it, they only have the database in common? So in theory the frontend could be placed on a seperate server? I moved my own to a linux server in under a day :) My laravel app and database does not share the same server.
But if it isnt feasable, then I think you just need to test it out. My guess is that it might not work a 100% the same a valet, so even if you get it working there it might give you issues in plesk.
Please or to participate in this conversation.