centosodbcisql

Unable to connect to MS-SQL with ISQL


First post on StackExchange - please go easy :)

I have setup ODBC in Centos 6 in order to perform ms-sql queries from my Asterisk installation.

My Config files are:

/etc/odbc.ini

[asterisk-connector]
Description     = MS SQL connection to 'asterisk' database
Driver          = /usr/lib64/libtdsodbc.so
Setup           = /usr/lib64/libtdsS.so
Servername      = SQL2
Port            = 1433
Username        = MyUsername
Password        = MyPassword
TDS_Version     = 7.0

/etc/odbcinst.ini

[odbc-test]
Description = TDS connection
Driver = /usr/lib64/libtdsodbc.so
Setup = /usr/lib64/libtdsS.so
UsageCount = 1
FileUsage = 1

/etc/asterisk/res_odbc.conf

[asterisk-connector]
enabled => yes
dsn => asterisk-connector
username => MyUsername
password => MyPassword
pooling => no
limit =>
pre-connect => yes

I am able to connect via ISQL when I pass in the password and username:

[root@TestVM etc]# isql -v asterisk-connector MyUsername MyPassword
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

..but I should be able to connect without the username / password. All that returns is:

[root@TestVM etc]# isql -v asterisk-connector
[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[01000][unixODBC][FreeTDS][SQL Server]Adaptive Server connection failed
[ISQL]ERROR: Could not SQLConnect

It is as if ISQL cannot read the username and password from the config files.

I need to be able to perform MS-SQL lookups from within the Asterisk dialplan, but for that to happen I must be able to call ISQL with just the data source name and can't pass in the authentication parameters.

All the guides I've read online state that I should be able to connect with just the

isql -v asterisk-connector

command, but that's not happening for me.

I've been pulling my hair out for a few days on this, so any help or pointers in the right direction would be much appreciated.

Thanks in advance.

Edit:

I have turned on logging, and may have a clue. The username and password definitely aren't being passed in. Look:

[ODBC][27557][1455205133.129690][SQLConnect.c][3614]
                Entry:
                        Connection = 0xac3080
                        Server Name = [asterisk-connector][length = 18 (SQL_NTS)]
                        User Name = [NULL]
                        Authentication = [NULL]
                UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE'

                DIAG [01000] [FreeTDS][SQL Server]Adaptive Server connection failed

                DIAG [S1000] [FreeTDS][SQL Server]Unable to connect to data source

So User Name and Authentication here are [NULL]. It's obviously not picking up the username / password in odbc.ini or res_odbc.conf, but the question is why. I'll keep investigating :)

Edit2:

The OSQL utility returns:

[root@TestVM etc]# osql -S SQL2 -U MyUsername -P MyPassword
checking shared odbc libraries linked to isql for default directories...
strings: '': No such file
        trying /tmp/sqlH ... no
        trying /tmp/sqlL ... no
        trying /etc ... OK
checking odbc.ini files
        reading /root/.odbc.ini
[SQL2] not found in /root/.odbc.ini
        reading /etc/odbc.ini
[SQL2] found in /etc/odbc.ini
found this section:
looking for driver for DSN [SQL2] in /etc/odbc.ini
  no driver mentioned for [SQL2] in odbc.ini
looking for driver for DSN [default] in /etc/odbc.ini
osql: error: no driver found for [SQL2] in odbc.ini

Solution

  • Ok, so I solved it (pretty much). The password and username in my odbc files were being ignored. Because I was calling the DB queries from Asterisk, I was using a file called res_odbc.ini too. This contained my username and password also, and when I run the query from Asterisk, it conencts and returns the correct result.

    In case it helps, here is my final working configuration.

    odbc.ini

    [asterisk-connector]
    Description = MS SQL connection to asterisk database
    driver = /usr/lib64/libtdsodbc.so
    servername = SQL2
    Port = 1433
    User = MyUsername
    Password = MyPassword
    

    odbcinst.ini

    [FreeTDS]
    Description = TDS connection
    Driver = /usr/lib64/libtdsodbc.so
    UsageCount = 1
    
    [ODBC]
    trace           = Yes
    TraceFile       = /tmp/sql.log
    ForceTrace      = Yes
    

    freetds.conf

    #   $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 = 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
    
    # A typical Sybase server
    [egServer50]
            host = symachine.domain.com
            port = 5000
            tds version = 5.0
    
    # A typical Microsoft server
    [SQL2]
            host = 192.168.1.59
            port = 1433
            tds version = 8.0
    

    res_odbc.conf

    [asterisk-connector]
    enabled = yes
    dsn = asterisk-connector
    username = MyUsername
    password = MyPassword
    pooling = no
    limit = 1
    pre-connect = yes
    

    Remember if you are using Centos 64 bit to modify the driver path to lib64. Most of the guides online have the wrong (for 64 bit) paths.

    Good luck - it's a headache :)