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.