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

12 thoughts on “Symfony2 MSSQL support with pdo_dblib”

  1. @Sali, are you on Windows? Could it be that you did not load the required dlls in the PHP configuration? I’m just guessing.

  2. I did everything, with tsql is OK but when i try
    php app/console doctrine:database:create
    i’ve got:

    Could not create database [dbname] for connection named conn_name
    SQLSTATE[01002] Adaptive Server connection failed (severity 9)
    could you help me please?

  3. @Alberto: Thank you for your comment. Could it be that you do not have wrong connection details or not enough permission to create the database? Can you test that?

  4. I thought that and i try only read query:
    root@ubuntu-desktop:/var/www/mysfproject# php app/console doctrine:query:sql “SELECT * FROM table” (and I am root user too) but the same error.

    I made a php file :

    setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $stmt = $con->prepare(‘SELECT * FROM table’);
    $stmt->execute();
    $i=1;
    while( $row = $stmt->fetch() ){
    echo $i.’ – ‘.$row[0].’ ‘.$row[1];
    echo “”;
    $i++;
    }

    }
    catch(PDOException $e)
    {
    echo ‘Error: ‘ .$e->getMessage();
    }
    and everything is fine…. I don’t understand where is the problem.

    I installed bundle this way: composer require leaseweb/doctrine-pdo-dblib

  5. I have 2 freetds.conf in /usr/share/freetds and /etc/fretds both with:

    [my_mssql_server]
    host = 192.168.0.42
    port = 1433
    tds version = 8.0
    client charset = UTF-8
    text size = 20971520

  6. this is my autoload.php file:

    registerNamespaces(array(
    ‘PDODblib’ => __DIR__ . ‘/../vendor/bundles’,
    ));
    is that the problem?

  7. @Alberto: It seems your conf files are good, also your autoloader seems unlikely to me to be the cause.

  8. thank you for your help Maurits….. so what can I do? just change autoload file? i am working with symfony 2.3

  9. Hi
    Thanks for sharing.
    After putting in the connection details in /etc/freetds/freetds.conf when I try to connect via tsql command I get this error

    Error 20012 (severity 2):
    Server name not found in configuration files.
    Am I doing something wrong here?

Leave a Reply

Your email address will not be published. Required fields are marked *