The following warnings occurred:
Warning [2] Undefined variable $unreadreports - Line: 26 - File: global.php(961) : eval()'d code PHP 8.1.2-1ubuntu2.14 (Linux)
File Line Function
/global.php(961) : eval()'d code 26 errorHandler->error
/global.php 961 eval
/printthread.php 16 require_once



UserSpice
[Howto] Connect Microsoft Azure MySQL with SSL - Printable Version

+- UserSpice (https://userspice.com/forums)
+-- Forum: Support Center (https://userspice.com/forums/forumdisplay.php?fid=23)
+--- Forum: UserSpice 4.4 (https://userspice.com/forums/forumdisplay.php?fid=27)
+--- Thread: [Howto] Connect Microsoft Azure MySQL with SSL (/showthread.php?tid=1426)



[Howto] Connect Microsoft Azure MySQL with SSL - stonez56 - 04-09-2019

Hi all,
This is my first post! Smile
Thanks for this great PHP user management framework. 
It saved me a lot of time to management users in my PHP application!

For about a week, I tried to figure out how to connect MS Azure MySQL with SSL.
Finally, I got it worked.  And I am very happy to share this with you!

Software versions:
  • UserSpice V4.4
  • Azure MySQL 5.7.24-log (MySQL Community Server (GPL)
  • OS: Win64

1) In /users/init.php,  update correct Azure Mysql User name, password, db name, and password:
Code:
// Set config
$GLOBALS['config'] = array(
    'mysql'      => array(
'host'         => 'yoursystem.mysql.database.azure.com',
'username'     => 'mysqladm@yoursystem',
'password'     => 'your_password',
'db'           => 'your_db',
),
'remember'        => array(
 'cookie_name'   => 'ejiasdf93asdfj',
 'cookie_expiry' => 604800  //One week, feel free to make it longer
),
'session' => array(
 'session_name' => 'user',
 'token_name' => 'token',
)
);

2) Download CA certifications here (BaltimoreCyberTrustRoot.crt.pem) from here and save it in your website.
   In my case, I saved this file at d:\home\site\wwwroot\




3) Then, in /users/classes/DB.php, 
     add two lines, in PDO connects as options around LINE 31:

    PDO::MYSQL_ATTR_SSL_CA => "d:\home\site\wwwroot\BaltimoreCyberTrustRoot.crt.pem",
    PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false

Code:
class DB {
    private static $_instance = null;
    private $_pdo, $_query, $_error = false, $_errorInfo, $_results=[], $_resultsArray=[], $_count = 0, $_lastId, $_queryCount=0;

    private function __construct($config = []){
        if (!$opts = Config::get('mysql/options'))
            $opts = array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET SESSION sql_mode = ''",

            PDO::MYSQL_ATTR_SSL_CA => "d:\home\site\wwwroot\BaltimoreCyberTrustRoot.crt.pem",
            PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false
                            
                            );
        try{
            if($config == []){
                $this->_pdo = new PDO('mysql:host=' .
                    Config::get('mysql/host') .';dbname='.
                    Config::get('mysql/db') . ';charset=utf8',
                    Config::get('mysql/username'),
                    Config::get('mysql/password'),
                    $opts);
            }......more codes.... below

Then, you should be able to connect to Microsoft Azure MySQL with SSL enabled! 

Hope this is helpful to you!

Stonez


RE: [Howto] Connect Microsoft Azure MySQL with SSL - mudmin - 04-09-2019

Wow. That's fantastic. I will add this to the documentation. THANK YOU!


RE: [Howto] Connect Microsoft Azure MySQL with SSL - stonez56 - 04-10-2019

(04-09-2019, 04:08 PM)mudmin Wrote: Wow. That's fantastic. I will add this to the documentation. THANK YOU!

I'm glad that this is useful to someone and thanks for the great user management framework!