29

If you search Google for this question, you will find a lot of incorrect, misleading, and outdated information. Surprisingly, there isn't a solid answer on Stack Overflow, so we should change that.

I am using the Mac port installation of Apache and PHP. I have installed php5-mssql, and I can see mssql on my phpinfo() page.

But I don't see it listed under PDO.

PDO support enabled
PDO drivers     dblib, mysql, odbc, pgsql

Is mssql not associated with PDO? Is there another driver that can be used on a Mac to connect to a SqlServer database using PDO? Seems like this is something that should be possible.

Eric Z Beard
  • 37,669
  • 27
  • 100
  • 145

5 Answers5

16

Does this help you?

http://blog.nguyenvq.com/2010/05/16/freetds-unixodbc-rodbc-r/

I use FreeTDS to connect to Microsoft SQL servers from a Linux server and it looks like the person in the link above has used FreeTDS to connect from a Mac.

Here is my /etc/freetds/freetds.conf file (the only part I added was at the very end for the XYZ server):

[global]
        # TDS protocol version
;       tds version = 4.2

        # Whether to write a TDSDUMP file for diagnostic purposes
        # (setting this to /tmp is insecure on a multi-user system)
;       dump file = /tmp/freetds.log
;       debug flags = 0xffff

        # Command and connection timeouts
;       timeout = 10
;       connect timeout = 10

        # If you get out-of-memory errors, it may mean that your client
        # is trying to allocate a huge buffer for a TEXT field.  
        # Try setting 'text size' to a more reasonable limit 
        text size = 64512

# Define a connection to the MSSQL server.
[xyz]
        host = xyz
        port = 1433
        tds version = 8.0

[Edit by the asker]

FreeTDS configuration is the first half of the answer. Once it's configured you should be able to run something like this from the command line and connect:

tsql -S xyz -U username -P password

Then you need to use dblib, not mssql, as the PDO driver:

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

Where $dbhost is the name from the freetds.conf file

Eric Z Beard
  • 37,669
  • 27
  • 100
  • 145
Benny Hill
  • 6,191
  • 4
  • 39
  • 59
  • 2
    I have not had any luck using this approach on a Mac. – Eric Z Beard Nov 27 '12 at 12:24
  • How far along can you get in the process? Can you connect to the MSSQL server using tsql from the command line? (tsql -S mssql_server_name -U username) Also, how about: $con = new PDO('dblib:host=xyz;dbname=abc', 'domain\username', 'password'); (Note the use of the domain as part of the username) – Benny Hill Nov 27 '12 at 15:00
  • locale is "en_US.UTF-8" locale charset is "UTF-8" using default charset "UTF-8" Error 20017 (severity 9): Unexpected EOF from the server Error 20002 (severity 9): Adaptive Server connection failed There was a problem connecting to the server – Eric Z Beard Nov 27 '12 at 21:35
  • And with PDO I get: Unable to connect: Adaptive Server is unavailable or does not exist (severity 9) – Eric Z Beard Nov 27 '12 at 21:36
  • Does this help (in regards to Error 20017): http://stackoverflow.com/questions/6973371/freetds-problem-connecting-to-sql-server-on-mac-unexpected-eof-from-the-server – Benny Hill Nov 27 '12 at 21:38
  • Ok now I can connect. @E_p also deserves some credit for the dblib answer, since that's the PDO driver to use. – Eric Z Beard Nov 27 '12 at 22:35
  • Unfortunatelly the link provided in the answer does not exist ; can you point to another link ? – delphirules Oct 03 '18 at 17:13
5

dblib is the driver that need to be used with mssql on unix systems

No need for you to install anything else,

<?php
    $dsn = 'dblib:dbname=testdb;host=127.0.0.1';
    $user = 'dbuser';
    $password = 'dbpass';
    $dbh = new PDO($dsn, $user, $password);
E_p
  • 3,136
  • 16
  • 28
  • 1
    When I try this I get "Unable to connect: Adaptive Server is unavailable or does not exist (severity 9)". I have confirmed that the SqlServer database is available on the network via telnet to port 1433. – Eric Z Beard Nov 27 '12 at 12:23
  • $dsn = 'dblib:dbname=testdb;host=127.0.0.1;port=1433'; http://php.net/manual/en/ref.pdo-mysql.connection.php – E_p Nov 27 '12 at 16:42
  • Same result after adding port. – Eric Z Beard Nov 27 '12 at 21:36
  • another option is `$dsn = 'dblib:dbname=testdb;host=127.0.0.1:1433';` – E_p Nov 27 '12 at 21:43
  • sorry I couldn't choose this as the accepted answer.. both of you had half of it but without the FreeTDS config the dblib driver won't work. I had to pick one of them. – Eric Z Beard Nov 27 '12 at 22:43
  • @delphirules Thanks, removed link. – E_p Oct 03 '18 at 18:21
5

After looking at many threads, I've found that the best way to connect to MSSQL from Mac OS X with PHP 7 or older is to use dblib. (Just download the correct php version)

You can follow these instructions (ignoring the mssql.so extension) to connect very easily:

https://github.com/BellevueCollege/public-docs/blob/master/PHP/configure-mssql-pdodblib-mac.md

It worked perfect with OS X El Capitan, Bitnami with PHP 7.

Steps 1.- Install XCode

$ xcode-select ---install

