Major bug in phpBB's SQL Server support?

This is an archive of the phpBB 2.0.x support forum. Support for phpBB2 has now ended.
Forum rules
Following phpBB2's EoL, this forum is now archived for reference purposes only.
Please see the following announcement for more information: viewtopic.php?f=14&t=1385785
novasource
Registered User
Posts: 12
Joined: Thu Sep 21, 2006 4:23 pm

Major bug in phpBB's SQL Server support?

Post by novasource » Thu Sep 21, 2006 5:51 pm

All I get on a fresh install is this most obtuse, useless error message: phpBB : Critical Error / Could not connect to the database.

I am running the following configuration:
  • phpBB 2.0.21
    PHP 5.1.6
    SQL Server 2000 SP4
    Windows 2003 SP1 for both the IIS and SQL Server machines
PHP itself seems to work fine. I go to phpBB's install/install.php and enter the following:
  • Default board language: English
    Database Type: MS SQL Server 7/2000
    Choose your installation method: Install
    Database Server Hostname / DSN: [full DNS name for the SQL Server]
    Your Database Name: [name of database that I just created]
    Database Username: [username for a SQL Server account (not Windows account) that has db_owner permissions on the above DB]
    Database Password: [the password for the DB]
    Prefix for tables in database: phpbb_
    Admin Email Address: [my email]
    Domain Name: [full DNS name for the server that has IIS]
    Server Port: 80
    Script path: /phpbb/
    Administrator Username: [a new username for the administrator]
    Administrator Password: [a password for administrator account]
    Administrator Password [ Confirm ]: [that password again]
Then I press Start Install and get this completely useless error message:
  • phpBB : Critical Error
    Could not connect to the database
The funny thing is that if I change the database server's DNS name to a blatantly incorrect server name that cannot possibly resolve, I get the exact same error.

In my php.ini, I uncommented this line: extension=php_mssql.dll. I also set cgi.force_redirect = 0.

I also ran Wireshark (formerly Ethereal) on my web server to monitor traffic between phpBB and SQL Server. phpBB isn't even trying to talk on standard SQL Server ports!! When you talk to SQL Server, you usually start out with 1433 and 1434. For some reason, phpBB is trying to talk to ports 445 and then 139, then it sends SMB packets(!). It appears that phpBB is taking the massively incorrect step of trying to authenticate to SQL Server with Windows accounts instead of SQL Server accounts! Indeed, I see evidence it is trying to authenticate with the web server's iusr_webServerName account.

Just to make sure I am not smoking crack, I monitored the TCP communications of a different forum product that talks to the same SQL Server. It only communicates TCP to ports 1433 or 1434. None of this SMB or 445 or 139 or iusr crap appears in the packets.

BTW, I disabled all firewall and MS IPSec's port restrictions at one point, and that didn't make a difference.

I have two other competing products running fine, using this exact same SQL Server (albeit with different databases). I had hoped to use phpBB because it supposedly has a superior feature set. But does it really work with SQL Server? Seems like the answer is no given that http://www.phpbb.com/phpBB/viewtopic.php?t=161475 has been open for almost 3 years with little resolution on any SQL Server matter besides a phpBB bug dealing with converting nulls to spaces.

Bobble
Registered User
Posts: 3504
Joined: Thu Mar 24, 2005 12:51 pm

Post by Bobble » Thu Sep 21, 2006 6:04 pm

Of course you realise that phpBB is not officialy supported under PHP 5, right?

novasource
Registered User
Posts: 12
Joined: Thu Sep 21, 2006 4:23 pm

Post by novasource » Thu Sep 21, 2006 6:12 pm

Bobble wrote: Of course you realise that phpBB is not officialy supported under PHP 5, right?


The install document said I need "PHP version 4.0.3 or later." Is the install document wrong?

cybrid23
Former Team Member
Posts: 9877
Joined: Wed Jun 29, 2005 5:55 am
Location: Somewhere in the Midwest...
Contact:

Post by cybrid23 » Thu Sep 21, 2006 6:50 pm

After using google and the search here, I found this nifty mssql install guide:

http://www.phpbb.com/phpBB/viewtopic.php?t=74511

See if it helps....
---Never leave home without a towel and your peril sensitive sunglasses.
---Do Not PM Me For Support. It will go unanswered.
Thanks.

