Need to connect to self-signed MS SQLServer

Need to connect to self-signed MS SQLServer

由Ronald Vyhmeister發表於
Number of replies: 8

I'm setting up a test server to migrate from 3.9 to 4.1... We use MS SQL for the server, but for testing I need to run my own DB server, which I'm doing, using a self-signed certificate.

The problem is that the SQL driver requires signing... so I get this error:

SQLState: 08001
Error Code: -1
Message: [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: [error:1416F086:SSL routines:tls_process_server_certificate:certificate verify failed:self signed certificate]
SQLState: 08001
Error Code: -1
Message: [Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection

For another type of similar situation, the solution indicated at https://moodle.org/mod/forum/discuss.php?d=434849 is to add a parameters line 

$extdb->setConnectionParameter('TrustServerCertificate', 'yes');
I've added the line
$authdb->setConnectionParameter('TrustServerCertificate', 'yes'); 
in the following places:
auth/db/auth.php line 161
enrol/database/lib.php line 860
How can I pass that parameter? I may be the first, but I'm certain I won't be the last to try this... 
Thanks for your input!




                        

    
    
    
                            
    
    
                                

                                        
評比平均分數： - 

                                        
                                

    
                            
                    

                

            

        

    
        

                

                    

                        
                
                        

                            

                                        

                                                
                                        

                                

                                        In reply to Ronald Vyhmeister
                                        
Re: Need to connect to self-signed MS SQLServer

                                        

                                            由Leon Stringer發表於
                        

                                

                
                                        

                                                        Core developers的相片
                                                        Particularly helpful Moodlers的相片
                                        

                            

                
                            

                
                                

                                    

                                        
I don't have a SQL Server environment to try this but looking at the code perhaps this will work:


Edit lib/dml/sqlsrv_native_moodle_database.php inserting line 225 below:


 223           'ConnectionPooling' => !empty($this->dboptions['dbpersist']),
 224           'ReturnDatesAsStrings' => true,
 225           'TrustServerCertificate' => !empty($this->dboptions['trustservercertificate']),
 226          ));


Then edit config.php adding 'trustservercertificate' => true to $CFG->dboptions.

                                    

                
                
                
                                        
                
                
                                            

                                                    
評比平均分數： - 

                                                    
                                            

                
                                        
                                

                            

                        

                    

                
        

                

                    

                        
                
                        

                            

                                        

                                                
                                        

                                

                                        In reply to Leon Stringer
                                        
Re: Need to connect to self-signed MS SQLServer

                                        

                                            由Ronald Vyhmeister發表於
                        

                                

                
                                        

                                        

                            

                
                            

                
                                

                                    

                                        
OK... Did this... the problem I think we have is that this is during the install, so there is no config.php yet...
Any suggestion for making this work before the config.php exists?
Thanks!

                                    

                
                
                
                                        
                
                
                                            

                                                    
評比平均分數： - 

                                                    
                                            

                
                                        
                                

                            

                        

                    

                
        

                

                    

                        
                
                        

                            

                                        

                                                
                                        

                                

                                        In reply to Ronald Vyhmeister
                                        
Re: Need to connect to self-signed MS SQLServer

                                        

                                            由Howard Miller發表於
                        

                                

                
                                        

                                                        Core developers的相片
                                                        Documentation writers的相片
                                                        Particularly helpful Moodlers的相片
                                                        Peer reviewers的相片
                                                        Plugin developers的相片
                                        

                            

                
                            

                
                                

                                    

                                        
You don't need to run install to create config.php.   Copy config-dist.php to config.php and then edit the resulting file with your site parameters (it's very well commented). The installation will start after the bit were config.php would normally be created.

                                    

                
                
                
                                        
                
                
                                            

                                                    
評比平均分數： - 

                                                    
                                            

                
                                        
                                

                            

                        

                    

                
        

        

                

        

                

                

                    

                        
                
                        

                            

                                        

                                                
                                        

                                

                                        In reply to Leon Stringer
                                        
Re: Need to connect to self-signed MS SQLServer

                                        

                                            由Matteo Scaramuccia發表於
                        

                                

                
                                        

                                        

                            

                
                            

                
                                

                                    

                                        
Hello Everyone,
4.2 is addressing the use case with this extra configuration setting, https://github.com/moodle/moodle/blob/f3bf17cdfbee0377f5a09fcbbcce7c0367bbe58b/config-dist.php#L81-L84, done with https://github.com/moodle/moodle/commit/f8537ffec6c3248525cc1322e3aaa2ebd64bea0a via MDL-64153.


This new setting will be backported down to 4.1, 4.0 and 3.11: MDL-77669.


HTH,
Matteo

                                    

                
                
                
                                        
                
                
                                            

                                                    
評比平均分數：Useful (1) 

                                                    
                                            

                
                                        
                                

                            

                        

                    

                
        

                

                    

                        
                
                        

                            

                                        

                                                
                                        

                                

                                        In reply to Matteo Scaramuccia
                                        
Re: Need to connect to self-signed MS SQLServer

                                        

                                            由Ronald Vyhmeister發表於
                        

                                

                
                                        

                                        

                            

                
                            

                
                                

                                    

                                        
Glad to know it will be addressed... and thank you @Howard for pointing out the path to putting it in a config.php... I've crafted many a config.php in the past, so that was not bad at all!



Now, the struggles begin... it goes through the process and gets to the updating of the admin user, and it throws this error:



Error reading from database



More information about this error



And the error information goes nowhere, and I cannot see any error logs on the SQL Server... any ideas welcome!

                                    

                
                
                
                                        
                
                
                                            

                                                    
評比平均分數： - 

                                                    
                                            

                
                                        
                                

                            

                        

                    

                
        

                
        

                

                

                    

                        
                
                        

                            

                                        

                                                
                                        

                                

                                        In reply to Matteo Scaramuccia
                                        
Re: Need to connect to self-signed MS SQLServer

                                        

                                            由Matteo Scaramuccia發表於
                        

                                

                
                                        

                                        

                            

                
                            

                
                                

                                    

                                        
Hello Everyone,
MDL-77669 has been now integrated/tested/released and available in weeklies; it will be available even in 3.9:


    

  • 3.9.21
    • 

  • 3.11.14
    • 

  • 4.0.8
    • 

  • 4.1.3
    • 



HTH,
Matteo

                                    

                
                
                
                                        
                
                
                                            

                                                    
評比平均分數： -