Connect to Microsoft SQL Server 2012 using PHP PDO on Ubuntu

Microsoft SQL (MSSQL) databases provide a Open Database Connectivity (ODCB) interface for accessing data. First we need to enable TCP/IP connection of MSSQL in SQL Server Configuration Manager:
1. Right click to enable TCP/IP

2. Double-click to set TCP/IP port (default is 1433)

3. Restart MSSQL service

4. Also remember to open MSSQL listening port 1433 on Windows Firewall.



At the Ubuntu side, we use PHP and PHP Data Objects (PDO) for connection. PDO is a powerful tool that can connect all kinds of databases, with appropriate driver installed.


1. Enable PDO ODBC
sudo apt-get install php5-odbc


2. we also need to install unixodbc and FreeTDS (ref.). Luckily we can easily install them through apt-get:
sudo apt-get install unixodbc tdsodbc


3. After installing necessary libraries, we need to configure our Data Source Name (DSN) in /etc/odbc.ini and /etc/odbcinst.ini (from this ref.)

/etc/odbc.ini

 # Define a connection to a Microsoft SQL server  
 # The Description can be whatever we want it to be.  
 # The Driver value must match what we have defined in /etc/odbcinst.ini  
 # The Database name must be the name of the database this connection will connect to.  
 # The ServerName is the name we defined in /etc/freetds/freetds.conf  
 # The TDS_Version should match what we defined in /etc/freetds/freetds.conf  
 [MSSQL_DSN]  
 Description   = Microsoft SQL server   
 Driver        = freetds  
 Database      = Your_DB  
 ServerName    = MY_SERVER  
 TDS_Version   = 7.0  

/etc/odbcinst.ini

 # Define where to find the driver for the Free TDS connections.  
 # Make sure you use the right driver (32-bit or 64-bit).  
 [freetds]  
 Description = MS SQL database access with Free TDS  
 Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so  
 Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so  
 #Driver = /usr/lib/i386-linux-gnu/odbc/libtdsodbc.so  
 #Setup = /usr/lib/i386-linux-gnu/odbc/libtdsS.so  
 UsageCount = 1  

/etc/freetds/freetds.conf

 #Define a connection to the Microsoft SQL Server  
 [MY_SERVER]  
   host = 192.168.1.10  
   port = 1433  
   tds version = 7.0  

4. Restart Apache server to enable the ODBC features
sudo service apache2 restart

5. Finally we can use ODBC in PDO as below:
$DB_conn = new PDO('odbc:MSSQL_DSN', 'sa', 'password');