Unable to connect to MS-SQL with ISQL

Question

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

Answer 1

I would replace "Username" with "UID" and "Password" with "PWD" in your odbc.ini.... from FreeTDS Manual - Chapter 4 - Preparing ODBC:

The original ODBC solution to this conundrum employed the odbc.ini file. odbc.ini stored information about a server, known generically as a Data Source Name (DSN). ODBC applications connected to the server by calling the function SQLConnect(DSN, UID, PWD), where DSN is the Data Source Name entry in odbc.ini, UID is the username, and PWD the password. Any and all information about the DSN was kept in odbc.ini. And all was right with the world.

The ODBC 3.0 specification introduced a new function: SQLDriverConnect. The connection attributes are provided as a single argument, a string of concatenated name-value pairs. SQLDriverConnect subsumed the functionality of SQLConnect, in that the name-value pair string allowed the caller to pass — in addition the the original DSN, UID, and PWD — any other parameters the driver could accept. Moreover, the application can specify which driver to use. In effect, it became possible to specify the entire set of DSN properties as parameters to SQLDriverConnect, obviating the need for odbc.ini. This led to the use of the so-called DSN-less configuration, a setup with no odbc.ini.