We have a Moodle running which I’ve recently moved to a new server. The old server was running PHP 5.2.x and the new one is on 5.3.x. This of course now means that the PHP extension php_mssql.dll is no longer supported, so the link to my SQL users database was not working, and nobody could login. Infact they would just see a blank white page, which didn’t really help anybody.
To get as much reporting as possible, I changed a few options:
- In php.ini, set display_errors to ON
- In Moodle set Debug ADOdb to ON (Debug ADOdb connection to external database – use when getting empty page during login. Not suitable for production sites.)
Once this was changed it became apparent that MSSQL was the issue, from there I checked phpinfo(); to find it hadn’t loaded, and that in turn led me to Google when I realised I wasn’t going to be easy to make this work. I read a few blogs on other people getting this working, with self-compiled versions of php_mssql but I couldn’t get this working myself and so looked for other options.
I initially went down the route of installing php_sqlsrv, which I got working fairly easily, only to find that there was no easy way to then select this from within Moodle.
After that, I decided to create an ODBC connection to MSSQL and then in Moodle use the ODBC connection for user authentication. This was fairly straight forward. The first thing to note is that there are both 32 and 64 bit versions of the ODBC Data Source Manager. I initially used the standard link from admin tools, which turned out to be the 64bit option. From Moodle I then entered the following database details:
- Host: DSN Name
- Database: odbc_mssql
- Use Sybase quotes: Yes
- DB Name:
- DB User: SQL Username
- Password: SQL Password
- Table: [Linkedserver].Database.dbo.vw_VIEW
- Username field: username
- Password field: password
- Password format: MD5 hash
- External db encoding: utf-8
- SQL setup command
- Debug ADOdb: No (Yes while testing)
- Password-change URL:
This all seemed OK, but I kept getting the error:
The specified DSN contains an architecture mismatch between the Driver and Application
A bit Googling took me here which revealed that I should be using the 32bit version of the ODBC Data Source Manager. Once I ran it in 32bit mode it all worked perfectly, and I didn’t have to muck around rewriting the PHP code to use SQLSRV.