Connecting to a MSSQL Server/Database With PHP on Ubuntu/Debian

Hi,

I recently had to connect to a MSSQL Server using PHP on a Debian box (the previous database was in MySQL and we wanted to migrate to MSSQL). The following is how I accomplished it:

Step 1
On your Debian/Ubuntu box you will need to install php5-sybase (the Sybase connector provides the required libraries for connecting to the MSSQL Server) and the freetds software (also unixodbc) - do this as follows:

debian-7$ sudo apt-get install freetds-common freetds-bin unixodbc php5-sybase

Once this has installed, restart Apache (I'm assuming you're using Apache as I was, if you are using another web server - restart that, or if you only want it for php-cli - then no need to restart anything!).

debian-7$ sudo service apache2 restart

You can now test whether you can connect to your MSSQL Server as follows - using the tsql command line tool:

debian-7$ tsql -H <hostname/ip_address> -p <port> -U <username> -P <password> -D <chosen_database>

You should get a message that looks similar to this:

locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Default database being set to <chosen_database>
1> quit

If you get something similar to what you see above - you're in business and things are working, now you can set up your /etc/freetds/freetds.conf file and get cracking with your code.

(Please note I've only tested this using Mixed-Mode Authentication on the MSSQL Server, and hence I'm not sure if this will work using Windows Authentication - I doubt it but not sure - if you've tried it with Windows Authentication, please let everyone know whether it works or not and what changes (if any) you had to make). Also naturally, the user you log in with, needs to have access to your <chosen_database> - but I would have thought that as being pretty obvious!

Now to configure your /etc/freetds/freetds.conf file, open it in your chosen editor, and you will see some pre-populated content, at the bottom of this, add a line that looks similar to what I've put below:

[YOUR_SERVER_FRIENDLY_NAME]
host = <hostname/ip_address>
port = <port>
tds version = 8.0

You should now be able to connect without providing all the hostname/ports and such information about the server as follows:

debian-7$ tsql -S <YOUR_SERVER_FRIENDLY_NAME> -U <username> -P <password> -D <chosen_database>

This should give you output similar to that shown below:

locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Default database being set to <chosen_database>
1> quit

If you get this, then excellent, you're ready to wire in your PHP code:

To do this, simply take a look at the code I've pasted below (although ideally you should use some sort of ORM layer to make your life easy) - but for the purposes of illustration, the example below will suffice:

$connection = mssql_connect('<YOUR_SERVER_FRIENDLY_NAME>', '<username>', '<password>');

if (!$connection) {
  die('Unable to connect!');
}

if (!mssql_select_db('<chosen_database>', $connection)) {
  die('Unable to select database!');
}

$result = mssql_query('SELECT * FROM <some_table>');

while ($row = mssql_fetch_array($result)) {
  var_dump($row);
}

mssql_free_result($result);

When you run this - either through a browser or commandline, you should get data from your <some_table>.

I hope this has helped someone, enjoy :)

Warm Regards,

Shabbir