Symfony2 MSSQL support with pdo_dblib

We now maintain a library for connecting to a MSSQL database, since we are using it in one of our Symfony2 applications. You can find the code here:

https://github.com/LeaseWeb/LswDoctrinePdoDblib

If you are trying to use Doctrine 2 (for Symfony 2) to connect to MSSQL server from a Linux machine, you might encounter the same problems we did. To be able to connect to MSSQL from a Ubuntu Linux server you will have to install “php5-sybase” (Sybase / MS SQL Server module for php5).

$ sudo apt-get install freetds-bin php5-sybase

Add the following lines to “/etc/freetds/freetds.conf”, and make sure you set it to talk the right MSSQL protocol. The TDS protocol version should be 8.0 for MSSQL server 2005 and up, which may be confusing. Also replace “{mssql-server-ip-address}” with your MSSQL servers IP address (without the brackets).

    [mssql_freetds]
        host = {mssql-server-ip-address}
        port = 1433
        tds version = 8.0
        client charset = UTF-8
        text size = 20971520

If you only connect to one server, you can also set the global TDS protocol version to 8.0 like this:

     [global]
         # TDS protocol version
         tds version = 8.0

Now test that your freetds is working by using the command line client. You have to replace {username} and {password} with your username and password (without the brackets):

$ tsql -S mssql_freetds -U {username}
Password: {password}
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1> quit

If you specify in “app/config/parameters.ini” that you want to use the “pdo_dblib” driver like this:

database_driver="pdo_dblib"

You will see the error:

[Doctrine\DBAL\DBALException]
The given 'driver' pdo_dblib is unknown, Doctrine currently supports only the following drivers:
pdo_mysql, pdo_sqlite, pdo_pgsql, pdo_oci, oci8, ibm_db2, pdo_ibm, pdo_sqlsrv

This is why you should install our LswDoctrinePdoDblib bundle, and following the instructions, you should comment out the “driver” and add a “driver_class” like this:

    # Doctrine Configuration
    doctrine:
        dbal:
            #driver:   %database_driver%
            driver_class:   Lsw\DoctrinePdoDblib\Doctrine\DBAL\Driver\PDODblib\Driver

After that you should be able to run this command without any errors:

$ php app/console doctrine:database:create

Now do not expect everything to work flawlessly, since MSSQL is not officially supported, and Doctrine is very picky in what it does and does not accept as database structure. However, this should get you started. Post your experience and questions in the comments. Good luck!

Share