Need to connect to self-signed MS SQLServer

Need to connect to self-signed MS SQLServer

Ronald Vyhmeister -
回帖数：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!




                        

    
    
    
                            
    
    
                                

                                        
平均分： - 

                                        
                                

    
                            
                    

                

            

        

    
        

                

                    

                        
                
                        

                            

                                        

                                                
                                        

                                

                                        回复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.

                                    

                
                
                
                                        
                
                
                                            

                                                    
平均分： - 

                                                    
                                            

                
                                        
                                

                            

                        

                    

                
        

                

                    

                        
                
                        

                            

                                        

                                                
                                        

                                

                                        回复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!

                                    

                
                
                
                                        
                
                
                                            

                                                    
平均分： - 

                                                    
                                            

                
                                        
                                

                            

                        

                    

                
        

                

                    

                        
                
                        

                            

                                        

                                                
                                        

                                

                                        回复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.

                                    

                
                
                
                                        
                
                
                                            

                                                    
平均分： - 

                                                    
                                            

                
                                        
                                

                            

                        

                    

                
        

        

                

        

                

                

                    

                        
                
                        

                            

                                        

                                                
                                        

                                

                                        回复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) 

                                                    
                                            

                
                                        
                                

                            

                        

                    

                
        

                

                    

                        
                
                        

                            

                                        

                                                
                                        

                                

                                        回复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!

                                    

                
                
                
                                        
                
                
                                            

                                                    
平均分： - 

                                                    
                                            

                
                                        
                                

                            

                        

                    

                
        

                
        

                

                

                    

                        
                
                        

                            

                                        

                                                
                                        

                                

                                        回复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

                                    

                
                
                
                                        
                
                
                                            

                                                    
平均分： -