Connecting to external Database

Connecting to external Database

by Otaku Dude -
Number of replies: 10

HIya,

We are exploring using a plugin for creating cohorts and enrolments to them (cohort external database). We are having trouble getting it to work, we can't get a connection between the 2 servers. Currently we are seeing this errror:

"external db ip: missing extension for mssql"

We are linking to ProSolution via mssql. Our Moodle:

Moodle: 4rc2

OS: Oracle linux 8.6

PHP: 7.3.20

Our IT staff have little expertise in Linux and are struggling a bit. We are not sure what extensions are missing and whether they are missing from our Moodle server or our ProSolution server.

Thank you,

Nige.


Average of ratings: -
In reply to Otaku Dude

Re: Connecting to external Database

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
What is says - you don't have the extension for mssql compiled into PHP. Fixing that depends on how PHP was installed.

I've never used mssql but I recall that linking PHP to it is less than straightforward. Could be better now.
Average of ratings: -
In reply to Howard Miller

Re: Connecting to external Database

by Otaku Dude -
Hello Howard,

Thank you for your response. OK, so I need to download and compile it like I did when I added php sodium? Something like this?

https://developpaper.com/linux-compiles-mssql-extensions-using-php-to-connect-sql-server-2008/

Thank you,

Nige.
Average of ratings: -
In reply to Otaku Dude

Re: Connecting to external Database

by Leon Stringer -
Picture of Core developers Picture of Particularly helpful Moodlers

That guide is very out of date, it's for the old mssql extension which I don't think is supported in PHP 7.0 or later. You'll need the sqlsrv extension.

I haven't done this for a while but I think this is what you need.

Average of ratings: -
In reply to Leon Stringer

Re: Connecting to external Database

by Otaku Dude -
Thank you Leon,

That link was very useful. I have now managed to install unixODBC-devl and installed and built sqlsrv and pdo_sqlsrv. However when I check to see if they are installed (php -m),
I get the following:

PHP Warning: PHP Startup: Unable to load dynamic library 'pdo_sqlsrv.so' (tried: /usr/lib64/php/modules/pdo_sqlsrv.so (/usr/lib64/php/modules/pdo_sqlsrv.so: undefined symbol: php_pdo_register_driver), /usr/lib64/php/modules/pdo_sqlsrv.so.so (/usr/lib64/php/modules/pdo_sqlsrv.so.so: cannot open shared object file: No such file or directory)) in Unknown on line 0

The instructions I found also said to edit php.ini:
extension=sqlsrv.so
extension=pdo_sqlsrv.so

Googling the above error, I found this which said to remove pdo_sqlsrv.so from php.ini:
https://github.com/microsoft/msphpsql/issues/736

Doing this still brings up the missing extension error in Moodle.

Thank you,

Nige.
Average of ratings: -
In reply to Otaku Dude

Re: Connecting to external Database

by Leon Stringer -
Picture of Core developers Picture of Particularly helpful Moodlers

Is the missing extension error occurring in your code or in Moodle core code?

If it's in your code make sure you're using the functions in the sqlsrv extension (e.g. sqlsrv_connect(), sqlsrv_query()), not the ones from mssql extension (e.g. mssql_connect(), mssql_query()) which were removed from PHP 7.0 onwards. If it's still not working, can you share a code snippet where the error occurs? Although if it's not Moodle's code we might not be able to help.

If it's in Moodle code, can you tell us what you're trying to set up and, importantly, any database type you've selected. A screenshot may help us better understand the scenario, crop or obscure any information you don't want to share.

Average of ratings: -
In reply to Leon Stringer

Re: Connecting to external Database

by Otaku Dude -
Hi,

The missing extension error is reported when running sync/schedule for the plugin:

Thank you,

Nigel.

Average of ratings: -
In reply to Otaku Dude

Re: Connecting to external Database

by Leon Stringer -
Picture of Core developers Picture of Particularly helpful Moodlers

Try changing the Database driver to "mssqlnative":

tool_cohortdatabase | dbtype: mssqlnative

I think that's the ADOdb term for the php-sqlsrv extension.

Average of ratings: -
In reply to Leon Stringer

Re: Connecting to external Database

by Otaku Dude -
Hi Lean,

Thanks for that, that was gamechanger smile I have now sorted the technical side of things. (incorrect odbc driver installed). I just now have a certification issue to resolve:

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

Many thanks,

Nige.
Average of ratings: -
In reply to Otaku Dude

Re: Connecting to external Database

by Leon Stringer -
Picture of Core developers Picture of Particularly helpful Moodlers

The error is hopefully self-explanatory: SQL Server is using a self-signed certificate that the PHP database driver can't verify. So based on this StackOverflow answer the options are 1) add the SQL Server's self-signed certificate to the trusted certificates store on the Moodle server, or 2) specify TrustServerCertificate=yes when connecting to the SQL Server server. For option 2 it doesn't look like there's any way to set this in core Moodle or in the plugin. You could try a code change inserting line 471 below to admin/tool/cohortdatabase/classes/sync.php:

            ⋮
469         // Connect to the external database (forcing new connection).
470         $extdb = ADONewConnection($this->config->dbtype);
471         $extdb->setConnectionParameter('TrustServerCertificate', 'yes');
472         if ($this->config->debugdb) {
            ⋮

I don't have an environment readily available so I haven't been able to test this, so this may not work.

Average of ratings: -
In reply to Leon Stringer

Re: Connecting to external Database

by Otaku Dude -
Hi Leon,

Thank you so much for doing that, it worked a treat!

Regards,

Nige.
Average of ratings: -