So I’ve just spent a painful number of hours to figure out how to connect to a Microsoft SQL Database (MS SQL) that’s using dynamic ports and named instances (if you’ve got a feeling I’m definitely not a M$ guy … you would be right).
Because I didn’t find a proper working how to on doing this and had to resort to reading mailinglist archives and far, far too many stackoverflow threads I’m posting everything here before I do my best to forget it.
Note: I’m using Arch (Manjaro) but it should work the same way on Ubuntu – just update paths and install commands.
Step 1: Install required packages
sudo pacman -S odbc php-odbc freetds
Step 2: Enable extensions in php.ini
Make sure to restart PHP-FPM after you’re done.
Step 3: Configure ODBC and FreeTDS
sudo nano /etc/odbcinst.ini
[FreeTDS] # This is your identifier! You'll need it in odbc.ini Description = FreeTDS Driver v0.91 Driver = /usr/lib/libtdsodbc.so # THIS IS ARCH SPECIFIC PATH fileusage=1 dontdlclose=1 UsageCount=1
If you’re not on arch you’ll have to find the location of libtdsodbc.so for your distro, or you can just use locate to find it.
sudo nano /etc/odbc.ini
[mssqlconn] # This is important - you'll be refering to your DB with this Description = MSSQL Server Driver = FreeTDS # Same as identifier in odbcinst.ini Database = NAME_OF_DATABASE # Not instance, database within the instance ServerName = mssqlconn # This is the same identifier as in freetds.conf TDS_Version = 8.0
sudo nano /etc/freetds/freetds.conf
Add this to the end:
[mssqlconn] host = # HOSTNAME or IP of your server instance = # Name of the INSTANCE (not DB) tds version = 8.0
Step 4: Test connection
osql -S mssqlconn -U 'username' -P 'password'
If this works, you’re good to go!
Step 5: Connect from PHP
$serverName = 'mssqlconn'; $dbName = 'YourDb'; $pdo = new PDO(sprintf("odbc:DRIVER=FreeTDS;SERVERNAME=%s;DATABASE=%s", $serverName, $dbName), $username, $pass);
That’s it! The rest is standard PDO stuff.