User avatar
lurttinen
Translator
Posts: 4670
Joined: Tue Sep 21, 2004 12:05 pm
Location: Tampere, Finland
Name: Martti Lokka
Contact:

Post by lurttinen » Thu Sep 21, 2006 6:51 pm

novasource wrote:
Bobble wrote:Of course you realise that phpBB is not officialy supported under PHP 5, right?


The install document said I need "PHP version 4.0.3 or later." Is the install document wrong?

Later, as in Version 4. 4 and 5 are two different PHP versions.
Major number change like from 4-5 usually breaks backward compatibility and bobble is right.
PHP5 is not officially supported, but it might work.

I would downgrade to PHP 4 before doing anything else.
Signature is here

espicom
Registered User
Posts: 17905
Joined: Wed Dec 22, 2004 1:14 am
Location: Woodstock, IL

Post by espicom » Thu Sep 21, 2006 6:51 pm

The magic chain of things to make PHPBB work:

Your SQL server must be running.
PHP must be configured to talk to it.
You must have a valid username/password for that server.
The database must already exist.
The username/password must have privileges to operate in that database, including the CREATE, DROP, ALTER, SELECT, INSERT, DELETE privileges.

If any of those are not correct, you will get the "can not connect" error message.

Try running contrib/dbinformer.php instead of install/install.php, and see where your connect chain is broken.
Jeff
Fixing 1016/1030/1034 Errors | (obsolete link) | MySQL 4.1/5.x Client Error | phpBBv2 Logo in ACP
Support requests via PM are ignored!
"To be fully alive is to feel that everything is possible." - Eric Hoffer

novasource
Registered User
Posts: 12
Joined: Thu Sep 21, 2006 4:23 pm

Post by novasource » Thu Sep 21, 2006 7:34 pm

I downgraded to PHP 4.4.4, and I get the exact same error.

Here is the response from contrib/dbinformer.php:

What you entered
Database Type: MS SQL Server 7/2000
Database Server Hostname / DSN: [server name]
Your Database Name: [database name]
Database Username: [SQL Server account name--NOT a Windows account]
Database Password: [SQL Server account password]

Connection to database
You have not established a connection to MS SQL Server 7/2000.
ERROR:

Your database was not found.
ERROR:

Another incredibly informative error message. :roll:

I re-ran Wireshark (formerly Ethereal), and phpBB is still trying to communicate to ports it has no business talking to. There is absolutely no reason why phpBB should be talking to any port on the SQL Server machine besides 1433 or 1434. I am reasonably certain phpBB is trying to authenticate to the SQL Server machine with the Windows account under which the PHP script engine runs, which is, by default, a local account on the machine hosting IIS. This is abnormal behavior since I am specifying an account in the Database Configuration page.

Again, I have other products on the same IIS machine that talk to the same SQL Server. These products do not access ports 445 or 139, nor do they send SMB packets. They do well-behaved port 1433 and 1434 accesses.

Graham
Former Team Member
Posts: 8462
Joined: Tue Mar 19, 2002 7:11 pm
Location: UK
Contact:

Post by Graham » Thu Sep 21, 2006 7:49 pm

I hate to disappoint you, but phpBB itself is doing no communication directly with the SQL server at all by itself - all our communication goes via the mssql PHP extension. You are able to see exactly what calls get made by looking at db/mssql.php

You may wish to try testing this by creating a simple PHP script yourself and making the same calls (ie mssql_connect, etc).

If the direct connection via the PHP module does not work, you could also try connecting via ODBC as well which is another installation option
"So Long, and Thanks for All the Fish"

phpBB Useful Links: Knowledge Base | Userguide | Forum Search | MOD Database | Styles Database
My Links: Blog!

novasource
Registered User
Posts: 12
Joined: Thu Sep 21, 2006 4:23 pm

Post by novasource » Thu Sep 21, 2006 7:58 pm

Graham wrote: I hate to disappoint you, but phpBB itself is doing no communication directly with the SQL server at all by itself - all our communication goes via the mssql PHP extension. You are able to see exactly what calls get made by looking at db/mssql.php

You may wish to try testing this by creating a simple PHP script yourself and making the same calls (ie mssql_connect, etc).

If the direct connection via the PHP module does not work, you could also try connecting via ODBC as well which is another installation option


