Lucas Courot

Connect Doctrine2 to a MS SQL Server within a Symfony2 project

Posted on by

Connect Doctrine2 with a MS SQL Server within a Symfony2 project

I'm currently working on a project that requires a Microsoft SQL Server database. As Doctrine2 supports many different drivers I thought it would be very easy to configure. Something like changing the value of the ‘database_driver‘ and that's it!

Unfortunately, this wasn't enough for me. I tried different drivers (pdo_sqlsrv, pdo_dblib, mssql), I spent ages trying different solutions I found on Google and I'm sharing with you what worked for me.

Install the missing packages

If you're running on a "Debian like" OS you have to install freetds-bin and php5-sybase packages. I didn't have to do it on my Mac.

Now check your phpinfo() to see if the pdo_dblib driver is loaded.

Install a bundle for the driver support in Doctrine

composer require isoft/mssql-bundle

And follow the instructions from the README file

Configure Symfony2

Your parameters.yml file should look like this

# app/config/parameters.yml
parameters:
    database_driver: dblib
    database_host: Acme-SQLDEV\DEVSQLINSTANCE2008R2
    database_port: 1433
    database_name: DatabaseName
    database_user: DatabaseUser
    database_password: Password

    [...]

As you can see I appended the name of the SQL Server instance to the name of the database host using an backslash.

Don't forget to add the driver_class under doctrine.dbal in your config.yml as described on Github.

Still have some unexpected issues ?

In Doctrine\DBAL\DriverManager, the bundle says you have too add the driver the the list. However pdo_dblib didn't work for me and I had the replace it with dblib as below:

# vendor/doctrine/dbal/lib/Doctrine/DBAL/DriverManager.php
 private static $_driverMap = array(
        'pdo_mysql'  => 'Doctrine\DBAL\Driver\PDOMySql\Driver',
        ...
        'dblib' => 'Doctrine\DBAL\Driver\PDODblib\Driver',
    );

I know this is very messy to change a vendor file and you might have problems with this in the future by I haven't found any better solution.

If you have trouble updating your schema, you might also comment the _getPortableSequenceDefinition() method in Realestate\MssqlBundle\Schema\DblibSchemaManager class which overrides the one in Doctrine\DBAL\Schema\SQLServerSchemaManager.

Final thoughts

As you can see it's such a pain to support MS SQL Server in Doctrine and you should avoid to use it as much as possible in your PHP projects.

If you have any better solution, please post it in your comment.

Useful links

See the other articles published in Symfony .