2.- Install Homebrew

3.- Install autoconf using Homebrew.

$ brew install autoconf

4.- Install FreeTDS

$ brew install freetds

5.- Download your version of PHP Source and uncompress it.

6.- Build the PDO DBLIB extension (Example for PHP 5.5.14)

$ cd php-5.5.14/ext/pdo_dblib
$ phpize
$ ./configure --with-php-config=/usr/bin/php-config --with-pdo-dblib=/usr/local/
$ make
$ sudo cp modules/pdo_dblib.so /usr/lib/php/extensions/no-debug-non-zts-20121212

7.- Add the .so extensio to php.ini extension=pdo_dblib.so

8.- Restart Apache

9.- Connect using the dblib dsn:

$pdo = new PDO("dblib:host=$dbhost;dbname=$dbname","$dbuser","$dbpwd");
Esteban Eid Jordán
  • 119
  • 1
  • 6
  • Well, I made it all the way to the end of step 6, but could not copy that file to that /usr/lib/... location. I was told: `cp: /usr/lib/php/extensions/no-debug-non-zts-20121212/pdo_dblib.so: Operation not permitted`. I also tried copying the file over via ForkLift, but was still denied. And, yes, I am 100% certain I'm using the correct su credentials. I just wish it gave me a reason. For reference, I am using MAMP 4.0.6 with its PHP 7.0.12. – Sturm May 24 '17 at 17:26
  • Tried to do this, but i get the 'could not find driver' error . Any ideas ? – delphirules Oct 20 '17 at 16:17
  • 1
    @Sturm I had the same `Operation not permitted` message, and this is caused by Apple's SIP (System Integrity Protection). You have to disable SIP before you can copy something into this directory. Check this answer : https://apple.stackexchange.com/a/208481 – Clemorphy Nov 22 '17 at 11:41
  • When i run this command "./configure --with-php-config=/usr/bin/php-config --with-pdo-dblib=/usr/local/" i get this error : "configure: error: Cannot find php_pdo_driver.h.". Any ideas ? – delphirules Oct 03 '18 at 18:07
  • When i run the command "./configure --with-php-config=/usr/bin/php-config --with-pdo-dblib=/usr/local/" i'm getting this error : "configure: error: Cannot find php_pdo_driver.h.". Any ideas ? – delphirules Oct 03 '18 at 18:10
1

Thanks Esteban for the nice guide (https://stackoverflow.com/a/37707426) which succesfully helped me install the pdo_dblib driver.

However, I was having issues connecting to my Azure SQL database from OSX 10 with PHP (5.5) and FreeTDS using the provided dblib dsn. What finally fixed it for me was appending the Azure database (m53man42a) to my username.

My dblib PDO connection in PHP:

$conn = new PDO("dblib:host=azure-sql;dbname=my-database-name", 
"username@m53man42a",
"my-secret-password");

My FreeTDS.conf:

[azure-sql]
host = m53man42a.database.windows.net
port = 1433
tds version = 8.0
client charset = UTF-8
text size = 20971520

Consider adding this as a bullet number 10 in your list... :D

Community
  • 1
  • 1
holm50
  • 821
  • 8
  • 7
1

Note that Microsoft have published a PHP7 extension for this, but if you're still on PHP5.x, that doesn't help you. I've succeeded in connecting using a different stack: freetds,odbc,pdo.

I'm using OS X 10.11.6 (El Capitan) with Macports, PHP5.6.

I've started by creating an Azure SQL Database called mydb on a server with a name of myserver.database.windows.net. It's important to remember to open the firewall to your client IP address, which you do on the server.

First step is to install freetds with the ODBC driver, and its PHP connector (change php56 to the correct version of your PHP):

sudo port install freetds +odbc
sudo port install php56-odbc

Next, you need to include some lines in your configuration files:

/opt/local/etc/odbcinst.ini

[FreeTDS]
    Description = ODBC for FreeTDS
    Driver      = /opt/local/lib/libtdsodbc.so
    Setup       = /opt/local/lib/libtdsodbc.so
    FileUsage   = 1

This tells the odbc library where to find its odbc driver.

/opt/local/etc/freetds/freetds.conf

[myserver]
    host = myserver.database.windows.net
    port = 1433
    tds version = 7.0

This tells the freetdc library where to find your server.

/opt/local/etc/odbc.ini

[myds]
Description = Test for SQL Server on Azure
Driver = FreeTDS
Trace = Yes
TraceFile = /var/log/sql.log
Database = mydb
Servername = myserver
UserName = myusername
Password = mypassword
Port = 1433
Protocol = 7.0
ReadOnly = No
RowVersioning = No
ShowSystemTables = No
ShowOidColumn = No
FakeOidIndex = No

This creates a data source called myds pointing at your database, enabling you to connect with the following PHP:

$conn = new PDO('odbc:myds', 'myusername', 'mypassword');

If any of this doesn't work for you, first check that the freetds installation is correct using:

tsql -S myserver -U myusername -P mypassword

And then check that the ODBC specifications are OK using:

isql -v myds myusername mypassword

Thanks to https://github.com/lionheart/django-pyodbc/wiki/Mac-setup-to-connect-to-a-MS-SQL-Server, which does the equivalent job for Python and which pointed me in the right direction for all this.

xgretsch
  • 1,294
  • 13
  • 15