Technical Bits

26th July 2007

Getting Microsoft SQL Express Server working over the network and talking to it using PHP

At work we have just bought a new streaming video server, that uses Microsoft SQL Express Server 2005 to store clip information. In it's "out of the box" state the SQL server on this box was set up to listen only for local connections, as this is all it needs to run. However we wanted to use the clip information on our intranet site which meant connecting to the media server database from the web server.

This wasn't as straight forward as it should have been. I went into the Configuration Manager and found the TCP/IP section (figure 1), which seemed a good place to start.

Figure 1

It was already enabled, so I didn't need to change anything there. Going in to the properties, I found that the "IP Addresses" section (figure 2) only contained an entry for the localhost (127.0.0.1). I set the IP Address in IP1 to the IP address of the server.

Figure 2

I doesn't matter that "Enabled" is set to No, as long as "Listen All" is set to Yes on the Protocol tab (figure 3). I restarted the SQL services but a TCP scan revealed that the server still wasn't listening for SQL connections. This stumped me for a while. I eventually discovered that it was down to the "TCP Port" field in the IPAll section being empty. I stuck in the port number 1433, restarted the services and all was well.

Figure 3

The next step was to connect to the server using the PHP mssql functions. I created a user on the SQL server for the PHP user to use. Using mssql_connect to connect to the database, all was well until we ran a query to retrieve data from a table. PHP came back with the error:

Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier.

It turns out that the PHP function can't cope with the ntext fields in the SQL Express Database. We needed to use the SQL "Cast" function to get around it:

SELECT CAST(ntext_field_name AS VARCHAR) FROM table_name

There maybe some other, better way around it but this is enough for our needs.