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