Unable to connect PHP to MSSQL database - Moodle installation

Unable to connect PHP to MSSQL database - Moodle installation

by John Knight -
Number of replies: 9

Hi,


I am trying to install Moodle 3.0.2 with PHP 7 on a Windows 2012 server with IIS connecting to a MSSQL 2012 database.


I have got PHP working and I can view the Moodle install pages but I am unable, it seems, to get PHP 7 to connect to the database. I have created the database per instructions.


The error I get after completing the database properties is:

Error: database driver problem detected

The site administrator should verify server configuration

Microsoft Drivers for SQL Server for PHP are not installed or not configured properly.

This is understandable as I am unsure how to get PHP to connect. The Microsoft drivers only seem to go up to PHP version 5.6 and FreeTDS doesn't seem to support PHP7 as far as I can see.

I am hoping that someone has connected PHP 7 with MSSQL and can help me please.

Many thanks,


John

Average of ratings: -
In reply to John Knight

Re: Unable to connect PHP to MSSQL database - Moodle installation

by Usman Asar -

John, there is an extension in PHP called pdo_mysql, where PDO stands for PHP Data Object, that is purposely built to formalize the DB connections by creating a uniform interface, so be it SQL Lite, Oracle or SQL Server, shouldn't have any trouble connecting.

Have you got ODBC drivers installed? alternatively use WPI bundled with IIS, and see if updated drivers for PHP 7 is available.

Or direct link for MS ODBC Drivers for PHP can be found HERE.

Also you may want to have a look at PHP 7 Drivers for MS SQL, though I believe written for Azure, but will work, HERE.

Average of ratings: -
In reply to Usman Asar

Re: Unable to connect PHP to MSSQL database - Moodle installation

by John Knight -

Hi Usman,

Thanks for your reply.

I have only just come across the release notes for 3.0.2 where it states that PHP 7 is not compatible. The other documents I had been using did not make mention of this so looks like I was trying to make something work that won't!

I have also done some reading around the SQL Driver for PHP 7 and it looks like this is still not available in a fully working condition, at least for 64-bit environments. (https://github.com/Azure/msphpsql/issues/58)

Using the WPI was something I wasn't too keen on, in fact it thinks I have a couple of versions of PHP installed already which I don't!

I am going to try using PHP 5.6.17 with the Microsoft driver php_sqlsrv_56_nts.dll - hopefully this will work. Having read various documents it would appear the installing Moodle from the ground up should be straight forward - I hope it is!

Thanks again,

John

Average of ratings: -
In reply to John Knight

Re: Unable to connect PHP to MSSQL database - Moodle installation

by Usman Asar -

John, give a try to technical review as update 4 days ago, PHP 7 drivers for SQL Server, HERE.

For Installing Moodle on Windows Server and IIS, I recently created video tutorial, you can find it HERE. I do recommend watching both videos, but following method in second video, using PHP Manager (an extension to IIS for managing PHP), after watching this, you MAY want to change your mind from SQL Server to MariaDB, but again choice remains yours, for I don't doubt SQL Server's performance.

Average of ratings: -
In reply to Usman Asar

Re: Unable to connect PHP to MSSQL database - Moodle installation

by John Knight -

Thanks Usman, I shall watch the videos.

We are unable to switch RDBMS platform, we work with both Oracle and MSSQL customers and are not in a position to force customers to install an additional database platform (nor would we expect them too).

I reverted to PHP 5.6.17 and after having to install both x86 and x64 version of C++ runtime 2012 and also ODBC drivers for SQL Server 2012 I have progressed past the point of previous failure.

I now am faced with another issue;

Exception - Failed to write cache file "[localcachedir]/mustache/-1/clean/__Mustache_b836275c8c636708cf9a37ee0d079617.php".

Will now search the forum here for any clues - is it just me or is Windows, IIS, MSSQL and PHP not particulars easy to configure for Moodle?

Thanks again for your help and suggestions.

Average of ratings: -
In reply to John Knight

Re: Unable to connect PHP to MSSQL database - Moodle installation

by Usman Asar -

John, looks like permissions error, have you given correct permissions to moodledata folder?

I haven't heard anything creating issues with MS SQL Server, Oracle does popped out few, but definitely not  SQL Server.

Also, sometimes purging caches does help, for that, logged in as admin, use URL

 www.mymoodle.com/admin/purgecaches.php

see if that helps.

Average of ratings: -
In reply to Usman Asar

Re: Unable to connect PHP to MSSQL database - Moodle installation

by John Knight -

Hi Usman,

With regards to the moodledata folder, I gave permissions as per the documentation (Share with EVERYONE with read rights, Grant IUSR write permission) and had to change this to EVERYONE read/write and ISUR read/write as the install/configuration process complained of not being able to write files. The one I recall was after the Copyright screen which is when I had to give ISUR read permission.

I got as far as having to upgrade all the plug-ins which looked like it went okay but then the site hung and I couldn't get past that screen. The website is now unresponsive and I can't get any page to load.

I've been pulling my hair out with this, I am reluctant to give up on it having spent days getting this far but will see what my bosses say.

Cheers,

John

Average of ratings: -
In reply to John Knight

Re: Unable to connect PHP to MSSQL database - Moodle installation

by Usman Asar -

John, if installation started, it shouldn't have halted like this, IUSR permissions are correct. now I am assuming you must have left something in PHP settings particularly for IIS, as Apache's and IIS's are different.

These are the settings I use to initialize PHP forMoodle

error_log="C:\php\php56\temp\"
upload_tmp_dir="C:\php\php56\temp\"
session.save_path="C:\php\php56\temp\"
cgi.force_redirect=0
cgi.fix_pathinfo=1
fastcgi.impersonate=1
fastcgi.logging=0
max_execution_time=300
date.timezone=Europe/London
input_max_vars = 2000
extension_dir= "C:\php\php56\ext\"

Rather than getting frustrated, give sometime and follow video, you may be missing something.

Average of ratings: -
In reply to Usman Asar

Re: Unable to connect PHP to MSSQL database - Moodle installation

by Salman Zaidi -

Hi Usman

Can you please guide or help what thing i have missed out. much appreciate.

I am facing problem in external db authentication. Moodle is installed on Maria DB (MySQl) and i need external mssql server authentication

I have setup every thing according to document. Here is my detail:

1. Windows 10 64 bit

2. MSSQL Server 2005 64 bit

2. PHP 5.6 Installed

3. Moodle 3.1

4. I have configured MS SQL Server driver dlls for php 5.6 and tested them via phpinfo.php
5. I made odbc DSN tested it from there succuessfully.

6. But when i set up all parameters in external db plugin according to your advice but it gives me error as below

Warning: odbc_connect(): SQL error: [Microsoft][ODBC Driver
Manager] Data source name not found and no default driver specified, SQL
state IM002 in SQLConnect in C:\xampp\apps\moodle\htdocs\lib\adodb\drivers\adodb-odbc.inc.php on line 66

(odbc_mssql): SET CONCAT_NULL_YIELDS_NULL OFF

Warning: odbc_exec() expects parameter 1 to be resource, boolean given in C:\xampp\apps\moodle\htdocs\lib\adodb\drivers\adodb-odbc.inc.php on line 542

IM002: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

lmp64: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Cannot connect the database


Attachment sample.jpg
Average of ratings: -
In reply to Salman Zaidi

Re: Unable to connect PHP to MSSQL database - Moodle installation

by Usman Asar -

Salman, have you mentioned in your config.php file about the location of DB as well?

Average of ratings: -