Need to connect to self-signed MS SQLServer

Need to connect to self-signed MS SQLServer

by 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!




                        

    
    
    
                            
    
    
                                

                                        
Average of ratings:  - 

                                        
                                

    
                            
                    

                

            

        

    
        

                

                    

                        
                
                        

                            

                                        

                                                
                                        

                                

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

                                        

                                            by Leon Stringer - 
                        

                                

                
                                        

                                                        Picture of Core developers
                                                        Picture of 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.

                                    

                
                
                
                                        
                
                
                                            

                                                    
Average of ratings:  - 

                                                    
                                            

                
                                        
                                

                            

                        

                    

                
        

                

                    

                        
                
                        

                            

                                        

                                                
                                        

                                

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

                                        

                                            by 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!

                                    

                
                
                
                                        
                
                
                                            

                                                    
Average of ratings:  - 

                                                    
                                            

                
                                        
                                

                            

                        

                    

                
        

                

                    

                        
                
                        

                            

                                        

                                                
                                        

                                

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

                                        

                                            by Howard Miller - 
                        

                                

                
                                        

                                                        Picture of Core developers
                                                        Picture of Documentation writers
                                                        Picture of Particularly helpful Moodlers
                                                        Picture of Peer reviewers
                                                        Picture of 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.

                                    

                
                
                
                                        
                
                
                                            

                                                    
Average of ratings:  - 

                                                    
                                            

                
                                        
                                

                            

                        

                    

                
        

        

                

        

                

                

                    

                        
                
                        

                            

                                        

                                                
                                        

                                

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

                                        

                                            by 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

                                    

                
                
                
                                        
                
                
                                            

                                                    
Average of ratings: Useful (1) 

                                                    
                                            

                
                                        
                                

                            

                        

                    

                
        

                

                    

                        
                
                        

                            

                                        

                                                
                                        

                                

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

                                        

                                            by 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!

                                    

                
                
                
                                        
                
                
                                            

                                                    
Average of ratings:  - 

                                                    
                                            

                
                                        
                                

                            

                        

                    

                
        

                
        

                

                

                    

                        
                
                        

                            

                                        

                                                
                                        

                                

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

                                        

                                            by 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

                                    

                
                
                
                                        
                
                
                                            

                                                    
Average of ratings:  - 

                                                    
                                            

                
                                        
                                

                            

                        

                    

                
        

                

                    

                        
                
                        

                            

                                        

                                                
                                        

                                

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

                                        

                                            by Ronald Vyhmeister - 
                        

                                

                
                                        

                                        

                            

                
                            

                
                                

                                    

                                        
This is awesome! Glad to see this available for future installs!

                                    

                
                
                
                                        
                
                
                                            

                                                    
Average of ratings:  -