Connection to a MS SQL Server from Symfony / Doctrine on Mac or Linux

Posted on by Kévin Dunglas

Microsoft provides a SQL Server driver for PDO. Unfortunately, this driver only works on Windows. Linux and Mac OS X apps must use the FreeTDS compatibility layer: an open source implementation of the MS SQL Server protocol for Unix.

It’s possible to connect a Symfony app to a SQL Server instance on Unix through FreeTDS but this involve to use a Doctrine driver that is not provided with the standard distribution. Some tutorials already explain how to do that, but they encourage doing dirty things like editing files in the vendor/ directory. Here is the clean way! And if you are interested in creating your own server then read more here.

First, install FreeTDS.

On Mac OX X, use Homebrew:
brew install freetds

On Debian or Ubuntu:
apt-get install freetds-bin

The next step is to configure FreeTDS to be able to connect to the SQL Server instance.

Open the freetds.conf file (/etc/freetds/freetds.conf on Debian / Ubuntu and /usr/local/etc/freetds.conf on Mac OS X) and add the connection details of your server:

[my_server]
host = sql.example.com
port = 1433
tds version = 8.0
client charset = UTF-8
text size = 20971520

Be sure to set the protocol version to 8.0, the client charset and the text size.

Now, you should be able to connect to the SQL server from the command line:
tsql -S my_server -U myusername

Type your password when asked and Ctrl+D to disconnect from the server.

It’s time to install the DBLIB PDO Driver.

On Mac OS X (replace php55 by the version of PHP you are using):
brew install php55-pdo-dblib

On Debian or Ubuntu:
apt-get install php5-sybase

And add the DBLIB driver for Doctrine (packaged in a Symfony bundle) in your app:

# in your Symfony app directory
composer require realestateconz/mssql-bundle:dev-master

Enable the Symfony bundle. Add this line in the registerBundles() method of your AppKernel in app/AppKernel.php:

new Realestate\MssqlBundle\RealestateMssqlBundle(),

Finally, configure Doctrine to use this driver. Edit app/config/config.yml:

doctrine:
    dbal:
        driver_class: Realestate\MssqlBundle\Driver\PDODblib\Driver
        host: my_server
        dbname: MYDATABASE
        user: myuser
        password: mypassword

Note that you must use the driver_class parameter, and not driver. Of course, you should not hardcode these values. Use the interactive parameters system instead.

Your Symfony app is now able to connect to the SQL Server. Try to run a SQL query:
php app/console doctrine:query:sql "SELECT * FROM MY_TABLE"

As SQL Server is a bad default DBMS for a Symfony app, especially on Unix servers, you should be interested by using multiple database connection with Symfony and Doctrine.

Related posts:

  1. PHP Schema: generate a fully functional PHP / Doctrine / Symfony data model from Schema.org vocabulary in minutes
  2. Persistence in PHP with the Doctrine ORM: my new book in pre-order
  3. “Persistence in PHP with Doctrine ORM” eBook for $5 until January 6th 2015
  4. Entities and Mapping Information with the Doctrine ORM

24 thoughts on “Connection to a MS SQL Server from Symfony / Doctrine on Mac or Linux”

  1. Pingback: Connection to a MS SQL Server from Symfony / Doctrine on Mac or Linux ← Bookmarks

  2. Hi there.

    Thanks for this precious tutorial! Configured everything no problems!

    Are you available for some further help? We got to the point we successfully get a reply from our remote MSSQL via “php app/console doctrine:query:sql …”

    Now we’d like to reverse engineer the database into a bundle, but:

    a) using realestateconz we have a problem with a data type

    [Doctrine\DBAL\DBALException]
    Unknown database type timestamp requested, Realestate\MssqlBundle\Platforms\DblibPlatform may not support it.

    b) we tried using the isoft fork, but then we bump into

    [ErrorException]
    Notice: Undefined index: constraint_name in …/vendor/isoft/mssql-bundle/Realestate/MssqlBundle/Schema/DblibSchemaManager.php line
    32

    Reply

    1. Hi Giancarlo,

      The reverse engineering feature is not supported through this driver. IMO the easiest way is to generate the model from a Windows box with the official Microsoft driver (if it supports reverse engineering) then to use the model with the Linux driver.

      Reply

      1. Merci Kévin! It’s a long shot, but we’ll have a crack at it!

        Last thing: what distribution of this MSSQL bundle should we count on? The one you’re using in your tutorial or the isoft fork?

        Thanks in advance…

        Reply

        1. I’m using the bundle from realestateconz because it is still maintained (last commit 2 months ago) and doesn’t require to edit manually files in the vendor/ directory.

          I’ve not tried the isoft fork but there is no activity on the repository for a while.

          Reply

          1. Very well, mate. Thanks a bunch! We’ll revert to the realestateconz bundle and move on from there. I’ll keep you posted if we have any useful result.

  3. Thanx for this great article, I learnt many interesting things.
    Do you know how to get proper error messages or at least the contextual parameters for them ?
    IE: Conversion failed when converting the %1 value %2 to data type %3.

    Reply

  6. Hi Kevin

    Thanks for the details. I am now able to get the data from MSSQL using doctrine from app/console. But from the website code in src it says “could not find driver
    500 Internal Server Error – PDOException”

    do you know what could be the issue here ?

    Reply
  7. Pingback: Access SQL from Symfony 2 on CentOS 6.0 with MSSQL | Raj Karan
  11. Pingback: Connect Doctrine to a MS SQL Database – shareitHQ

  12. Hi thank you for posting this, I am having an exception “[PDOException]
    could not find driver ” do you know way is this happening?

    Thanks

    Reply

  13. Hi,

    Thanks for this great article. I am working on moodle having ‘Symfony 2.6.13’. By this article, I have tried to access mssql db on Mac. Working fine other than some stored procedures not called on Mac. Same stored procedures are working fine on windows. Is there any issue with these installations or I am missing some thing else?.

    Thanks

    Reply

  14. My configuratio and ERROR:

    $php -i | grep PDO
    PDO
    PDO support => enabled
    PDO drivers => dblib, firebird, mysql, odbc, pgsql, sqlite
    PDO Driver for FreeTDS/Sybase DB-lib => enabled
    PDO_Firebird
    PDO Driver for Firebird => enabled
    PDO Driver for MySQL => enabled
    PDO_ODBC
    PDO Driver for ODBC (unixODBC) => enabled
    PDO Driver for PostgreSQL => enabled
    PDO Driver for SQLite 3.x => enabled

    _______________________________________
    $tsql -C
    Compile-time settings (established with the “configure” script)
    Version: freetds v0.91
    freetds.conf directory: /etc/freetds
    MS db-lib source compatibility: no
    Sybase binary compatibility: yes
    Thread safety: yes
    iconv library: yes
    TDS version: 4.2
    iODBC: no
    unixodbc: yes
    SSPI “trusted” logins: no
    Kerberos: yes

    $ sudo vim /etc/freetds/freetds.conf
    [sqlserver_dev]
    host = 192.168.98.199
    port = 1433
    tds version = 7.0
    text size = 20971520

    execute symfony console
    $bin/console doctrine:query:sql “select * from pm01estoque.dbo.cadcop00” –connection=sqlserver

    Result this error

    [PDOException]
    SQLSTATE[01002] Adaptive Server connection failed (severity 9)

    Reply

Leave a Reply