27

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?

user1477388
  • 20,790
  • 32
  • 144
  • 264
  • 1
    What operating system (and version) are you using? – Benny Hill Nov 23 '13 at 15:34
  • My PHP is on CentOS linux (mediatemple) but the MSSQL database is from Microsoft Dynamics (Great Plains). I don't know what version it is. Do I need to find out? My database user only has read-access, they told me. – user1477388 Nov 23 '13 at 16:14
  • 1
    Thank you for asking in stackoverflow. I really having hard time maintaining old legacy code using sql 2000. I'm just born 1996 and nowdays database are simpler, this way is really annoying, but at least someone already face it. Thank you – Benyamin Limanto Dec 03 '19 at 10:33

3 Answers3

38

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.

FlipperPA
  • 13,607
  • 4
  • 39
  • 71
Benny Hill
  • 6,191
  • 4
  • 39
  • 59
  • Thanks but I am not sure how to install it. I have pulled up documents like http://www.hosting.com/support/linux/installing-freetds-for-linux/ but I am an IIS guy! I am not very comfortable with this... Is there a more full-proof way? I am on Plesk which I believe is CentOS (from mediatemple). – user1477388 Nov 23 '13 at 18:08
  • 1
    Well, just like your link says you need to install the necessary driver. SSH into your server (you can use [PuTTY](http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html)) and (as root) type `yum install freetds` to install FreeTDS. If you're uncomfortable doing this, find someone you know who is familiar with Linux to help you. – Benny Hill Nov 23 '13 at 19:55
  • I wish I knew someone who works with Linux! I know all only Microsoft people... I'll give it a shot and see what happens. Thanks. – user1477388 Nov 24 '13 at 00:48
  • I finally got it working. Couldn't have done it without ya! Thanks, Benny. Btw, I edited your answer with some additional points that helped me. – user1477388 Nov 26 '13 at 01:12
  • 1
    @user1477388 - whoo-hoo! Glad you got it working :-) – Benny Hill Nov 26 '13 at 02:31
  • 1
    Hi, I couldn't get it to work with the "dblib:..." DSN string but I was able to do it with an odbc one: "odbc:mssql" (skipping entirely the freetds.conf server definition). Anyone know what difference will it make doing it this way? I was able to query succesfully (I tried only SELECT statements as it was my goal). – Martin Buezas Apr 08 '15 at 17:07
  • you don't need to enclose parameters in quotes. `$pdo = new PDO("dblib:host=mssql;dbname=$dbname", $dbuser, $dbpwd);` – Gamesh Dec 11 '15 at 14:04
38

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).

JamesP
  • 602
  • 6
  • 12
  • This is a great answer, After reading around your answer makes perfect. http://stackoverflow.com/questions/13066716/freetds-not-using-its-config# – chapskev Apr 25 '16 at 10:51
  • This worked for me: $pd = new PDO("odbc:DRIVER=FreeTDS;SERVER=ipaddress\SQLEXPRESS;DATABASE=$dbname", $username, $password); – Jan Dec 14 '16 at 23:07
2

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
Don
  • 21
  • 5