1

I had to upgrade to php 7.1 and then my connection to mssql via freetds aren't working

  • PHP 7.1.12-1+ubuntu16.04.1+deb.sury.org+1
  • Ubuntu 16.04.3 LTS
  • SQL Server 2008 r2

The tsql -C

tsql -C

Compile-time settings (established with the "configure" script)
                            Version: freetds v0.91
             freetds.conf directory: /usr/local/etc
     MS db-lib source compatibility: yes
        Sybase binary compatibility: no
                      Thread safety: yes
                      iconv library: yes
                        TDS version: 5.0
                              iODBC: no
                           unixodbc: yes
              SSPI "trusted" logins: no
                           Kerberos: no

/etc/odbcinst.ini

[freetds]
Description     = MS SQL database access with Free TDS
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
UsageCount = 1

/etc/odbc.ini

[mssql_db1_freetds]
Driver                  = freetds
Description             = MSSQL Server Stage
Database                = TESTDB1
Server                  = 192.168.0.20
Port                    = 1433
instance                = SQLSRV01


[mssql_db2_freetds]
Driver = FreeTDS
Server = 192.168.0.20
Port = 1433
Database = TESTDB2
Driver=/usr/local/lib/libtdsodbc.so
UsageCount = 1

[Default]
Driver=/usr/local/lib/libtdsodbc.so

/etc/freetds/freetds.conf (copied in /usr/local/etc/freetds.conf for command line)

#   $Id: freetds.conf,v 1.12 2007/12/25 06:02:36 jklowden Exp $
#
# This file is installed by FreeTDS if no file by the same
# name is found in the installation directory.
#
# For information about the layout of this file and its settings,
# see the freetds.conf manpage "man freetds.conf".

# Global settings are overridden by those in a database
# server specific section
[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 = 600
        connect timeout = 600

[mssql_db1_freetds]
    host = 192.168.0.20
    port = 1433
    timeout = 600
    connect timeout = 600
    tds version = 8.0
    text size = 20971520

Tryin to connect by tsql

tsql -S 192.168.0.20 -U test_user -P mypwd (same with tsql -S mssql_db1_freetds -U test_user -P mypwd)

locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Error 20009 (severity 9):
        Unable to connect: Adaptive Server is unavailable or does not exist
        OS error 111, "Connection refused"
There was a problem connecting to the server

I really don't know whre i'm wrong. SQLSERVER config is not changed. Thanks

  • I've deleted odbcinst.ini and odbc.ini now i can connect via command line but not by my web app (symfony). error is SQLSTATE[01002] Adaptive Server connection failed (mssql_db1_freetds) – Jack Skeletron Dec 4 '17 at 10:02
2

I've changed tds version in /etc/freetds/freetds.conf from 8.0 (it worked before php 7.1 upgrade) to 7.0 a typical MSSQL server. Now it works.

Complete config

/etc/odbcinst.ini

[freetds]
Description     = MS SQL database access with Free TDS
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
UsageCount = 1

/etc/odbc.ini

[mssql_db1_freetds]
Description             = MSSQL Server
Driver                  = freetds
Database                = Db1
ServerName              = mssql_db1_freetds
TDS_Version             = 7.0

[mssql_db2_freetds]
Description             = MSSQL Server
Driver                  = freetds
Database                = Db2
ServerName              = mssql_db2_freetds
TDS_Version             = 7.0

/etc/freetds/freetds.conf

[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 = 600
        connect timeout = 600

[mssql_db1_freetds]
    host = 192.168.0.20
    port = 1433
    timeout = 600
    connect timeout = 600
    tds version = 7.0
    text size = 20971520

[mssql_db2_freetds]
    host = 192.168.0.20
    port = 1433
    timeout = 600
    connect timeout = 600
    tds version = 7.0
    text size = 20971520

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.