Connecting to MSSQL db (PDO, FreeTDS, ODBC)
PHP modules installed?
Create a info.php
file in your webserver (apache2 dir: /var/www/html
in Ubuntu) with <?php phpinfo() ?>
for view details. Use odbcinst -j
for get config files loaded by unixODBC and tsql -C
for get FreeTDS details; check if is MS db-lib source compatibility: yes
.
If the MS db-lib
is 'no' you need configure freetds with the --enable-msdblib
.
My ./configure
line:
./configure --with-tdsver=8.0 --with-unixodbc=/usr/local --enable-msdblib
Then sudo make && sudo make install
This is my working config:
Freetds.conf:
[MSSQL]
host = 192.168.1.200
port = 1433
tds version = 8.0
client charset = UTF-8
odbc.ini:
[mssql]
Description = MSSQL Server
Driver = freetds #The odbcinst.ini driver name
ServerName = MSSQL # The Freetds.conf connection name
Database = database
TDS_Version = 8.0
odbcinst.ini
[freetds]
Description = MS SQL database access with Free TDS
Driver = /usr/lib/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbclibtdsS.so
UsageCount = 1
Related videos on Youtube
Admin
Updated on September 18, 2022Comments
-
Admin over 1 year
I have an Ubuntu 12.04 server and I'm trying to establish a connection to a MSSQL database.
I've managed to connect using
tsql
andisql
, butosql
doesn't work and connecting with PHP using PDO also isn't working.. I will try to provide as much information as I can and if you need more just let me know and I will edit.freetds.conf:
[MSSQL] host = TPSACC port = 54488 tds version = 8.0
odbc.ini:
[MSSQL] Description = MS SQL connection to PRODUCTION database Driver = FreeTDS Database = PRODUCTION Server = TPSACC UserName = sa Password = pass Trace = No TDS_Version = 8.0 Port = 54488
odbcinst.ini:
[FreeTDS] Description = ODBC for Microsoft SQL Driver = /usr/local/lib/libtdsodbc.so UsageCount = 1 Threading = 2
~> isql MSSQL sa pass
+---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL>
~> tsql -S MSSQL -U 'sa' -P 'pass'
locale is "en_US.UTF-8" locale charset is "UTF-8" using default charset "UTF-8" 1>
~> osql -S MSSQL -U sa -P pass
checking shared odbc libraries linked to isql for default directories... strings: '': No such file trying /tmp/sql ... no trying /tmp/sql ... no trying /etc ... OK checking odbc.ini files reading /home/toolplas/.odbc.ini [MSSQL] not found in /home/toolplas/.odbc.ini reading /etc/odbc.ini [MSSQL] found in /etc/odbc.ini found this section: [MSSQL] Description = MS SQL connection to PRODUCTION database Driver = FreeTDS Database = PRODUCTION Server = TPSACC UserName = sa Password = pass Trace = No TDS_Version = 8.0 Port = 54488 looking for driver for DSN [MSSQL] in /etc/odbc.ini found driver line: " Driver = FreeTDS" driver "FreeTDS" found for [MSSQL] in odbc.ini found driver named "FreeTDS" "FreeTDS" is not an executable file looking for entry named [FreeTDS] in /etc/odbcinst.ini found driver line: " Driver = /usr/local/lib/libtdsodbc.so" found driver /usr/local/lib/libtdsodbc.so for [FreeTDS] in odbcinst.ini /usr/local/lib/libtdsodbc.so is an executable file "Server" found, not using freetds.conf Server is "TPSACC" osql: no IP address found for "TPSACC"
In PHP I have:
$conn = new PDO ("dblib:host=TPSACC;dbname=PRODUCTION","$username","$pw");
..or..
$conn = new PDO ("dblib:host=TPSACC;port=54488;dbname=PRODUCTION","$username","$pw");
And they both return this error:
SQLSTATE[HY000] Unable to connect: Adaptive Server is unavailable or does not exist (severity 9)
I have been stuck here for a couple days and can't quite figure out why only half of the connections actually work..
Any and all help is really appreciated, thanks!
-
TallTed over 8 yearsYou're not using ODBC, with those connect strings. You're telling PHP to use the SQL Server-specific DBLIB connector, originally developed as Sybase (hence "Adaptive Server"). You may benefit from reviewing some documentation on PHP + ODBC. If you're targeting a recent SQL Server, you may also need a different ODBC driver, as FreeTDS doesn't yet fully support all the new features of SQL Server 2008 and later. My employer makes a few you can test for free.
-
-
Vladimir Pak about 4 yearsMore detailed: ettiennelouw.co.za/2016/12/21/mssql-ubuntu-16-php-7-freetds In my case works with: MS db-lib source compatibility: no