You are correct in a highly technical sense.

However, the PHP script layer is only doing the bidding of the phpBB programmers. From the best I can tell, the phpBB programmers have apparently chosen to make phpBB authenticate with Windows accounts--not SQL Server accounts--if you are talking to a remote SQL Server. This is incorrect and unusual behavior; the install.php page instructs one to use a "database username" and "database password," not a "Windows username" and "Windows password."

I am quite sure PHP is capable of connecting to SQL Server databases with SQL Server account instead of Windows accounts. Otherwise, PHP would be notorious for being terribly defective.

espicom
Registered User
Posts: 17905
Joined: Wed Dec 22, 2004 1:14 am
Location: Woodstock, IL

Post by espicom » Thu Sep 21, 2006 8:25 pm

PHPBB has no way of knowing whether the account information you give it is an SA or Windows account. It simply passes that information to PHP. The following settings in php.ini (not part of PHPBB) affect how PHP talks to the server:
[MSSQL]
; Allow or prevent persistent links.
mssql.allow_persistent = On

; Maximum number of persistent links. -1 means no limit.
mssql.max_persistent = -1

; Maximum number of links (persistent+non persistent). -1 means no limit.
mssql.max_links = -1

; Minimum error severity to display.
mssql.min_error_severity = 10

; Minimum message severity to display.
mssql.min_message_severity = 10

; Compatability mode with old versions of PHP 3.0.
mssql.compatability_mode = Off

; Valid range 0 - 2147483647. Default = 4096.
;mssql.textlimit = 4096

; Valid range 0 - 2147483647. Default = 4096.
;mssql.textsize = 4096

; Limits the number of records in each batch. 0 = all records in one batch.
;mssql.batchsize = 0

; Use NT authentication when connecting to the server
mssql.secure_connection = Off

; Specify max number of processes. Default = 25
;mssql.max_procs = 25


Generally, these settings aren't even available to script writers to modify...
Jeff
Fixing 1016/1030/1034 Errors | (obsolete link) | MySQL 4.1/5.x Client Error | phpBBv2 Logo in ACP
Support requests via PM are ignored!
"To be fully alive is to feel that everything is possible." - Eric Hoffer

User avatar
DavidMJ
Former Team Member
Posts: 314
Joined: Thu Aug 04, 2005 12:50 am
Location: New York

Post by DavidMJ » Thu Sep 21, 2006 8:33 pm

The PHP Manual wrote: resource mssql_connect ( [string servername [, string username [, string password [, bool new_link]]]] )
The semantics are not the issue, the MSSQL extension for PHP takes a username and a password and this is what we ask of you.

As to your quip regarding converting nulls to spaces, we do the reverse. We convert spaces to nulls. This, by the way, is a SQL Server bug. Don't believe me?
MSDN wrote: Whether SQL Server interprets an empty string as either a single space or as a true empty string is controlled by the compatibility level setting. If the compatibility level is less than or equal to 65, SQL Server interprets empty strings as single spaces. If the compatibility level is equal to 70, SQL Server interprets empty strings as empty strings. For more information about setting the compatibility level, see sp_dbcmptlevel (Transact-SQL).
We get the value from the MSSQL extension, we can't tell for sure if it is null or not so if we get a space, we just assume it is null.

novasource
Registered User
Posts: 12
Joined: Thu Sep 21, 2006 4:23 pm

Post by novasource » Fri Sep 22, 2006 4:51 pm

espicom wrote: PHPBB has no way of knowing whether the account information you give it is an SA or Windows account. It simply passes that information to PHP. The following settings in php.ini (not part of PHPBB) affect how PHP talks to the server:
[MSSQL]
; Allow or prevent persistent links.
mssql.allow_persistent = On

; Maximum number of persistent links. -1 means no limit.
mssql.max_persistent = -1

; Maximum number of links (persistent+non persistent). -1 means no limit.
mssql.max_links = -1

; Minimum error severity to display.
mssql.min_error_severity = 10

; Minimum message severity to display.
mssql.min_message_severity = 10

; Compatability mode with old versions of PHP 3.0.
mssql.compatability_mode = Off

; Valid range 0 - 2147483647. Default = 4096.
;mssql.textlimit = 4096

