Connect PHP to MSSQL via PDO ODBC

When I execute this code:

print_r(PDO::getAvailableDrivers());

It says I have the odbc driver available.

Array ( [0] => mysql [1] => odbc [2] => sqlite )

However, when I try to use it like so:

$handle = new PDO("odbc:Server=dbServerIpAddress,myportnumber;Database=mydatabase", "myusername", 'mypassword');

It doesn't do anything - no errors and it doesn't work at all. It won't even execute past that line!

How can I connect PHP to this MSSQL database via PDO and ODBC?

There are several configuration files you need to have set up. /etc/odbc.ini, /etc/odbcinst.ini and /etc/freetds/freetds.conf (these locations are valid for Ubuntu 12.04 and probably correct for most *nixes).

You'll need to install unixodbc and freetds (not sure what the package names are on CentOS). In Ubuntu this would be apt-get install unixodbc tdsodbc.

For help installing these, look at this question Can't Install FreeTDS via Yum Package Manager

/etc/odbc.ini (this file may be empty)

# 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]
Description             = MSSQL Server
Driver                  = freetds
Database                = XXXXXX
ServerName              = MSSQL
TDS_Version             = 7.1

/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/i386-linux-gnu/odbc/libtdsodbc.so
#Driver      = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup       = /usr/lib/i386-linux-gnu/odbc/libtdsS.so
UsageCount  = 1

/etc/freetds/freetds.conf (or you may find it at /etc/freetds.conf)

# The basics for defining a DSN (Data Source Name)
# [data_source_name]
#       host = <hostname or IP address>
#       port = <port number to connect to - probably 1433>
#       tds version = <TDS version to use - probably 8.0>

# Define a connection to the Microsoft SQL Server
[mssql]
    host = XXXXXX
    port = 1433
    tds version = 7.1

You may have to change the tds version = 7.1 line above depending on your version of MSSQL.

You will have to restart apache after you've made these changes.

In your PHP code you'll create your PDO object like this:

$pdo = new PDO("dblib:host=mssql;dbname=$dbname", "$dbuser","$dbpwd");

Note that your username may need to be in the format: domain\username.

Also, you will know that it worked if you execute phpinfo() in your page and search for "freetds" which will show an mssql section with freetds listed as the Library Version.

The accepted answer is correct up to the actual PHP call. As someone has rightly commented it should be calling the odbc driver. Secondly it isn't using the Data Source Name (DSN) that has been configured in odbc.ini but is in fact creating an ad-hoc DSN. Instead:

$pdo = new PDO("odbc:mssql", "$dbuser","$dbpwd");

where mssql refers to the DSN object in odbc.ini

You can create an ad-hoc DSN as follows:

$pd = new PDO('odbc:DRIVER=FreeTDS;SERVERNAME=mssql;DATABASE=' . $dbName,
              $dbuser, $dbpass);

where mssql now refers to the server object in freetds.conf and FreeTDS the driver object in odbcinst.ini

(this should really be a comment but I don't have the rep points).

If you want to directly setup a pdo odbc connection using FreeTDS driver to MS SQL server, without specifying it in configuration file freetds.conf.

$connection_string = "odbc:DRIVER=FreeTDS;SERVER=$serverName;PORT=$portNo;DATABASE=$dbName"; 
$conn = new PDO($connection_string, $dbUser, $dbPass);

If you have a MSSQL server that have a named instance, you can remove the port no and then modify $serverName in the format server_ip\instance_name Eg: "192.168.1.1\sqlexpress" where sqlexpress is the instance name.

$connection_string = "odbc:DRIVER=FreeTDS;SERVER=$serverName;DATABASE=$dbName"; 
$conn = new PDO($connection_string, $dbUser, $dbPass);

Please note to configure the driver location in odbcinst.ini

[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver      = libtdsodbc.so
Setup       = libtdsS.so

Connect PHP to MSSQL via PDO ODBC

Related

Recent Posts