Trouble with Moodle & MSSQL

Trouble with Moodle & MSSQL

by Dion Baker -
Number of replies: 18

Hello,

I was hoping to get some help with installing moddle.

Currently we have moodle running on a WindowsXP box using Apache and MySQL, but for certain reasons, I have been assigned to get a new moodle running, on a different server, under IIS and MSSQL.

After some fiddling, I've gotten php working on IIS, but am currently having trouble in the moodle install. That install.php page will come up without a problem, and I can move through it until it comes to the database configuration page.

When I set this up, originally I used FreeTDS (php_dblib.dll), but kept getting the error "PHP has not been properly configured with the MSSQL extension so that it can communicate with SQL*Server. Please check your php.ini file or recompile PHP." when I hit next. Everything looked okay in php.ini etc. and I decided to try ODBC, but again with no luck (I tried a few different settings with ODBC, as the install instructions on http://docs.moodle.org/en/Installing_MSSQL_for_PHP were rather minimal in the ODBC section).

When I have ODBC running, I woukld get a different message: "We could not connect to the database you specified. Please check your database settings."

I'm starting to run out of ideas, so I was hoping for some help / ideas from others. I'm rather new to this, but have a basic knowledge and understanding to be able to work with it.

The info is as follows:

Microsoft Server 2003 (running on a VM) with the latest updates & SPs
PHP version: 5.2.6
IIS 6.0
MSSQL version: SQL Server Express Edition 2005 (Using Managment Studio Express 2005)
FreeTDS - DotNet 2.0 and 3.0 are installed, and I have tried msvcr71.dll instead.

I can attach the php.ini in another post if requested,

Cheers,

Average of ratings: -
In reply to Dion Baker

Re: Trouble with Moodle & MSSQL

by Richard Enison -
DB,

Just wanted to confirm that you have the

extension=php_db.dll

line in your active php.ini file and you have commented out (with a semicolon in front) the

extension=php_mssql.dll

line. Are you sure you have the correct version of php_db.dll for PHP 5.2.x? And are you sure the changes were made to the active php.ini file, and that you restarted Apache after making the changes?

RLE
Average of ratings: -
In reply to Richard Enison

Re: Trouble with Moodle & MSSQL

by Dion Baker -

Yes, I do have the php_mssql.dll commented out (I followed the instructions pretty much perfectly, several times from scratch >.<) but as for the php_db.dll, there isn't a line in the php.ini for it (extension=php_db.dll) and there isn't a php_db.dll file in C:\PHP\ext.

There are however, extensions php_dba.dll, php_dbase.dll and php_dblib.dll.

Edit: I just realised to check PECL. I haven't used any of the PECL extensions as I only wanted to use the basic extensiosn that are needed at first, and when I had PECL before, php would not work. I have just put in php_db.dll from PECL and am bouncing to see what happens, will let you know.

Cheers.

Average of ratings: -
In reply to Richard Enison

Re: Trouble with Moodle & MSSQL

by Dion Baker -

I have tried using php_db.dll (I added extension=php_db.dll into php.ini) and no luck on that one.

Average of ratings: -
In reply to Dion Baker

Re: Trouble with Moodle & MSSQL

by Richard Enison -
DB,

Sorry, I meant php_dblib.dll. That's the FreeTDS extension to use instead of php_mssql.dll. Are you sure you have the right one for PHP 5.2.x? All three have exactly the same size in bytes, as far as I can tell, so you can't tell by that.

RLE
Average of ratings: -
In reply to Richard Enison

Re: Trouble with Moodle & MSSQL

by Dion Baker -

RE,

Yes, I've been trying with that, and with no luck. On http://docs.moodle.org/en/Installing_MSSQL_for_PHP#Using_FreeTDS_on_Windows it has the direct links to 5.1.x, 5.2.x, and 6.x, I'm using 5.2.6, so I used the 5.2.x .dll (http://kromann.info/php5_2-Release_TS/php_dblib.dll) with no luck. I also did use the freetds.conf file, on the root of C:\

I do not have the .NET Framework 1.1, but I do have 2.0 and 3.0 installed, so thought that shouldn't be a problem. I can't install 1.1 without removing both 2.0 and 3.0, so I also tried using Franks .dll, which you can use instead of having .NET 1.1 installed, but no luck with that.

I'm rather lost for ideas here, as I've gone over the documents many times, making sure not to have missed anything, and trying different configurations (eg. in freetds.conf, instead of using 127.0.0.1 as the IP, using the static IP of the machine (in this case 10.1.0.220). I also made sure that SQL etc. had access through the firewall, as well as port 1433 (but TCP and UDP), and even went as far as turning off the firewall for testing.

I'm thinking of trying the install with Moodle 1.7 instead of the latest 1.9 to see if that makes any difference, as the MSSQL with PHP manual was written for "Moodle 1.7 (and upwards)".

Average of ratings: -
In reply to Dion Baker

Re: Trouble with Moodle & MSSQL

by Richard Enison -
DB,
  1. I would think that .NET 2.0 and 3.0 should be at least as good as 1.1, but I don't know for sure.
  2. I hope your realize that your machine's "static IP" of 10.1.0.220 is not a public Internet static IP, because it is in the range of IP's that are reserved for local network (LAN) addresses. But that's OK. You should be able to use either one, I would think.
  3. Unless you have already been using Moodle 1.7, please don't install it. It is recommended not to install it, and in upgrading Moodle from a version lower than 1.7, to skip 1.7.
  4. I presume when you say "no luck", you just mean you have tried installing Moodle after going through the process of trying installing FreeTDS. Have you tried any testing outside of Moodle? For starters, try phpinfo. See if it indicates PHP support of MSSQL via FreeTDS is installed.
  5. Here's a (slightly modified) script I found in an old forum post. It issues the same test that Moodle (now) uses to test for installation of a PHP extension for MSSQL:

    <?php
    if (function_exists('mssql_connect')) {
    echo "mssql_connect function exists<br>";
    }
    echo "End of test.";
    ?>

  6. Try the FreeTDS Troubleshooting section.
  7. If worse comes to worse, and you just can't get FreeTDS to work, why not try ODBTP?
RLE
Average of ratings: -
In reply to Richard Enison

Re: Trouble with Moodle & MSSQL

by Dion Baker -

RLE,

I'm thinking about uninstalling .NET 2.0 and 3.0, and installing JUST 1.1 to see if any difference is made, although I'm not sure if it will make any difference.

I'm setting up moodle inside the school, as the Intranet. We've had moodle running on a windows XP box with Apache and MySQL for the last couple of years, purely as an intranet site. We're not worried about the IP range we're using because it's just a LAN, not for internet access.

Looks like 1.7 isn't a good idea then?

Sorry, my language is a bit funky sometimes (We Australians talk wonky, see? smile ), so yes, by "no luck" I mean that I've tried & tested, to no avail. I used the test page on the readme for installing PHP under IIS, and everything seemed ok.

Most of my testing I've done, trying to get it to work, has been using FreeTDS, but I've also used ODBC as I mentioned in the original post, and while Moodle could talk to it, I would get a message saying it could not connect to the database. I did give ODBTP a go, but I must admit, I did not go very far with it.

I'm thinking I'll give that another try. Something else I'm looking at is using FastCGI with IIS instead of ISAPI, and also unistalling .NET 2.0 and 3.0, and installing 1.1.

Average of ratings: -
In reply to Dion Baker

Re: Trouble with Moodle & MSSQL

by Richard Enison -
DB,

As I said, one would think 2.0 and 3.0 would be good if 1.1 is, but you never know. So if you're willing to try just 1.1, I'm not going to try and stop you.

No problem that you're on a LAN intranet. I just wanted to make sure you understood that the IP you listed was not an Internet static IP.

Yes, Moodle 1.7 is bad news.

I didn't have a problem understanding the meaning of "no luck" in a general sense. I just wanted to pin down specifically the kind of test that you tried and that failed.

I've read good things about FastCGI with IIS. Hope it works out!

RLE
Average of ratings: -
In reply to Dion Baker

Re: Trouble with Moodle & MSSQL

by Dion Baker -

So after much stuffing around, I've made a little headway. By little I mean I tiptoed forward a bit.

I went ahead and did everything from scratch, again, with a few little changes with extensions, MSSQL settings, IIS settings, etc.

First I tried with FastCGI, and had a lot of trouble getting it to work, and when I delved even deeper into FastCGI, decided that PHP seems to work perfectly fine under ISAPI & IIS, so I might just go down that path last.

Anyway. I went from scratch again, and went to use ODBTP (using the instructions on the "Installing MSSQL for PHP" page) and they say "If [the] downloaded package isn't present the extension matching your PHP platform/version, you should build if from source files. To do that, just "configure, make, make install". That will create some stuff under "/usr/local". "

Not exactly easy on an Microsoft OS, so I'd also prefer not to go down that path. There are pre-made files 4.x, 5.0.x and 5.1.x, so I'm thinking about giving it a go with PHP 5.1.x, as that will be easy to swap over 5.2.x.

Anyway, the message I'm now getting is "We could not connect to the database you specified. Please check your database settings." when I try to access MSSQL in the Moodle install. (Picture attached)

All other MSSQL options in the "Type" drop down list give me the same error.

Host Server is 127.0.0.1, because the db is on local host, but I've also tried 10.1.0.220, as well as 127.0.0.1\SQLEXPRESS (this is what is displayed in the MSSQL Manager when you connect) and 10.1.1.220\SQLEXPRESS, and [Server Name] & [Server Name]\SQLEXPRESS.

I've even gone as far as disabling the firewall altogether to make sure it's not blocking it. So, I'm pretty sure it's a problem with my FreeTDS setup now, but can't be sure.

Thanks for your help sofar RE =), and thanks for being so patient so far >.<

Average of ratings: -
In reply to Dion Baker

Re: Trouble with Moodle & MSSQL

by Richard Enison -
DB,

I think the error you're getting now is, as you put it, a tiptoe forward from the one you got before. Because the previous one said PHP doesn't even have a way of interacting with MSSQL at all. Now it seems to be saying it can do that, but it just has a problem connecting to the particular database you named, on the host you named, using the username you named and the p/w you entered. Make sure they are all correct (especially the p/w!)

To confirm this, I think if you try phpinfo, it will show the MSSQL extension as being installed. And if you run the little PHP script in my post of about 26 and a half hours ago, it will indicate the same. Provided you have the odbtp service (and MSSQL of course!) running. You might need to start it up every time you reboot, or go into Windows Services and set it to automatically start.

EDIT: There is some weirdness in the docs that come with odbtp (like the many README files), but the Moodle doc page seems to have fixed most of these, with one possible exception: where it says to add to php.ini

extension=php_odbtp.dll

this may be a typo for

extension=php_odbtp_mssql.dll

So try it both ways, and let us know which one works better (since I don't have MSSQL, I can't try it myself). If it is a typo, I'll fix it (unless you fix it first!). Of course, it goes without saying that the lines for php_mssql.dll and php_dblib.dll should be commented out if you are using odbtp instead of FreeTDS.

RLE
Average of ratings: -
In reply to Richard Enison

Re: Trouble with Moodle & MSSQL

by Dion Baker -

I'm yet to try what you've suggested (I'm a bit busy with other problems. Sometimes the network admin has to juggle many jobs at once =() but I would like to ask about one thing.

I recieved a PM from another member called Luis de Vasconcelos with the following:

Hi Dion. I saw your "Trouble with Moodle & MSSQL" post in the Moodle forum. If you're still having problems setting up Moodle have a look at http://moodlewindows.k-net.co.za for full instructions on how to setup Moodle on the Windows platform.

 I'm a bit usure about the URL (It's the South Africa extension) and there's no page coming up under that URL. I find it a bit weird that the person replied privatly with that info rather than post on the thread, so is anyone able to vouch to say that this site it legit?

Cheers,

Average of ratings: -
In reply to Dion Baker

Re: Trouble with Moodle & MSSQL

by Dion Baker -

Whoops, ya'll just go and ignore that post.

I checked a google cache version of the URL, and looked at the users profile, and realised he's a rather active member of the forum. I can't delete / edit the last post, so I'll just go ahead and apologise for my mistrust. If what I'm about to try doesn't work, I'll go ahead and read through his site properly (chached or live if it's up yet) and see what happens.

DB

Average of ratings: -
In reply to Dion Baker

Re: Trouble with Moodle & MSSQL

by Luis de Vasconcelos -

LOL! I'll remember this next time I PM somebody... big grin

PS I sent you a PM instead of posting the link on the forum because somebody in the Moodle community is a bit upset that I created that website and doesn't want me to "advertise" it on the forum...

Average of ratings: -
In reply to Dion Baker

Re: Trouble with Moodle & MSSQL

by Dion Baker -

Hello All,

So I've finally found my problem, and as you can assume, it's simple but obscure. Euch.

When you install Microsoft SQL Server Express 2005 (NOT the standard Microsoft SQL 2005) you have a choice between default install and custom install.

Now, if you choose custom install, it will install so that SQL is accessed through the path [servername]\SQLEXPRESS, instead of the usual [servername]. For some reason, if you have it installed as such, when you type in the host server (Either [localhost/127.0.0.1]\SQLEXPRESS or [servername]\SQLEXPRESS, depending on your setup) it will not "talk" to SQL. If you tell it to install with the path [servername] (Aka no \SQLEXPRESS after it), then there is no problem, you just put localhost or [servername] in the Host Server, and viola, it will work.

I have no idea why, as I'm not coder or expert, but that seemed to be the problem. I tested it a few times (set it up from scratch, using localhost\SQLEXPRESS, and using just localhost) and determined it was this that was causing the problem.

It's all installed now, a nd running perfectly.

Now I just have to migrate data from the MySQL Apache Moodle to the MSSQL IIS moodle. Wish me luck, and thanks for the help RE! smile

As a post note, the URL that Luis gave me was fantastic, as it outlined every tiny little step. It would be brilliant if this was put on the Docs, or a link to the URL somewhere in the docs, as this is a brilliant all in one guide for those wanting to do a completely Microsoft run version of Moodle.

Cheers,

DB

Average of ratings: -
In reply to Dion Baker

Re: Trouble with Moodle & MSSQL

by Luis de Vasconcelos -

Thanks! blush There is a link to the moodlewindows setup guide on: http://docs.moodle.org/en/Installation_for_Windows_2003_with_IIS and on a few other pages on moodledocs.

I haven't had a chance to update it yet and it is actually a bit out of date - I wrote it before Microsoft released FastCGI. These days I prefer to run PHP on FastCGI instead of the php5isapi.dll module - performance seems to be better.

Also, it's missing the ODBTP setup details, so I need to add it sometime...

Average of ratings: -
In reply to Dion Baker

Re: Trouble with Moodle & MSSQL

by Luis de Vasconcelos -

> Now I just have to migrate data from the MySQL Apache
> Moodle to the MSSQL IIS moodle.

As a matter interest, why are you moving from MySQL and Apache onto MSSQL and IIS?

Average of ratings: -
In reply to Luis de Vasconcelos

Re: Trouble with Moodle & MSSQL

by Dion Baker -

Once again Luis, sorry about the mistrust, just when someone PMs me a link to a site outside of the site, I just want to be careful, but it was very helpul, pretty much saved me arse! =)

> Thanks! blush There is a link to the moodlewindows setup guide on: http://docs.moodle.org/en/Installation_for_Windows_2003_with_IIS and on a few other pages on moodledocs.

Have those links been added recently? I didn't noticed them when I used the pages before, but they're there now mixed

> I wrote it before Microsoft released FastCGI. These days I prefer to run PHP on FastCGI instead of the php5isapi.dll module - performance seems to be better.

I must say that by the way many people talk about FastCGI, it does sound like it's well worth using. I did try to use it at one point, but had a few problems, and due to my lack of knowledge when it comes to PHP & IIS etc. I decided to go back to what I had already learned, and what I knew worked etc, that being ISAPI.

> As a matter interest, why are you moving from MySQL and Apache onto MSSQL and IIS?

To tell the truth, I'm not sure myself. Where I work, there's two IT people and the IT Teacher. The IT teacher set up moodle with Apache and MySQL a while ago on an XP box, which started off as a test, and pretty quickly blew out into a full grown site. The other IT bloke, who's my supervisor/boss has said that he wants it on MSSQL and IIS, and I sort of just do it no questions asked. I think that he wants that because we have one server that hosts a few internal pages seperate from the Moodle box, on IIS, and we already have a server with the full version of MSSQL 2005 on it (It's for Altiris).

Also, when it comes to IIS and MSSQL, if it's that software that breaks, the support is a bit better than the open source community (In this case it is. We pay a bit of a premium for that.)

I think the boss has other reasons, but never really asked, he just wants it done.

> because somebody in the Moodle community is a bit upset that I created that website and doesn't want me to "advertise" it on the forum

Personally, I think that's just ludicrous. As someone who has just set up Moodle on IIS & MSSQL, I can safely say that the current docs on Microsoft setup are, well, lacking to say the least. It's sort of a mish mash, with some being very detailed, and other sort of just telling you what to do, but not how to do it. If the person in question doesn't think that an all in one guide for a full MS setup isn't worthwhile, well ... lets not go there =)

tl;dr, the guide is good, and should replace the current MS docs (or sit alongside, so you can see the other options such as ODBTP that you don't cover)

Average of ratings: -
In reply to Dion Baker

Re: Trouble with Moodle & MSSQL

by Richard Enison -
DB,

Finding out when a link (or anything else) was added to a Moodle doc (Wiki) page is the easiest thing in the world. For example, going to the Installation for Windows 2003 with IIS page and clicking on the history tab, I did an interval-bisection search (approximately) and found that the moodlewindows link you reference was added July 13 by (would you believe) LdV himself. The previous change, BTW, was made by yours truly. Small world, ain't it?

Frankly, I for one want to see all the documentation on how to successfully install Moodle on MS platforms as possible. I see so many posts in these forums from people trying unsuccessfully to do that, and I try to refer them to all the standard docs, and they say it still doesn't work. What do I tell them???

RLE
Average of ratings: -