; Valid range 0 - 2147483647. Default = 4096.
;mssql.textsize = 4096

; Limits the number of records in each batch. 0 = all records in one batch.
;mssql.batchsize = 0

; Use NT authentication when connecting to the server
mssql.secure_connection = Off

; Specify max number of processes. Default = 25
;mssql.max_procs = 25


Generally, these settings aren't even available to script writers to modify...


My default installation of PHP 4.4.4 (using the Windows MSI available at php.net) already had mssql.secure_connection = Off.

It seems that phpBB is not initially authenticating with the account you gave it. It appears that phpBB attempts to first authenticate with teh SQL Server's host using the IIS anonymous user account to create a named pipe. That is not what I want.

abd
Registered User
Posts: 41
Joined: Fri Dec 05, 2003 3:57 pm

Post by abd » Fri Sep 22, 2006 5:31 pm

Have you been able to determine that PHP on your sever can connect to the sql database at all? Regardless of phpbb the first thing to do would be make sure PHP and SQL are working together.

Save this as a file on your server (after replacing the bracketed words with the correct values) and try to run it.

Code: Select all

<SCRIPT LANGUAGE="php">

  //Links to the db 
  $db_link = mssql_connect("[SQLServerName]","[SQLServerAccountName]","[SQLServerAccountPassword]");
  mssql_select_db("[DatabaseName]",$db_link);

     $query  = "SELECT * FROM [TableName]";
     $result = mssql_query($query,$db_link);
       if($result)
         {
	  if(mssql_num_rows($result) > 0)
            print "YUP FOUND RESULTS";
	 else print "NO RESULTS";
         }// end result


</SCRIPT>
Make sure to replace table name with a valid table name with records in it.
Hope This Helps.

novasource
Registered User
Posts: 12
Joined: Thu Sep 21, 2006 4:23 pm

Post by novasource » Fri Sep 22, 2006 5:58 pm

abd wrote: Have you been able to determine that PHP on your sever can connect to the sql database at all? Regardless of phpbb the first thing to do would be make sure PHP and SQL are working together.

Save this as a file on your server (after replacing the bracketed words with the correct values) and try to run it.

Code: Select all

<SCRIPT LANGUAGE="php">

  //Links to the db 
  $db_link = mssql_connect("[SQLServerName]","[SQLServerAccountName]","[SQLServerAccountPassword]");
  mssql_select_db("[DatabaseName]",$db_link);

     $query  = "SELECT * FROM [TableName]";
     $result = mssql_query($query,$db_link);
       if($result)
         {
	  if(mssql_num_rows($result) > 0)
            print "YUP FOUND RESULTS";
	 else print "NO RESULTS";
         }// end result


</SCRIPT>
Make sure to replace table name with a valid table name with records in it.
Hope This Helps.


I get virtually the same result. This is the error for the line that has the mssql_connect statement: Warning: mssql_connect() [function.mssql-connect]: Unable to connect to server: servername in e:\www\test.php on line 4.

And I ran Wireshark (formerly Ethereal), and I am getting the exact same result as before: PHP is refusing to talk to standard SQL Server ports. Instead, it is sending TCP and SMB packets to ports 445 and 139, attempting to authenticate with the local server account under which IIS runs, not the account specified in the mssql_connect function arguments.

PHP should be simply connecting to ports 1433 or 1434 and authenticating with SQL Server accounts. Talking to 445 or 139 may be PHP trying to set up a named pipe. This is an advanced function that PHP should not be attempting by default unless I choose to do this.

I am researching this on Google. Others have reported similar problems with other PHP apps. I am just shocked that PHP has such a serious flaw.

novasource
Registered User
Posts: 12
Joined: Thu Sep 21, 2006 4:23 pm

Post by novasource » Fri Sep 22, 2006 6:18 pm

novasource wrote: PHP should be simply connecting to ports 1433 or 1434 and authenticating with SQL Server accounts. Talking to 445 or 139 may be PHP trying to set up a named pipe. This is an advanced function that PHP should not be attempting by default unless I choose to do this.

I am researching this on Google. Others have reported similar problems with other PHP apps. I am just shocked that PHP has such a serious flaw.


Looks like this may have been a problem for over 4 years now. See this Google Groups post from 2002.

Still researching.

Locked

Return to “2.0.x Support Forum”