I'm attempting to use PHP 5.5 with FreeTDS for MSSQL data access on Windows 2008 R2 with moodle 2.5.1.
This page lists several binaries for download on supported versions http://docs.moodle.org/25/en/Installing_MSSQL_for_PHP#Using_FreeTDS_on_Windows
Has anyone attempted to compile a recent version of FreeTDS under VC11 (Visual Studio 2012)?
I'm attempting to follow these instructions http://docs.moodle.org/dev/Compiling_FreeTDS_under_Windows
Are there any generous early adopters that can save me some time and heartache?
Perhaps because somebody (Eloy?) has added a big warning message to: http://docs.moodle.org/25/en/Installing_MSSQL_for_PHP#Microsoft_Drivers_for_SQL_Server_for_PHP that says, "WARNING: This driver has known problems and is therefore not suitable for any Moodle production servers."
And MDL-37734 suggests that it "won't be supported" until Microsoft fixes a few things in their drivers.
That's exactly right, Luis...
"I did not use FreeTDS. Therefore, I advise not to use it either, and to skip or disregard any step related to its use."
I hope no one takes that advice on a production server!
I was able to build FreeTDS pretty quickly... but building PHP 5.5 source (with includes for FreeTDS) has proved a stumbling block for the minute.
My fallback is to use PHP 5.4 and wincache.
Yes, maybe that could be a reason and maybe I'm missing something here, but from what I understand, that warning was for driver version 2005. Also, about FreeTDS, just below the warning mentioned, it says: "You should use FreeTDS if you are installing an older version of Moodle" (meaning <2.0).
At Using the Microsoft SQL Server Driver for PHP, under section Install the SQL Server Driver for PHP it says, "On the web server, install SQL Server Driver for PHP (minimum version required is v1.1)", but that link doesn't work anymore, as version 1.1 is not available, but version 3.0, which is the one I linked to (http://www.microsoft.com/en-us/download/details.aspx?id=20098).
Anyway, a few years ago I used version 1.1 with MSSQL 2008 without any issues (other than those derived from my lack of experience installing Moodle under WISP), and from what I know, they're still working fine (yes, I have just checked the site). I guess I was lucky
The line: 'This driver is only supported in Moodle 2.0 and up. You should use FreeTDS if you are installing an older version of Moodle.' is included here: http://docs.moodle.org/20/en/Installing_MSSQL_for_PHP#Microsoft_Drivers_for_SQL_Server_for_PHP
But it is NOT included here: http://docs.moodle.org/25/en/Installing_MSSQL_for_PHP#Microsoft_Drivers_for_SQL_Server_for_PHP
Also to clarify. I have successfully installed moodle 2.5.1 with sqlsrv (version 3.0) connecting to MSSQL 2008 R2 in testing. I wasn't able to find any problems with it while poking around.
However, the advice for 2.5 stands? To use FreeTDS, Yes?
Based on the related issues to this: https://tracker.moodle.org/browse/MDL-37734
Yes, right, I missed the update in the new page. Back to FreeTDS then
Well I've ended up spending a day building FreeTDS 0.91.89 and PHP 5.5.1 under x86 vc11 nts.
Seems to be working OK. I also have PHP 5.5.x versions of php_sqlsrv.dll and php_pdo_sqlsrv.dll on the same server in case somebody settles this sqlsrv Vs FreeTDS debate and I end up on the wrong side.
Thanks for the comments.
This may help someone..
The Moodle 2.5 installation docs here: http://docs.moodle.org/25/en/Installing_MSSQL_for_PHP warns against using the Microsoft drivers for SQL Server for PHP as it has known problems and is therefore not suitable for production servers.
Issue: https://tracker.moodle.org/browse/MDL-37734 (Still current as of July 2013)
“This issue is about to describe the problem happening with current sqllrv moodle driver (using the MS driver for PHP) to use Moodle under SQL*Server databases.
Along the last months, it has been reported in various issues, in different places, and it's affecting more and more, while we add more transactions to code base.”
Workaround: (use FreeTDS instead, while this gets fixed)
Microsoft Drivers 3.0 for PHP for SQL Server provides a self-extracting executable file which provides thread-safe and non-thread-safe drivers for PHP 5.3 and 5.4. http://www.microsoft.com/en-gb/download/details.aspx?id=20098. A Microsoft employee has released version 3.0.2 for PHP 5.5 here: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/e1d37219-88a3-46b2-a421-73bfa33fe433/unofficial-php-55-drivers-x86
The Moodle 2.5 installation docs here http://docs.moodle.org/25/en/Installing_MSSQL_for_PHP list various FreeTDS binaries for download from PHP version 5.2.x --> 5.4.x. No binaries exist for 5.5 on that page for download. To use PHP 5.5 a custom build is required.
Building PHP 5.5.1 with FreeTDS 0.91.89 for moodle 2.5.1 under x86 vc11
- Visual C 11 (Visual Studio 2012) (Express version OK) (Windows SDK not required)
- Current stable PHP source (http://php.net/downloads.php)
- Current stable FreeTDS source (ftp://ftp.freetds.org/pub/freetds/stable/)
- Most recent binary tools (http://windows.php.net/downloads/php-sdk/)
- PHP dependencies (http://windows.php.net/downloads/php-sdk/)
Setup PHP environment by following guide:
- In step 8, bin\phpsdk_buildtree.bat did not create the vc11 folder so I just copied the vc9.
Ensure you can build PHP under this created environment using:
configure --disable-all --enable-cli
Now loosely follow the instructions on building FreeTDS here:
If when attempting to build php_dblib.lib from source you get linking errors, it is probably because you are missing some source files in the projects/solutions. Doing a file search in the src folder for the symbol (without the preceding underscore) should highlight where the function definition comes from. Add the file to the project that won’t compile. Rinse and Repeat.
Linking errors: http://permalink.gmane.org/gmane.comp.db.tds.freetds/11009
When php_dblib.lib is compilied and in the php lib folder in your build environment, the last thing is to configure the makefile appropriately. (You may also need to compile libTDS.lib from FreeTDS - I had to.).
I used a number of different configurations while testing. This one worked:
configure --disable-all --disable-zts --enable-cli --with-dblib=shared --with-extra-includes=C:\php-sdk\phpdev\vc11\x86\php-5.5.1-src\freetds\include;C:\php-sdk\phpdev\vc11\x86\php-5.5.1-src\freetds\win32
Check the output from the configure call in case there any skipped dependencies. (like dblib!)
“--with-dblib=shared” is the key line for actually telling make to create an external dll file and not just compile the functionality directly into PHP core.
If while using nmake an unresolved external symbol linker error is reported: e.g. “error LNK2019: unresolved external symbol _tdsdump_log referenced in function _dblib_add_connection” you may need to add libTDS.lib to the libs statement in makefile and rerun make.
http://enyby.blogspot.co.uk/2013/01/eliminate-errors-compile-freetds-on.html. I had to do this.
Use “--enable-zts” or --disable-zts” for thread safety as mentioned.
Then when php_dblib.dll is created put it in the ext folder in your run time environment and enable the extension in your php.ini and you’re good to go!
Environment variables with IIS
One last consideration is the location of your freetds.conf file. If your server will only need one freetds.conf file then you can just use a server environment variable. Other wise you will need to add environment variables to the FastCGI Settings for PHP in IIS. Remember that the FREETDS variable is a path, and not a filename! I did not remember this!
just some comments aiming to clarify a bit the status of both the FreeTDS (mssql) and Microsoft (sqlsrv) drivers.
A. First of all, let me state clearly that, both, right now (Moodle 2.5), continue being supported. And any problem with them, if not impossible, will be fixed.
B. Said that, it's true that current support level is better for both MySQL and PostgreSQL (call it "gold support", although I don't like the nomenclature specially). Then the SQL*Server ones (silver), and finally Oracle (plastic?).
C. There are various reasons for declaring both SQL*Servers a bit beyond the gold ones, all them with their influence, like:
- Number of installations aka number of reports.
- Stability of the underlying drivers.
- Fulfillment of the SQL standards.
- Proprietary (and paid) nature of the software (driver, rdbms, os...)
D. About MDL-37734, it is a really, really nasty limitation of the server itself (incredible I'd say if you ask me), but it has been already "solved by a workaround" in our sqlsrv driver (see MDL-37748). It surely implies memory use will grow a bit for each connection, but it was the only way to go.
And it's exactly the same workaround that FreeTDS has built-in since ages ago (so we did not need to change our mssql driver).
We are just asking to MS if they would apply the workaround to their driver (like FreeTDS did) instead of having it applied in our (php code) drivers. That would benefit any other application out there trying to handle transactions and recordsets at the same time (something really usual, IMO).
So, at all effects, it's fixed (for Moodle POV). And, that warning in the Docs (that I did not write) is completely inaccurate right now IMO.
More yet, if you want to know if the server is going to work ok in your stack, just run the unit test on it. It will show possible problems for sure. We try hard to add to the tests any query/combination prone to errors.
E. About comparing FreeTDS and MS drivers, well, for me the election is pretty clear. I'm a Mac and need to use FreeTDS (same applies to any non-windows system out there). For people using complete windows stacks I think it has more sense to use the MS driver. Although the TDS (Tabular Data Stream) protocol is stable and solid... it's not "native/original" from MS, but something inherited from Sybase and maybe deprecated some day (remark it's a maybe only).
F. Disclaimer: be noted that I'm not a Windows guy nor expert at all, so surely all you know way more which one performs and suit your needs better for your environments.
So, brief summary:
Both drivers are supported, slightly beyond mysql and postgres (btw, have you considered them?), the horrible problem in MDL-37734 is fixed and, as a general recommendation, be as "native" as you can when building server stacks.
Hope it helps, ciao
PS: David, many thanks for building the PHP5.5 versions of the FreeTDS driver for Windows. Would you mind if I copy them to the Moodle downloads server and link to that location? Mainly to provide a stable location for them and to know how popular it is. TIA!
Thanks for a very good clarification. It's certainly good to know that the MS drivers can be used fine, specially when working in a full MS environment, as you said.
Your name came up a number of times while I was researching MSSQL and moodle, so it's great to hear this "from the horses mouth", so to speak.
When you say: "More yet, if you want to know if the server is going to work ok in your stack, just run the unit test on it."
Does that mean running the acceptance testing here? * Home -> Site administration -> Development -> Acceptance testing
On not using MySQL/postgres.
The database expertise of the IT staff at the moodle install I'm currently working on is all with MSSQL. All of their other systems are using MSSQL also. So if it works then that is the preference.
In regards to the drivers. Yes, please do.
BTW, I updated the page you started here:
We used to connect fine through external db with mssql in moodle 2.5
After upgrading php to 5.4.35 (in unix server) we can't connect the users from the external db (as we used to) we get these errors:
Warning: mssql_connect(): Unable to connect to server: 188.8.131.52:1433 in ......../xxxxx/moodle/lib/adodb/drivers/adodb-mssql.inc.php on line 614
184.108.40.206:1433: Connection error to server 'OURSERVER-IP' with user 'OUR-USER'
(mssql): SELECT * FROM dbo.vMoodleUserData WHERE StudentCode = '246847' AND PasswordInt = '7097'
Warning: mssql_query() expects parameter 2 to be resource, boolean given in /xxxxxx/moodle_emuna/lib/adodb/drivers/adodb-mssql.inc.php on line 830
Please Help me what can I do to fix this?
In our php erver (unix) we have freetds installed too and in the sql server 2005 (WINDOWS) we used to use the ntwdblib.dll ( I don't know if that has something to do with that). Do we need to change or update a new driver in the SQL server so it will fit the php 5.4 version?
What do we need to so exactly? and what is the freetds version in the php server that I need to set? Is it 7.0 , 8.0 or something else?
Hope someone can help me?
Guys I am still struggling with this issue, I guess I have tried everything to let Moodle connect to mssql (sqlsrv is running, dblib is running) I even added ntwdblib since I read in a forum that it might work, what am I missing please. I am using PHP 5.5.12, sql_srv_55 and FreeTDS_0.91.89_PHP_5.5.x_(vc11_ts_dblib) and still giving me connection error, any pinpoints? or advice?
It was commented and I removed it entierly, my phpinfo says cscript /nologo configure.js "--enable-snapshot-build" "--disable-isapi" "--enable-debug-pack" "--without-mssql" "--without-pdo-mssql" ",
PDO support enabled
PDO drivers sqlsrv, mysql, odbc, sqlite
sqlsrv support enabled
Directive Local Value Master Value
sqlsrv.ClientBufferMaxKBSize 10240 10240
sqlsrv.LogSeverity 0 0
sqlsrv.LogSubsystems 0 0
sqlsrv.WarningsReturnAsErrors On On
Thank you it worked, if any one wants to know how i can send him the documentation and used files. cheers!
hello, I am using WAMP on Windows7, PHP 5.5, Apache 2.4.9, Moodle on MySQL and trying to connect to MSSQL, If I go to the external database plugin it shows me the connection is well configured and names of the table in MSSQL; I will list everything I've done and please if anyone can help or transfer the thread to development since all over Moodle forum I find the same question and then no luck in connecting or it connects but the steps are not specified.
I am not the PC administrator
The PC is on a network
No local antivirus or firewall shown
The MSSQL and MySQL are on the same PC
php_sqlsrv_55_ts (extension installed, php.ini modified, right sign next to it in wamp-php-extensions)
php_pdo_sqlsrv_55_ts (extension installed, php.ini modified, right sign next to it in wamp-php-extensions)
sybase_ct active (though I read it might need other dll dependencies, please anyone knows them?)
ntwdblib.dll added to php and apache though useless (wamp-php-extensions php_mssql shows a red square)
phpdblib.dll added with freetds.conf file located in C:\ (I'm using wamp shall it be located elsewhere?) the file contains
host = 127.0.0.1 #shall I add an instance? how to do that please?
port = 1433
client charset = UTF-8
tds version = 7.2 # tdsversion shall it be 8.0 and whats the difference?
text size = 40971520
debug level = 1
odbc.ini added to it a line at the end tds_version = 7.2 (if freetds.conf modified to 8.0 this must be modified as well?)
php.ini added the extension of dblib and it has a right check in wamp-php-extensions;
The extensions that show exclamation mark in php-extensions php-opcache, php_enchant are those necessary for our case? (there are some extensions that are not "on" but without exclamation mark so I guess those are ok)
Is there any Apache module that needs to be added?
My PHP info says without-mssql" "--without-pdo-mssql"
sqlsrv, mysql, odbc, sqlite
Do you think 10240 must be changed, do you think without-mssql" "--without-pdo-mssql" (is a normal message?)
dblib is not shown anwhere in php.info is that normal?
shall I add
• mssql.textlimit = 20971520
• mssql.textsize = 20971520 to php.ini although I am not using mssql? if not under what name shall I put those in php.ini
is there anything else that needs to be modified in php.ini such as mssql.secure_connection = On?
I tried even to install Moodle on MSSQL it reached the enter your admin credentials then gives an error though the database is created (i followed step by step the documentation on Moodle: IP, Port, collation, Ansi....)
I think its a timeout error, security error or ODBC error, any suggestions?
A Colleague told me about odbtp drive, I didn't know how to install it, anyone has any idea baout that?
Sorry for all the information above, but I thought maybe this issue lacks clarity, since its been asked a lot and there is not much step by step and debugging info mentioned
Moodlers, Anyone? please I need to setup my server this week and still didnt reach the bottom of this issue, it is crucial that moodle connects to MSSQL since all the students data is there through the earlier LMS system
What do you mean by "it is crucial that moodle connects to MSSQL since all the students data is there through the earlier LMS system"? Are you trying to migrate student data from a different LMS into Moodle? Installing Moodle on the same database as that "earlier LMS system" won't magically bring in all the student data from that LMS into Moodle. You'll have to go through a migration process to export the data from the earlier LMS system and import it into Moodle.
Are you still using SQL Server 2005, as per your earlier post? That's probably going to give you problems. The minimum that you should dare to install Moodle on is SQL Server 2008 R2. Anything older than that is asking for trouble.
First you describe your freetds.conf file. Then you show the PHPInfo() output for SQLSRV. Those are two different drivers. SQLSRV has nothing to do with the freetds.conf file. SQLSRV = the Microsoft PHP Driver for SQL.
You only need one of them to connect PHP/Moodle to MSSQL- FreeTDS OR SQLSRV. If you are using FreeTDS (MSSQL) then ignore the SQLSRV section in your PHPInfo() output. That SQLSRV section in has nothing to do with FreeTDS.
Does PHPInfo() show a MSSQL section? If not, then FreeTDS isn't loaded correctly.
For SQL Server 2005 and FreeTDS you should use:
tds version = 7.2
in your freetds.conf file. It includes support for varchar(max), varbinary(max), xml datatypes and MARS. Using "tds version = 8.0" probably won't work. 8.0 isn't a real TDS version - FreeTDS will alias 8.0 back to 7.1.
Where have you put your freetds.conf file? In your PHP folder?
And have you created the FREETDSCONF environment variable and pointed it to the location of your freetds.conf file? If not, then move freetds.conf to your PHP folder (e.g. C:\PHP) and then point the FREETDSCONF environment variable to that PHP path ("C:\PHP\freetds.conf"). Restart the webserver and then reload PHPInfo(). Does it show the MSSQL section? If it does, what happens when you try to connect to Moodle again?
Thanks a lot for your help, will test it and let you know, yes I loaded all the extensions that i read about, in hope that it would work, I will go through freetds, what I want is to be able to activate the external enrolment plugin between Moodle is on MySQL but i want it to fetch enrolments from another platform that runs on MSSQL through this plugin. MSSQL is 2008 R2
what version shall I use please in the freetds.conf?
I did not get that step please
And have you created the FREETDSCONF environment variable and pointed it to the location of your freetds.conf file? If not, then move freetds.conf to your PHP folder (e.g. C:\PHP) and then point the FREETDSCONF environment variable to that PHP path ("C:\PHP\freetds.conf"). (what does FREETDSCONF environment variable) I have a freetds.conf file located in C:\ and its content is available in the earlier post,
Shall I disable SQLSERV? or it doesnt matter?
Once again thanks for your help and support, and sorry I'm
being naggy but its been a week that I'm trying every available possible
solution from the Internet.
Thanks a lot, whenever I try to activate the external enrolment plugin the test seems to work on Moodle Interface, if I try to run the cli script via cmd (I am a windows user, using Moodle through wamp, cant use $ sudo...) it gives me "error while communicating with external database", I am running "php -f C:\wamp3\www\moodle\enrol\database\cli\sync.php" (yes its wamp3 and my localhost is localhost:8080) any advice? any help? thanks in advance
Anyone? any suggestions?
It looks like the new versions of the FreeTDS library require a client charset to be set, just throw the following in if you don't already have it set:
[under either global or your specific host]
client charset = ISO-8859-1
Also, I'm not sure if it's just me, but I have to set the FREETDSCONF environment setting before it can read my configuration file when using CLI (in the past, I just used the default location).